Oracle中MERGE用法

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

1、UPDATE或INSERT子句是可选的

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

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

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

语法:

MERGEHINTINTO SCHEMA .TABLE T_ALIAS

USINGSCHEMA . {TABLE | VIEW |SUBQUERY } T_ALIAS

ON (CONDITION)

WHENMATCHEDTHEN MERGE_UPDATE_CLAUSE

WHENNOTMATCHED THEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

联想:
merge into是特有的功能，相当于在 MSSQL中的

ifexists(...)

updatetable

else

Insertinto table.

mergeinto语法不仅没有if exists语法啰嗦，而且比if exists还要高效很多，常用来在oracle之间同步数据库表。

例子：

1、创建测试表及数据

 

[c-sharp]  view plain  copy

1. DROP TABLE PRODUCTS;  
2. DROP TABLE NEWPRODUCTS;  
3. create table PRODUCTS  
4. (  
5. PRODUCT_ID INTEGER,  
6. PRODUCT_NAME VARCHAR2(60),  
7. CATEGORY VARCHAR2(60)  
8. );  
9. insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');  
10. insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');  
11. insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');  
12. insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');  
13. insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');  
14. commit;  
15. create table NEWPRODUCTS  
16. (  
17. PRODUCT_ID INTEGER,  
18. PRODUCT_NAME VARCHAR2(60),  
19. CATEGORY VARCHAR2(60)  
20. );  
21. insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');  
22. insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');  
23. insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');  
24. insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');  
25. commit;  

2、匹配更新

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
4. WHEN MATCHED THEN  
5.   UPDATE  
6.      SET P.PRODUCT_NAME = NP.PRODUCT_NAME,  
7.          P.CATEGORY     = NP.CATEGORY;  
8.   
9. SELECT * FROM PRODUCTS;  
10. SELECT * FROM NEWPRODUCTS;  

3、不匹配插入

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
4. WHEN NOT MATCHED THEN  
5.   INSERT  
6.     (PRODUCT_ID  
7.     ,PRODUCT_NAME  
8.     ,CATEGORY)  
9.   VALUES  
10.     (NP.PRODUCT_ID  
11.     ,NP.PRODUCT_NAME  
12.     ,NP.CATEGORY);  
13.   
14. SELECT * FROM PRODUCTS;  
15. SELECT * FROM NEWPRODUCTS;  

4、匹配带where/on更新

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
4. WHEN MATCHED THEN  
5.   UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;  
6.   
7. MERGE INTO PRODUCTS P  
8. USING NEWPRODUCTS NP  
9. ON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)  
10. WHEN MATCHED THEN  
11.   UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;  
12.   
13. SELECT * FROM PRODUCTS;  
14. SELECT * FROM NEWPRODUCTS;  
15.   
16. SELECT *  
17.   FROM PRODUCTS A  
18.  INNER JOIN NEWPRODUCTS B  
19.     ON A.PRODUCT_ID = B.PRODUCT_ID  
20.    AND A.CATEGORY = B.CATEGORY;  

