oracle merge into 用法详解

逃离我推掉我的手 2022-04-01 05:09 227阅读 0赞

# [oracle merge into 用法详解][oracle merge into] #

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操作。

首先创建示例表：  
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;

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]: https://www.cnblogs.com/kkfish/p/5264516.html