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子句来去除一些不需要的行

语法:

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_DELETE_CLAUSE;

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

ifexists(...)

updatetable

else

Insertinto table.

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

例子：

1、创建测试表及数据

    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、匹配更新


    MERGE INTO PRODUCTS P
    USING NEWPRODUCTS NP
    ON (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、不匹配插入


    MERGE INTO PRODUCTS P
    USING NEWPRODUCTS NP
    ON (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更新

    MERGE INTO PRODUCTS P
    USING NEWPRODUCTS NP
    ON (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 P
    USING NEWPRODUCTS NP
    ON (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更新、插入

    MERGE INTO PRODUCTS P
    USING NEWPRODUCTS NP
    ON (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常量表达式

    MERGE INTO PRODUCTS P
    USING NEWPRODUCTS NP
    ON (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、匹配删除、不匹配插入

    MERGE INTO PRODUCTS P
    USING NEWPRODUCTS NP
    ON (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、源表为子查询(自联接)

    MERGE INTO PRODUCTS P
    USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1501) B
    ON (B.CO <> 0)
    WHEN MATCHED THEN
      UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1501
    WHEN NOT MATCHED THEN
      INSERT
        (PRODUCT_ID
        ,PRODUCT_NAME
        ,CATEGORY)
      VALUES
        (1501
        ,'KEBO'
        ,'NBA');

    MERGE INTO PRODUCTS P
    USING (SELECT COUNT(*) CO FROM PRODUCTS WHERE PRODUCTS.PRODUCT_ID = 1508) B
    ON (B.CO <> 0)
    WHEN MATCHED THEN
      UPDATE SET P.PRODUCT_NAME = 'KEBO' WHERE P.PRODUCT_ID = 1508
    WHEN NOT MATCHED THEN
      INSERT
        (PRODUCT_ID
        ,PRODUCT_NAME
        ,CATEGORY)
      VALUES
        (1508
        ,'KEBO'
        ,'NBA');

    SELECT * FROM PRODUCTS;

9.带delete的merge

MERGE INTO PRODUCTS P
USING NEWPRODUCTS NP
ON (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;

优点：

 —避免了分开更新

 —提高性能并易于使用

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

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

需要注意的地方：

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

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

转自： http://blog.csdn.net/nsj820/article/details/5755685