Oracle数据库中 merge into的用法

Oracle9g引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和update操作. MERGE命令从一个或多个数据源中选择行来update或insert到一个或多个表.在Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

5、源表就是using关键字后面跟的表，目标表就是将要被merge into的表

6、merge into 中所有的update、insert、delete都是针对目标表来操作的。由于merge into已经制定了操作的表，所以update、insert、delete都不需要再显示指出表名

7、总之，merge into的作用就是 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表或向一个表中插入数据，如果该表已有该数据则更新，反之新增数据。
语法：

MERGE INTO [your table-name] [rename your table here]  
  
USING ( [write your query here] )[rename your query-sql and using just like a table]  
  
ON ([conditional expression here] AND [...]...)  
  
WHEN MATHED THEN [here you can execute some update sql or something else ]  
  
WHEN NOT MATHED THEN [execute something else here ! ]  

接下来我们来直接进行测试：

需求一：向一个表中插入一条数据，如果该表中已经有该数据则更新，反之新增

首先创建一个表TEST_ONE

create table TEST_ONE
(
  ID   NUMBER not null
    primary key,
  NAME VARCHAR2(255),
  IP   VARCHAR2(255),
  MEMO VARCHAR2(255)
)
commit;

随便添加几条数据作为测试数据

INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (1, '2', '3', '周文军');
INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (2, '66', '366', '2656');
INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (3, '5656', '626', '2626');
INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (4, '5656', '2626', '626');

好了，我们的数据表已经建成，如下图：

如果我们需要新增一条数据，一般会这样进行

INSERT INTO TEST_ONE (ID, NAME, IP, MEMO) VALUES (5, 'mrhu', '127.0.0.1.0', '王先生的IP');

但我们希望可以先用ID进行判断，没有该数据新增，有该数据更新，怎么实现呢？

那么merge into命令来了，直接撸代码：

merge into TEST_ONE mtb using (select '5' as id, 'mrhu' as name,'127.0.0.1.0' as ip,'王先生的IP' as memo from dual)mmb on (mtb.id = mmb.id)
when matched THEN
update set mtb.name = mmb.name,mtb.ip = mmb.ip,mtb.memo=mmb.memo
when not matched then
insert (mtb.id, mtb.name,mtb.ip,mtb.memo) VALUES(mmb.id,mmb.name,mmb.ip,mmb.memo);

运行如下：

我们再来看看表中数据：

数据新增成功！

那么我们如何测试更新呢？很简单，我们将memo=‘王先生的IP’ 改为 memo=‘本大美女的IP’来进行测试

merge into TEST_ONE mtb using (select '5' as id, 'mrhu' as name,'127.0.0.1.0' as ip,'本大美女的IP' as memo from dual)mmb on (mtb.id = mmb.id)
when matched THEN
update set mtb.name = mmb.name,mtb.ip = mmb.ip,mtb.memo=mmb.memo
when not matched then
insert (mtb.id, mtb.name,mtb.ip,mtb.memo) VALUES(mmb.id,mmb.name,mmb.ip,mmb.memo);

运行如下：

我们再来看看表中数据：

数据更新成功啦！

需求二：将A表中的数据添加到B表中，要求通过主键来进行判断，如果包含该数据则更新，反之新增

我们再创建一个表TEST_TWO作为表B，TEST_ONE作为表A

create table tes
(
  ID   NUMBER not null
    primary key,
  CODE VARCHAR2(255),
  MEMO VARCHAR2(255)
);
commit;

好了 表TEST_TWO建立好了，我们先来添加一条数据吧！

INSERT INTO ROOT.TEST_TWO (ID, CODE, MEMO) VALUES (5, 'mrhu', '隔壁老王的IP');

我们再看看TEST_TWO中的数据：

我们来将TEST_ONE中的数据导到我们新建的表中，通过分析，我们发现，TEST_TWO 表中有了一条数据，ID为5，TEST_ONE中也有一条ID为5的数据，预期执行效果为TEST_TWOID为5的数据的memo字段值将更新为TEST_ONE中的‘本大美女的IP’，其他值进行新增操作。

我们写代码来验证一下：

merge into TEST_TWO mtb using (select id,name,ip,memo from TEST_ONE) mmb on (mtb.id = mmb.id)
when matched THEN
UPDATE set mtb.code = mmb.name,mtb.memo = mmb.memo
when not matched THEN
insert (mtb.id,mtb.code,mtb.memo) values (mmb.id,mmb.name,mmb.memo);

我们来看看效果：

执行结果与预期结果一致，好了，merge into的用法你们学会了吗! 喜欢的关注支持一下！