Oracle MERGE INTO语法探究

浏览数：20 / 时间：2015年06月12日

1、MERGE INTO 的用途：
MERGE INTO 是Oracle 9i新增的语法，在10g时得到补充，用来合并UPDATE和INSERT语句，根据一张表或子查询与另一张表进行连接查询，连接条件匹配就进行

UPDATE，不匹配就进行INSERT，这个语法仅需要一次全表扫描就可以完成全部工作，执行效率会比单纯的UPDATE+INSERT高，具体应用可用于表之间的同步。
2、MERGE INTO 的语法：
语法结构：
MERGE [INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

语法说明：

MERGE INTO [表名] [别名] --需要更新的目标表

USING ( 子查询/表名/视图)[别名] --源表

ON ([连接条件] AND [...]...) --连接条件/更新条件

WHEN MATHED THEN UPDATE SET [...] --如果匹配，更新表记录，若只作更新出来，下面的INSERT部分可以去掉

WHEN NOT MATHED THEN INSERT VALUES() [...] --如果不匹配，插入表记录
3、MERGE INTO 演示：
1> 创建测试表及数据：
     --以表YAG1作为源表，表YAG2作为更新的目标表
     CREATE TABLE YAG1 AS SELECT OBJECT_NAME,oOBJECT_ID FROM USER_OBJECTS WHERE ROWNUM<=10;
     CREATE TABLE YAG2 AS SELECT OBJECT_NAME,oOBJECT_ID FROM USER_OBJECTS WHERE ROWNUM<=5;
     --修改表YAG1中某条记录的OBJECT_NAME，创造符合UPDATE的条件，
     SQL> UPDATE YAG1 SET OBJECT_NAME=‘AAAAA‘ WHERE OBJECT_NAME=‘T_CAT‘;
2>MERGE INTO 更新前两表的记录对比：
     SQL> SELECT A.OBJECT_ID,A.OBJECT_NAME,B.OBJECT_NAME FROM YAG1 A,YAG2 B WHERE A.OBJECT_ID=B.OBJECT_ID(+) ORDER BY 1;
A.OBJECT_ID      A.OBJECT_NAME        B.OBJECT_NAME
------------   ----------------   -----------------
46366            AAAAA              T_CAT
46367            SUM_STRING         SUM_STRING
46368            ARRAYLIST         ARRAYLIST
46369            TYSKZ_SJDX         TYSKZ_SJDX
46370            TYSKZ_SJXMGX         TYSKZ_SJXMGX

46371            PARAOBJECT
46372            T_LINK
46373            STR_SPLIT
46374            SPLIT_TYPE
46375            SYS_PLSQL_95487_9_1
3> 执行下面MERGE INTO 语句：
MERGE INTO YAG2 A
USING YAG1 B
ON (A.OBJECT_ID = B.OBJECT_ID)
WHEN MATCHED THEN
UPDATE SET A.OBJECT_NAME = B.OBJECT_NAME
WHEN NOT MATCHED THEN
INSERT VALUES (B.OBJECT_NAME, B.OBJECT_ID);
COMMIT;
4> MERGE INTO 更新后两表的记录对比：
     SQL> SELECT A.OBJECT_ID,A.OBJECT_NAME,B.OBJECT_NAME FROM YAG1 A,YAG2 B WHERE A.OBJECT_ID=B.OBJECT_ID(+) ORDER BY 1;
A.OBJECT_ID      A.OBJECT_NAME        B.OBJECT_NAME
------------   ----------------   -----------------
46366            AAAAA AAAAA
46367            SUM_STRING         SUM_STRING
46368            ARRAYLIST           ARRAYLIST
46369            TYSKZ_SJDX         TYSKZ_SJDX
46370            TYSKZ_SJXMGX         TYSKZ_SJXMGX
46371            PARAOBJECT         PARAOBJECT
46372            T_LINK               T_LINK
46373            STR_SPLIT         STR_SPLIT
46374            SPLIT_TYPE         SPLIT_TYPE
46375            SYS_PLSQL_95487_9_1 SYS_PLSQL_95487_9_1
.