5、匹配带where更新、插入

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
4. WHEN MATCHED THEN  
5.   UPDATE  
6.      SET P.PRODUCT_NAME = NP.PRODUCT_NAME,  
7.          P.CATEGORY     = NP.CATEGORY  
8.    WHERE P.CATEGORY = 'DVD'  
9. WHEN NOT MATCHED THEN  
10.   INSERT  
11.     (PRODUCT_ID  
12.     ,PRODUCT_NAME  
13.     ,CATEGORY)  
14.   VALUES  
15.     (NP.PRODUCT_ID  
16.     ,NP.PRODUCT_NAME  
17.     ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';  
18.   
19. SELECT * FROM PRODUCTS;  
20. SELECT * FROM NEWPRODUCTS;  

6、ON常量表达式

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (1 = 0)  
4. WHEN NOT MATCHED THEN  
5.   INSERT  
6.     (PRODUCT_ID  
7.     ,PRODUCT_NAME  
8.     ,CATEGORY)  
9.   VALUES  
10.     (NP.PRODUCT_ID  
11.     ,NP.PRODUCT_NAME  
12.     ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';  
13.   
14. SELECT * FROM PRODUCTS;  
15. SELECT * FROM NEWPRODUCTS;  

7、匹配删除、不匹配插入

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
4. WHEN MATCHED THEN  
5.   UPDATE  
6.      SET P.PRODUCT_NAME = NP.PRODUCT_NAME,  
7.          P.CATEGORY     = NP.CATEGORY DELETE  
8.    WHERE (P.CATEGORY = 'ELECTRNCS')  
9. WHEN NOT MATCHED THEN  
10.   INSERT  
11.     (PRODUCT_ID  
12.     ,PRODUCT_NAME  
13.     ,CATEGORY)  
14.   VALUES  
15.     (NP.PRODUCT_ID  
16.     ,NP.PRODUCT_NAME  
17.     ,NP.CATEGORY);  
18.   
19. SELECT * FROM PRODUCTS;  
20. SELECT * FROM NEWPRODUCTS;  

8、源表为子查询(自联接)

 

[c-sharp]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B  
3. ON (B.CO <> 0)  
4. WHEN MATCHED THEN  
5.   UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501  
6. WHEN NOT MATCHED THEN  
7.   INSERT  
8.     (PRODUCT_ID  
9.     ,PRODUCT_NAME  
10.     ,CATEGORY)  
11.   VALUES  
12.     (1501  
13.     ,'KEBO'  
14.     ,'NBA');  
15.   
16. MERGE INTO PRODUCTS P  
17. USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B  
18. ON (B.CO <> 0)  
19. WHEN MATCHED THEN  
20.   UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508  
21. WHEN NOT MATCHED THEN  
22.   INSERT  
23.     (PRODUCT_ID  
24.     ,PRODUCT_NAME  
25.     ,CATEGORY)  
26.   VALUES  
27.     (1508  
28.     ,'KEBO'  
29.     ,'NBA');  
30.   
31. SELECT * FROM PRODUCTS;  

优点：

 —避免了分开更新

 —提高性能并易于使用

 —在数据仓库应用中十分有用

 —使用merge比传统的先判断再选择插入或更新快很多

需要注意的地方：

1、从语法条件上看（ON (join condition)），merge into也是一个危险的语法。如果不慎重使用，会将源表全部覆盖到目标表，既危险又浪费效率，违背了增量同步的原则。所以在设计表结构时，一般每条记录都有“更新时间”的字段，用目标表“最大更新时间”判断源表数据是否有更新和新增的信息。

2、更新的字段，不允许有关联条件的字段（join condition）。比如条件是 A.ID=B.ID，那么使用“SET A.ID=B.ID”将报出一个莫名其妙的提示错误。

 

缺少一个带delete语句选项的示例，暂为补上：

[sql]  view plain  copy

1. MERGE INTO PRODUCTS P  
2. USING NEWPRODUCTS NP  
3. ON (P.PRODUCT_ID = NP.PRODUCT_ID)  
4. WHEN MATCHED THEN  
5.   UPDATE  
6.      SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY  
7.    --WHERE (P.CATEGORY = 'ELECTRNCS')   
8.   DELETE   
9.    WHERE (P.CATEGORY = 'ELECTRNCS')  
10. WHEN NOT MATCHED THEN  
11.   INSERT  
12.     (PRODUCT_ID, PRODUCT_NAME, CATEGORY)  
13.   VALUES  
14.     (NP.PRODUCT_ID, NP.PRODUCT_NAME, NP.CATEGORY);  
15.   
16. SELECT * FROM PRODUCTS;  
17. SELECT * FROM NEWPRODUCTS;  

说明：DELETE语句删除的是满足matched关联on条件，同时也要是update更新内容的子集，否则不会删除任何内容。