Oracle Merge 语言使用

动机：

想在Oracle中用一条SQL语句直接进行Insert/Update的操作。

说明：

在进行SQL语句编写时，我们经常会遇到大量的同时进行Insert/Update的语句 ，也就是说当存在记录时，就更新(Update)，不存在数据时，就插入(Insert)。

 

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

1、UPDATE或INSERT子句是可选的

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

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

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

语法:

MERGEHINTINTOSCHEMA . TABLE T_ALIAS

USINGSCHEMA . {TABLE |VIEW |SUBQUERY } T_ALIAS

ON (CONDITION)

WHENMATCHEDTHEN MERGE_UPDATE_CLAUSE

WHENNOTMATCHEDTHEN MERGE_INSERT_CLAUSE / MERGE_DELETE_CLAUSE;

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

ifexists(...)

updatetable

else

Insertinto table.

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

例子：

1、 创建测试表及数据

[c-sharp] view plain copy print ?

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; 

DROP TABLE PRODUCTS;DROP TABLE NEWPRODUCTS;create table PRODUCTS(PRODUCT_ID INTEGER,PRODUCT_NAME VARCHAR2(60),CATEGORY VARCHAR2(60));insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');commit;create table NEWPRODUCTS(PRODUCT_ID INTEGER,PRODUCT_NAME VARCHAR2(60),CATEGORY VARCHAR2(60));insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');commit;

2、 匹配更新

[c-sharp] view plain copy print ?

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; 

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY;SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

3、 不匹配插入

[c-sharp] view plain copy print ?

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; 

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN NOT MATCHED THEN INSERT (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES (NP.PRODUCT_ID ,NP.PRODUCT_NAME ,NP.CATEGORY);SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

4、 匹配带where/on更新

[c-sharp] view plain copy print ?

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; 

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME WHERE P.CATEGORY = NP.CATEGORY;MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID AND P.CATEGORY = NP.CATEGORY)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME;SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;SELECT * FROM PRODUCTS A INNER JOIN NEWPRODUCTS B ON A.PRODUCT_ID = B.PRODUCT_ID AND A.CATEGORY = B.CATEGORY;

5、 匹配带where更新、插入

[c-sharp] view plain copy print ?

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; 

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY WHERE P.CATEGORY = 'DVD'WHEN NOT MATCHED THEN INSERT (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES (NP.PRODUCT_ID ,NP.PRODUCT_NAME ,NP.CATEGORY) WHERE NP.CATEGORY != 'BOOKS';SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

6、 ON常量表达式

[c-sharp] view plain copy print ?

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; 

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (1 = 0)WHEN NOT MATCHED THEN INSERT (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES (NP.PRODUCT_ID ,NP.PRODUCT_NAME ,NP.CATEGORY) WHERE NP.CATEGORY = 'BOOKS';SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

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

[c-sharp] view plain copy print ?

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; 

MERGE INTO PRODUCTS PUSING NEWPRODUCTS NPON (P.PRODUCT_ID = NP.PRODUCT_ID)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = NP.PRODUCT_NAME, P.CATEGORY = NP.CATEGORY DELETE WHERE (P.CATEGORY = 'ELECTRNCS')WHEN NOT MATCHED THEN INSERT (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES (NP.PRODUCT_ID ,NP.PRODUCT_NAME ,NP.CATEGORY);SELECT * FROM PRODUCTS;SELECT * FROM NEWPRODUCTS;

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

[c-sharp] view plain copy print ?

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 INTO PRODUCTS PUSING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) BON (B.CO <> 0)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501WHEN NOT MATCHED THEN INSERT (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES (1501 ,'KEBO' ,'NBA');MERGE INTO PRODUCTS PUSING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) BON (B.CO <> 0)WHEN MATCHED THEN UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508WHEN NOT MATCHED THEN INSERT (PRODUCT_ID ,PRODUCT_NAME ,CATEGORY) VALUES (1508 ,'KEBO' ,'NBA');SELECT * FROM PRODUCTS;

优点：

— 避免了分开更新

— 提高性能并易于使用

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

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

需要注意的地方：

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

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

http://www.cnblogs.com/highriver/archive/2011/08/02/2125043.html