oracle merge into 用法详解

    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;

1.可省略的UPDATE或INSERT子句

update使用，省略insert：

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;

insert使用，省略update：MERGE INTO products p
    USING newproducts np
    ON (p.product_id = np.product_id)
    WHEN NOT MATCHED THEN
    INSERT
    VALUES (np.product_id, np.product_name,np.category);

2、带条件的Updates和Inserts子句

你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert操作对某些行的处理.

下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据，根据条件category进行更新：

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)
 WHEN MATCHED THEN
 UPDATE
 SET p.product_name = np.product_name,
 p.category = np.category
WHERE p.category = ‘DVD‘
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category)
WHERE np.category != ‘BOOKS‘

3.两表连接无条件的Inserts
你能够不用连接源表和目标表就把源表的数据插入到目标表中. 这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON (1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:

SQL> MERGE INTO products p
    USING newproducts np
   ON (1=0)
   WHEN NOT MATCHED THEN
   INSERT
   VALUES (np.product_id, np.product_name, np.category)
   WHERE np.category = ‘BOOKS‘

oracle merge into 用法详解

标签：