Oracle Merge 使用

http://blog.csdn.net/nsj820/article/details/5755685



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;

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

ifexists(...)

updatetable

else

Insertinto table.

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

例子：

1、创建测试表及数据

 

[c-sharp] view plaincopyprint?

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 plaincopyprint?

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 plaincopyprint?

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 plaincopyprint?

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 plaincopyprint?

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 plaincopyprint?

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 plaincopyprint?

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 plaincopyprint?

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”将报出一个莫名其妙的提示错误。

 

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

[sql] view plaincopyprint?

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;  

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 = 'ELECTRNCS')   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;

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

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

http://blog.itpub.net/25313300/viewspace-700222/

1.    MERGE INTO 的用途
         MERGE INTO 是Oracle 9i以后才出现的新的功能。那这个功能 是什么呢？
         简单来说，就是：“有则更新，无则插入”
         从这句话里，应该可以理解到，merge into 操作一个对象'A'的时候，要有另外一个结果集做为源数据 'B'.
         ‘merge into’  将B中的数据与A中的数据按照一定条件'C'进行对比，如果 A中数据满足C条件，则进行update操作，如果不满足条件 'C'，则进行insert操作。（请注意这种对应关系）

2、 语法结构
      
       MERGE [INTO] [schema.]table [alias]
       USING {[schema.]table|views|query} [alias]
       ON {condition}
       WHEN MATCHED THEN UPDATE SET {clause}
       WHEN NOT MATCHED THEN INSERT VALUES {clause}
     
       我们可以用于单条数据的处理，也可以用于数据的批处理。对于merge into来说，那都是张飞吃豆芽儿，小菜一碟儿。而且效率要比单独执行update+insert 操作效率要高。
    但是请注意，using语句中的结果集 B不可以与merge into 的对象A相同，否则，会因为结果集A,B恒等。

当 on() 进行等值判断时，只可以进行update操作，不能进行insert 操作，当 on() 进行不等值判断时，只可以进行insert操作，不能进行update操作。

         可能这样说还不是很清楚。下面我们实际操作演示，就会理解清楚了。

3.   测试 MERGE INTO
--------------------------------------------------------------------------------------------------------------
-------------------------------------        创建测试表--TEST1    ----------------------------------

 SQL>         CREATE TABLE TEST1(
      2          USERID        NUMBER,
      3          ID                  NUMBER);

Table created
--------------------------------------------------------------------------------------------------------------
----------------------------------          填充测试数据                ------------------------------- 
    
SQL> begin
  2  for i in 200..230 loop
  3  insert into test1 values(i,(i+100)/10);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed

SQL> select * from test1;

    USERID         ID
---------- ----------
       200         30
       201       30.1
       202       30.2
       203       30.3
       204       30.4
       205       30.5
       206       30.6
       207       30.7
       208       30.8
       209       30.9
       210         31
       211       31.1
       212       31.2
       213       31.3
       214       31.4
       215       31.5
       216       31.6
       217       31.7
       218       31.8
       219       31.9

    USERID         ID
---------- ----------
       220         32
       221       32.1
       222       32.2
       223       32.3
       224       32.4
       225       32.5
       226       32.6
       227       32.7
       228       32.8
       229       32.9
       230         33

31 rows selected
--------------------------------------------------------------------------------------------------------------
-----------------------------    创建测试表--TEST2 、TEST3      --------------------------------

SQL> create table test2
  2  as
  3  select userid,trunc(id,-1) id2 from test1 where length(id)=4;

Table created

SQL> select * from test2;

    USERID        ID2
---------- ----------
       201         30
       202         30
       203         30
       204         30
       205         30
       206         30
       207         30
       208         30
       209         30
       211         30
       212         30
       213         30
       214         30
       215         30
       216         30
       217         30
       218         30
       219         30
       221         30
       222         30

    USERID        ID2
---------- ----------
       223         30
       224         30
       225         30
       226         30
       227         30
       228         30
       229         30

27 rows selected

SQL> create table test3
  2  as
  3  select userid,id id3 from test1 where length(id) <> 4;

Table created

SQL> select * from test3;

    USERID        ID3
---------- ----------
       200         30
       210         31
       220         32
       230         33
--------------------------------------------------------------------------------------------------------------
-----------------------------------      测试MERGE INTO            -----------------------------------

SQL> MERGE INTO test3 t
  2  USING (SELECT userid,id2 FROM test2 WHERE id2=30) tw
  3  ON (t.userid = tw.userid)
  4  WHEN MATCHED THEN UPDATE SET t.id3=tw.id2
  5  WHEN NOT MATCHED THEN INSERT VALUES(tw.userid,tw.id2);

Done

SQL> SELECT * FROM test3;

    USERID        ID3
---------- ----------
       200         30
       210         31
       220         32
       230         33
       202         30
       201         30
       204         30
       203         30
       228         30
       217         30
       209         30
       221         30
       219         30
       227         30
       205         30
       212         30
       225         30
       208         30
       222         30
       226         30

    USERID        ID3
---------- ----------
       215         30
       218         30
       214         30
       216         30
       211         30
       224         30
       213         30
       223         30
       206         30
       207         30
       229         30

31 rows selected

SQL>

--测试完成，我们看到，test2表中的数据全部加到test3表中。

PS:

   on(condition_clause)这里的条件句中，可以使用=，>,< 等比较运算符。

-------------------------------------          The  End        -------------------------------------------