merge into用法详解

使用merge语句可以从一个或多个源中选择要满足条件的行更新或插入到表或试图中，你可以指定条件来确定是更新还是插入到目标表或视图中。
该语句是组合多个操作的方便方法，效率也更高。使得你避免使用多个INSERT、UPDATE和DELETE语句。
特别要注意：MERGE是一个确定性语句。不能在同一个MERGE语句中多次更新目标表的同一行（除非多次更新结果一致）。不然会直接报错。通俗的解释就是：假如源表中的多条记录导致会去目标表中更新同一行，但是会更新出两个不同的结果则会报错，更新得到同一个结果则不报错（这也是确定性的意思）。

1. 语法

MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING { [ schema. ] { table | view }
         | subquery 
         } [ t_alias ]
   ON ( condition )
   [ merge_update_clause ]
   [ merge_insert_clause ]
   [ error_logging_clause ] ;
 
merge_update_clause写法：
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
           [, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
 
merge_insert_clause写法：
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
          [, { expr | DEFAULT } ]...
       )
[ where_clause ]
 
where_clause写法：
WHERE condition

语法解析：
INTO子句
使用INTO子句指定要更新或插入的目标表或视图。
USING子句
使用USING子句指定要更新或插入记录的来源，即这些记录来自于哪里，可以是表或视图或子查询，注意子查询要用括号括起来。
ON子句
使用ON子句指定MERGE操作更新或插入的条件。对于目标表中搜索条件为true的每一行，Oracle数据库都会用源中的相应数据更新该行。如果任何行的条件都不为真，则数据库将根据相应的源行插入目标表。
merge_update子句
merge_update_clause子句指定目标表或视图的新列值。如果ON子句的条件为真，Oracle将执行此更新。如果执行了update子句，那么目标表上定义的所有更新触发器都将被激活。
这里还可以指定where条件，即where条件为真才更新。
merge_insert子句
如果ON子句的条件为false，则merge_insert_子句指定要插入到目标表列中的值。如果执行了insert子句，那么目标表上定义的所有插入触发器都将被激活。如果在INSERT关键字后省略列列表，则目标表中的列数必须与values子句中的值数匹配。
这里还可以指定where条件，即where条件为真才插入。

使用示例：

CREATE TABLE TY1(name varchar2(100), lev varchar2(100), age number);
CREATE TABLE TY2(name varchar2(100), lev varchar2(100), addr varchar2(100), age number);
 
INSERT INTO TY1('jack','level-10',23);
INSERT INTO TY1('rose','level-11',20);
 
INSERT INTO TY2('jack','level-9','addr1',19);
INSERT INTO TY2('hhj','level-10','addr2',18);
 
MERGE INTO TY1 
USING TY2 
ON (TY1.NAME = TY2.NAME)
WHEN MATCHED THEN
 UPDATE SET TY1.LEV =TY2.LEV,TY1.AGE = TY2.AGE 
WHEN NOT MATCHED THEN
 INSERT  VALUES (TY2.NAME,TY2.LEV,TY2.AGE)
 
最终TY1表里结果为：
jack,level-9,19
rose,level-11,20
hhj,level-10,18

2. 确定性语义问题示例展示

这里我们来展示一个上面说的确定性语义的问题，即在同一个MERGE语句中多次更新目标表的同一行。

正确示例：但是这里不会报错，因为多次更新同一行也是得到了一个确定性的结果（TY2里的两条jack的记录虽然更新了2次TY1，但是更新结果一致）。

清空上面的TY1和TY2表记录，重新插入：

DELETE FROM TY1;
DELETE FROM TY2;
INSERT INTO TY1('jack','level-10',23);
INSERT INTO TY1('rose','level-11',20);
 
INSERT INTO TY2('jack','level-9','addr1',19);
INSERT INTO TY2('jack','level-9','addr1',19);
INSERT INTO TY2('hhj','level-10','addr2',18);
 
MERGE INTO TY1 
USING TY2 
ON (TY1.NAME = TY2.NAME)
WHEN MATCHED THEN
 UPDATE SET TY1.LEV =TY2.LEV,TY1.AGE = TY2.AGE 
WHEN NOT MATCHED THEN
 INSERT  VALUES (TY2.NAME,TY2.LEV,TY2.AGE)
 
最终TY1表里结果为：
jack,level-9,19
rose,level-11,20
hhj,level-10,18

错误示例：但是这里会报错，因为多次更新同一行是得到了不同的，也就是不确定性的结果（TY2里的两条jack的记录更新了2次TY1，但是更新结果不一致，一个是level-8，一个是level-9）。
清空上面的TY1和TY2表记录，重新插入：

DELETE FROM TY1;
DELETE FROM TY2;
INSERT INTO TY1('jack','level-10',23);
INSERT INTO TY1('rose','level-11',20);
 
INSERT INTO TY2('jack','level-9','addr1',19);
INSERT INTO TY2('jack','level-8','addr1',19);
INSERT INTO TY2('hhj','level-10','addr2',18);
 
MERGE INTO TY1 
USING TY2 
ON (TY1.NAME = TY2.NAME)
WHEN MATCHED THEN
 UPDATE SET TY1.LEV =TY2.LEV,TY1.AGE = TY2.AGE 
WHEN NOT MATCHED THEN
 INSERT  VALUES (TY2.NAME,TY2.LEV,TY2.AGE)
 
最终TY1表里结果为：
报错

3. 根据某条数据来更新或插入示例展示

MERGE INTO TY1 t1
USING (SELECT 'jack' AS NAME, 'LEV-001' AS LEV, 34 AS AGE FROM DUAL) t2
ON (t1.NAME = t2.NAME)
WHEN MATCHED THEN
 UPDATE SET t1.LEV =t2.LEV,t1.AGE = t2.AGE 
WHEN NOT MATCHED THEN
 INSERT  VALUES (t2.NAME,t2.LEV,t2.AGE)

4. 注意点

1. 在 on里面的判断条件，假如有null或空字符串，会导致merge的时候一直是插入，不会更新。