Oracle中merge用法

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。 通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。

语法

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;

1、UPDATE或INSERT子句是可选的 

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

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

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

首先创建示例表： 

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子句 

在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息: 

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

 

      3 rows merged. 

 

      SQL> SELECT * FROM products; 

 

      PRODUCT_ID PRODUCT_NAME CATEGORY 

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

      1501 VIVITAR 35MM ELECTRNCS 

      1502 OLYMPUS CAMERA ELECTRNCS 

      1600 PLAY GYM TOYS 

      1601 LAMAZE TOYS 

      1666 HARRY POTTER TOYS 

      SQL> 

      SQL> ROLLBACK; 

      Rollback complete. 

      SQL> 

在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种 类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中. 

 

SQL> MERGE INTO products p 

      2 USING newproducts np 

      3 ON (p.product_id = np.product_id) 

      4 WHEN NOT MATCHED THEN 

      5 INSERT 

      6 VALUES (np.product_id, np.product_name, 

      7 np.category); 

 

      1 row merged. 

 

      SQL> SELECT * FROM products; 

 

      PRODUCT_ID PRODUCT_NAME CATEGORY 

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

      1501 VIVITAR 35MM ELECTRNCS 

      1502 OLYMPUS IS50 ELECTRNCS 

      1600 PLAY GYM TOYS 

      1601 LAMAZE TOYS 

      1666 HARRY POTTER DVD 

      1700 WAIT INTERFACE BOOKS 

 

2、带条件的Updates和Inserts子句 

你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据, 但必须字段CATEGORY也得同时匹配上: 

SQL> 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 WHERE p.category = np.category; 

 

      2 rows merged. 

 

      SQL> SELECT * FROM products; 

 

      PRODUCT_ID PRODUCT_NAME CATEGORY 

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

      1501 VIVITAR 35MM ELECTRNCS 

      1502 OLYMPUS CAMERA ELECTRNCS 

      1600 PLAY GYM TOYS 

      1601 LAMAZE TOYS 

      1666 HARRY POTTER DVD 

      SQL> 

      SQL> rollback

在这个例子中, 产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据. 下面例子展示了在Updates和Inserts子句都使用WHERE子句: 

SQL> 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 VALUES (np.product_id, np.product_name, np.category) 

      12 WHERE np.category != 'BOOKS' 

      SQL> / 

 

      1 row merged. 

 

      SQL> SELECT * FROM products; 

 

      PRODUCT_ID PRODUCT_NAME CATEGORY 

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

      1501 VIVITAR 35MM ELECTRNCS 

      1502 OLYMPUS IS50 ELECTRNCS 

      1600 PLAY GYM TOYS 

      1601 LAMAZE TOYS 

      1666 HARRY POTTER TOYS 

 

      SQL> 

注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS. 

3、无条件的Inserts 

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

SQL> MERGE INTO products p 

      2 USING newproducts np 

      3 ON (1=0) 

      4 WHEN NOT MATCHED THEN 

      5 INSERT 

      6 VALUES (np.product_id, np.product_name, np.category) 

        SQL> / 

 

      1 row merged. 

 

      SQL> SELECT * FROM products; 

 

      PRODUCT_ID PRODUCT_NAME CATEGORY 

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

      1501 VIVITAR 35MM ELECTRNCS 

      1502 OLYMPUS IS50 ELECTRNCS 

      1600 PLAY GYM TOYS 

      1601 LAMAZE TOYS 

      1666 HARRY POTTER DVD 

      1700 WAIT INTERFACE BOOKS 

      6 rows selected. 

      SQL> 

4、新增加的DELETE子句 

 

Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除. 

下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行. 

SQL> 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 DELETE WHERE (p.category = 'ELECTRNCS') 

      9 WHEN NOT MATCHED THEN 

      10 INSERT 

      11 VALUES (np.product_id, np.product_name, np.category) 

      SQL> / 

      4 rows merged. 

      SQL> SELECT * FROM products; 

      PRODUCT_ID PRODUCT_NAME CATEGORY 

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

      1501 VIVITAR 35MM ELECTRNCS 

      1600 PLAY GYM TOYS 

      1601 LAMAZE TOYS 

      1666 HARRY POTTER TOYS 

      1700 WAIT INTERFACE BOOKS 

      SQL> 

 

产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值. 

 

 

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

1、UPDATE或INSERT子句是可选的

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

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

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

语法:

MERGE HINT 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中的

if exists(...)

update table

else

Insert into table.

merge into 语法不仅没有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;

 

优点：

 — 避免了分开更新

 — 提高性能并易于使用

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

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

需要注意的地方：

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

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

 

 

 

在Oracle 10g之前，merge语句支持匹配更新和不匹配插入2种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

 

参考Oracle 的SQL Reference，大家可以看到Merge Statement的语法如下：

MERGE [hint] 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;

 

下面我在windows xp 下10.2.0.1版本上做一个测试看看

 

SQL> select * from v$version;

 

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> 

一、创建测试用的表

SQL> create table subs(msid number(9),

  2                    ms_type char(1),

  3                    areacode number(3)

  4                    );

 

表已创建。

 

SQL> create table acct(msid number(9),

  2                    bill_month number(6),

  3                    areacode   number(3),

  4                    fee        number(8,2) default 0.00);

 

表已创建。

 

SQL> 

SQL> insert into subs values(905310001,0,531);

 

已创建 1 行。

 

SQL> insert into subs values(905320001,1,532);

 

已创建 1 行。

 

SQL> insert into subs values(905330001,2,533);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> 

 

 

二、下面先演示一下merge的基本功能

 

1) matched 和not matched clauses 同时使用

   merge into acct a 

     using subs b on (a.msid=b.msid)

   when MATCHED then

        update set a.areacode=b.areacode

   when NOT MATCHED then

        insert(msid,bill_month,areacode) 

        values(b.msid,'200702',b.areacode);

2) 只有not matched clause,也就是只插入不更新

   merge into acct a 

     using subs b on (a.msid=b.msid)   

   when NOT MATCHED then

        insert(msid,bill_month,areacode) 

        values(b.msid,'200702',b.areacode);

 

3) 只有matched clause, 也就是只更新不插入

   merge into acct a 

     using subs b on (a.msid=b.msid)

   when MATCHED then

        update set a.areacode=b.areacode

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 

Connected as study

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 

SQL> 

SQL> merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when MATCHED then

  4          update set a.areacode=b.areacode

  5     when NOT MATCHED then

  6          insert(msid,bill_month,areacode) 

  7          values(b.msid,'200702',b.areacode);

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905320001     200702      532       0.00

 905330001     200702      533       0.00

 905310001     200702      531       0.00

 

SQL> insert into subs values(905340001,3,534);

 

1 row inserted

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905340001 3            534

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> 

SQL> merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when NOT MATCHED then

  4          insert(msid,bill_month,areacode) 

  5          values(b.msid,'200702',b.areacode);

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905320001     200702      532       0.00

 905330001     200702      533       0.00

 905310001     200702      531       0.00

 905340001     200702      534       0.00

 

SQL> update subs set areacode=999;

 

4 rows updated

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905340001 3            999

 905310001 0            999

 905320001 1            999

 905330001 2            999

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905320001     200702      532       0.00

 905330001     200702      533       0.00

 905310001     200702      531       0.00

 905340001     200702      534       0.00

 

SQL> 

SQL> merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when MATCHED then

  4          update set a.areacode=b.areacode;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905320001     200702      999       0.00

 905330001     200702      999       0.00

 905310001     200702      999       0.00

 905340001     200702      999       0.00

 

SQL> 

 

三、10g中增强一：条件操作

 

1) matched 和not matched clauses 同时使用

   merge into acct a 

     using subs b on (a.msid=b.msid)     

   when MATCHED then

        update set a.areacode=b.areacode

        where b.ms_type=0

   when NOT MATCHED then

        insert(msid,bill_month,areacode) 

        values(b.msid,'200702',b.areacode)

        where b.ms_type=0;

2) 只有not matched clause,也就是只插入不更新

   merge into acct a 

     using subs b on (a.msid=b.msid)   

   when NOT MATCHED then

        insert(msid,bill_month,areacode) 

        values(b.msid,'200702',b.areacode)

        where b.ms_type=0;

 

3) 只有matched clause, 也就是只更新不插入

   merge into acct a 

     using subs b on (a.msid=b.msid)

   when MATCHED then

        update set a.areacode=b.areacode

        where b.ms_type=0;

 

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 

Connected as study

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 

SQL> 

SQL> merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when MATCHED then

  4          update set a.areacode=b.areacode

  5          where b.ms_type=0

  6     when NOT MATCHED then

  7          insert(msid,bill_month,areacode) 

  8          values(b.msid,'200702',b.areacode)

  9          where b.ms_type=0;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      531       0.00

 

SQL> insert into subs values(905360001,0,536);

 

1 row inserted

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905360001 0            536

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> 

SQL> merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when NOT MATCHED then

  4          insert(msid,bill_month,areacode) 

  5          values(b.msid,'200702',b.areacode)

  6          where b.ms_type=0;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      531       0.00

 905360001     200702      536       0.00

 

SQL> update subs set areacode=888 where ms_type=0;

 

2 rows updated

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905360001 0            888

 905310001 0            888

 905320001 1            532

 905330001 2            533

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      531       0.00

 905360001     200702      536       0.00

 

SQL> 

SQL> merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when MATCHED then

  4          update set a.areacode=b.areacode

  5          where b.ms_type=0;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      888       0.00

 905360001     200702      888       0.00

 

SQL> 

四、10g中增强二：删除操作

An optional DELETE WHERE clause can be used to clean up after a 

merge operation. Only those rows which match both the ON clause 

and the DELETE WHERE clause are deleted.

 

   merge into acct a 

     using subs b on (a.msid=b.msid)

   when MATCHED then

        update set a.areacode=b.areacode        

        delete where (b.ms_type!=0);             

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> select * from acct;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> 

SQL>  merge into acct a

  2       using subs b on (a.msid=b.msid)

  3     when MATCHED then

  4          update set a.areacode=b.areacode

  5          delete where (b.ms_type!=0);

 

Done

 

SQL> select * from acct;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 

SQL>

 

 

 

 

其实merge除了不能return,功能还是挺强的,希望下面的东西对大家有点帮助。

 

1、无条件的Inserts 

 

 

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

 

 

 

SQL> MERGE INTO products p

    2 USING newproducts np

    3 ON (1=0)

    4 WHEN NOT MATCHED THEN

    5 INSERT

    6 VALUES (np.product_id, np.product_name, np.category)

    7 WHERE np.category = 'BOOKS'

    SQL> /

    1 row merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY

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

    1501 VIVITAR 35MM ELECTRNCS

    1502 OLYMPUS IS50 ELECTRNCS

    1600 PLAY GYM TOYS

    1601 LAMAZE TOYS

    1666 HARRY POTTER DVD

    1700 WAIT INTERFACE BOOKS

    6 rows selected.

          SQL> 

 

 

 

 

2、新增加的DELETE子句 

 

 

Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除. 

 

 

下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行. 

 

 

SQL> 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 DELETE WHERE (p.category = 'ELECTRNCS')

    9 WHEN NOT MATCHED THEN

    10 INSERT

    11 VALUES (np.product_id, np.product_name, np.category)

    SQL> /

    4 rows merged.

    SQL> SELECT * FROM products;

    PRODUCT_ID PRODUCT_NAME CATEGORY

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

    1501 VIVITAR 35MM ELECTRNCS

    1600 PLAY GYM TOYS

    1601 LAMAZE TOYS

    1666 HARRY POTTER TOYS

    1700 WAIT INTERFACE BOOKS

          SQL> 

 

 

 

 

产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.

 

3. 再来个toad中的例子吧.

Examples 

Merging into a Table: Example The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

 

INSERT INTO bonuses(employee_id)

   (SELECT e.employee_id FROM employees e, orders o

   WHERE e.employee_id = o.sales_rep_id

   GROUP BY e.employee_id);

 

SELECT * FROM bonuses;

 

EMPLOYEE_ID      BONUS

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

        153        100

        154        100

        155        100

        156        100

        158        100

        159        100

        160        100

        161        100

        163        100

 

MERGE INTO bonuses D

   USING (SELECT employee_id, salary, department_id FROM employees

   WHERE department_id = 80) S

   ON (D.employee_id = S.employee_id)

   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01

     DELETE WHERE (S.salary > 8000)

   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)

     VALUES (S.employee_id, S.salary*0.1)

     WHERE (S.salary <= 8000);

 

EMPLOYEE_ID      BONUS

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

        153        180

        154        175

        155        170

        159        180

        160        175

        161        170

        179        620

        173        610

        165        680

        166        640

        164        720

        172        730

        167        620

        171        740

 

 

 

 

作用：merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表； 

 

语法： 

 

MERGE INTO [your table-name] [rename your table here] 

 

USING ( [write your query here] )[rename your query-sql and using just like a table] 

 

ON ([conditional expression here] AND [...]...) 

 

WHEN MATHED THEN [here you can execute some update sql or something else ] 

 

WHEN NOT MATHED THEN [execute something else here ! ] 

 

-------------------------------------实例----------------------------------------------------------------- 

 

merge into tfa_alarm_act_nms a 

using (select FP0,FP1,FP2,FP3,REDEFINE_SEVERITY 

from tfa_alarm_status) b 

on (a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3) 

when matched then update set a.redefine_severity=b.redefine_severity 

when not matched then insert (a.fp0,a.fp1,a.fp2,a.fp3,a.org_severity,a.redefine_severity,a.event_time 

,a.int_id) 

values (b.fp0,b.fp1,b.fp2,b.fp3,b.REDEFINE_SEVERITY,b.redefine_severity,sysdate,7777778); 

 

作用：利用表 tfa_alarm_status跟新表tfa_alarm_act_nms 的b.redefine_severity，条件是a.fp0=b.fp0 and a.fp1=b.fp1 and a.fp2=b.fp2 and a.fp3=b.fp3，如果tfa_alarm_act_nms表中没有该条件的数据就插入。 

 

如果你的数据量很大，此sql效率非常高。 

 

把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLE DBA都会经常碰到的问题。在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。现在ORACLE9i专为这种情况提供了MERGE语句，使这一工作变得异常轻松。

在Oracle 10g中MERGE有如下一些改进： 

1、UPDATE或INSERT子句是可选的 

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

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

SQL> 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 VALUES (np.product_id, np.product_name, np.category) 

      12 WHERE np.category != 'BOOKS' 

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

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

SQL> 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 DELETE WHERE (p.category = 'ELECTRNCS') 

      9 WHEN NOT MATCHED THEN 

      10 INSERT 

      11 VALUES (np.product_id, np.product_name, np.category) 

 

 

Merge用来从一个表中选择一些数据更新或者插入到另一个表中。而最终是用更新还是用插入的方式取决于该语句中的条件。

下面我们简单的举一个例子：

SQL> create table merge_test1(a number,b varchar2(20))

表已创建。

SQL> create table merge_test2(a number,b varchar2(20))

表已创建。

SQL> insert into merge_test1 values(1,'A');

已创建 1 行。

SQL> insert into merge_test1 values(2,'B');

已创建 1 行。

SQL> insert into merge_test1 values(3,'C');

已创建 1 行。

SQL> insert into merge_test2 values(3,'C');

已创建 1 行。

SQL> insert into merge_test2 values(2,'C');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from merge_test1;

         A B

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

         1 A

         2 B

         3 C

SQL> select * from merge_test2;

         A B

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

         3 C

         2 C

前面的准备工作之后，用Merge对 Merge_test2进行更新

MERGE INTO MERGE_TEST2 M

USING MERGE_TEST1 N

ON (M.A = N.A)

WHEN MATCHED THEN

UPDATE SET M.B = N.B

WHEN NOT MATCHED THEN

INSERT (M.A,M.B) VALUES (N.A,N.B);

更新后结果：

SQL> select * from merge_test2;

         A B

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

         1 A

         3 C

         2 B

总结：

一、Merge 的用法大致上有三种:

1、只更新不插入

Merge into Merge_test2 M

Using Merge_test1 N

ON (M.A = N.A)

WHEN MATCHED THEN

UPDATE SET M.B = N.B;

2、只插入不更新

MERGE INTO merge_test2 M

USING merge_test1 N

ON (M.A =N.A) 

WHEN NOT MATCHED THEN

INSERT (M.A,M.B) VALUES(N.A,N.B);

3、既插入也更新

MERGE INTO merge_test2 M

USING merge_test1 N

ON(M.A = N.A)

WHEN  MATCHED THEN

UPDATE SET M.B=N.B

WHEN NOT MATCHED THEN

INSERT (M.A,M.B) VALUES(N.A,N.B)

二、注意事项

1、merge语句中，update不能用于更新连接的列(即ON(M.A = N.A))

例如：

MERGE INTO merge_test2 M

USING merge_test1 N

ON(M.A = N.A)

WHEN  MATCHED THEN

UPDATE SET M.A=N.B ,M.B=N.B

WHEN NOT MATCHED THEN

INSERT (M.A,M.B) VALUES(N.A,N.B)

我们执行上面的语句(注意红色部分)就会有如下的提示：

第 3 行出现错误:

ORA-38104: 无法更新 ON 子句中引用的列: "M"."A"

2、Using 后面不一定是表：

语法文档上写着：table,view,subquery

也就是说也可以是一个查询语句的结果集。

3、还有一种错误就是由于关联的值不唯一引起的

比如：

在merge_test1中再插入一条数据。与之前的重复了！

SQL> insert into merge_test1 values(3,'d');

此时我们执行

MERGE INTO merge_test2 M

USING merge_test1 N

ON(M.A = N.A)

WHEN  MATCHED THEN

UPDATE SET M.B=N.B

WHEN NOT MATCHED THEN

INSERT (M.A,M.B) VALUES(N.A,N.B)

第 1 行出现错误:

ORA-30926: 无法在源表中获得一组稳定的行

 

 

into 子句 

在into子句中指定所要修改或者插入数据的目标表

using 子句 

在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。

on 子句 

在on子句中指定执行插入或者修改的满足条件。在目标表中符合条件的每一行，oracle用数据源中的相应数据修改这些行。对于不满足条件的那些行，oracle则插入数据源中相应数据。

when matched | not matched 

用该子句通知oracle如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。

merge_update子句 

merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。

限制：当修改一个视图时，不能指定一个default值

merge_insert 子句 

merge_insert子句执行当不符合on子句条件时，往目标表中插入数据。如果插入子句执行，则目标表上插入触发器将被触发。

限制：当修改一个视图时，不能指定一个default值

以下是准备数据：就是创建两个表，aarontest表记录人员id，人员姓名和工资；mergetest表记录了人员id，奖金比例和奖金金额。

CREATE TABLE aarontest(

id INT PRIMARY KEY,

name VARCHAR2(100),

salary NUMERIC(10,2)

);

CREATE TABLE mergetest(

id INT PRIMARY KEY,

bonusrate FLOAT,

bonus NUMERIC(10,2)

);

COMMIT;

INSERT INTO aarontest VALUES (1, 'aaron1', 1000);

INSERT INTO aarontest VALUES (2, 'aaron2', 2000);

INSERT INTO aarontest VALUES (3, 'aaron3', 3000);

INSERT INTO aarontest VALUES (4, 'aaron4', 4000);

INSERT INTO aarontest VALUES (5, 'aaron5', 5000);

INSERT INTO mergetest VALUES (1, 0.1, 0);

INSERT INTO mergetest VALUES (3, 0.3, 0);

INSERT INTO mergetest VALUES (5, 0.4, 0);

COMMIT;

现在的数据状态：

aarontest表：

1 aaron1 1000 

2 aaron2 2000 

3 aaron3 3000 

4 aaron4 4000 

5 aaron5 5000

mergetest表：

1 0.1 0 

3 0.3 0 

5 0.4 0

我们的merge语句的功能就是，更新mergetest表，如果遇到和aarontest里相对应的id，就用aarontest里的工资乘以mergetest表里的奖金比例，将其结果存入mergetest表中的奖金一栏；如果没有找到，就用aarontest里的工资乘以固定的0.1结果存入mergetest表。

MERGE INTO mergetest t

USING (SELECT id, name, salary FROM aarontest) aaron

ON (t.id = aaron.id)

WHEN MATCHED THEN

UPDATE SET t.bonus = t.bonusrate * aaron.salary

WHEN NOT MATCHED THEN

INSERT (t.id, t.bonusrate, t.bonus) VALUES (aaron.id, 0.1, aaron.salary * 0.1);

COMMIT;

执行Merge语句以后的数据状态：

1 0.1 100 

3 0.3 900 

5 0.4 2000 

2 0.1 200 

4 0.1 400

很简单，仅用作个人测试。。。。

 

 

 

 在平时更新数据时，经常有这样一种更新，即将目标表中的数据与源表对比，如果存在记录，则根据源表中的值更新目标表中的数据，如果不存在的话，则新增入目标表中。我们当然可以使用两条语句来处理这类数据。但这其中有可能会出现异常。因此，Oracle在9i版本新增了MERGE语句，来合并UPDATE和INSERT语句。 该语句可以在同一语句中执行两步操作，可以减少执行多条insert 和update语句。merge是一个确定性的语句，即不会在同一条merge语句中去对同一条记录多次做修改操作。

       具体语法为： 

    MERGE [hint] 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; 

1.into 子句

在into子句中指定所要修改或者插入数据的目标表

2.using 子句

在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。

3.on 子句

在on子句中指定执行插入或者修改的满足条件。在目标表中符合条件的每一行，oracle用数据源中的相应数据修改这些行。对于不满足条件的那些行，oracle则插入数据源中相应数据。

4.when matched | not matched

用该子句通知oracle如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。

5.merge_update子句

merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。

限制：当修改一个视图时，不能指定一个default值

6.merge_insert 子句

merge_insert子句执行当不符合on子句条件时，往目标表中插入数据。

 

下面列出merge的基本用法 （此处目标表和源表表结构相同） 

  1) matched 和not matched 同时使用 

   merge into 目标表  a 

     using 源表 b on (关联条件 a.字段1 = b.字段1) 

   when MATCHED then 

        update set a.字段2=b.字段2,...... 

   when NOT MATCHED then 

        insert(a.字段2,a.字段3) 

        values(b.字段2,b.字段3); 

  2) 只有not matched clause,也就是只插入不更新 

   merge into 目标表 a 

     using  源表b on (关联条件 a.字段1 = b.字段1)   

   when NOT MATCHED then 

       insert(a.字段2,a.字段3) 

        values(b.字段2,b.字段3); 

 

  3) 只有matched clause, 也就是只更新不插入 

   merge into 目标表  a

 

 

 

 

Oracle 9i后的Merge语句剖析

 

动机： 

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

说明： 

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

实战： 

接下来我们有一个任务，有一个表T，有两个字段a,b，我们想在表T中做Insert/Update,如果存在，则更新T中b的值，如果不存在，则插入一条记录。在Microsoft的SQL语法中，很简单的一句判断就可以了，SQL Server中的语法如下： 

if exists(select 1 from T where T.a=''1001'' ) update T set T.b=2 Where T.a=''1001'' else insert into T(a,b) values(''1001'',2); 

以上语句表明当T表中如果存在a=''1001'' 的记录的话，就把b的值设为2，否则就Insert一条a=''100''，b=2的记录到T中。 

但是接下来在Oracle中就遇到麻烦了，记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗，Merge的语法如下： 

 

MERGE INTO table_name alias1 

USING (table|view|sub_query) alias2 

ON (join condition) 

WHEN MATCHED THEN 

UPDATE table_name 

SET col1 = col_val1, 

col2 = col2_val 

WHEN NOT MATCHED THEN 

INSERT (column_list) VALUES (column_values); 

 

上面的语法大家应该都容易懂吧，那我们按照以上的逻辑再写一次。 

 

MERGE INTO T T1 

USING (SELECT a,b FROM T WHERE t.a=''1001'') T2 

ON ( T1.a=T2.a) 

WHEN MATCHED THEN 

UPDATE SET T1.b = 2 

WHEN NOT MATCHED THEN 

INSERT (a,b) VALUES(''1001'',2); 

以上的语句貌似很对是吧，实际上，该语句只能进行更新，而无法进行Insert，错误在哪里呢？ 

其实在Oracle中Merge语句原先是用来进行整表的更新用的，也就是ETL工具比较常用的语法，重点是在Using上。 

用中文来解释Merge语法，就是： 

在alias2中Select出来的数据，每一条都跟alias1进行 ON (join condition)的比较，如果匹配，就进行更新的操作(Update),如果不匹配，就进行插入操作(Insert)。 

因此，严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数。” 

以上这句话也就很好的解释了在上面写的语句为何只能进行Update，而不能进行Insert了，因为都Select不到数据，如何能进行Insert呢:) 

接下来要改成正确的语句就容易多了，如下： 

 

MERGE INTO T T1 

USING (SELECT ''1001'' AS a,2 AS b FROM dual) T2 

ON ( T1.a=T2.a) 

WHEN MATCHED THEN 

UPDATE SET T1.b = T2.b 

WHEN NOT MATCHED THEN 

INSERT (a,b) VALUES(T2.a,T2.b); 

查询结果，OK! 

注意： 

如果不懂Merge语句的原理，Merge语句是一条比较危险的语句，特别是在您只想更新一条记录的时候，因为不经意间，你可能就把整表的数据都Update了一遍.....汗!!! 

我曾经犯过的一个错误如下所示，大家看出来是什么问题了吗？ 

 

MERGE INTO T T1 

USING (SELECT Count(*) cnt FROM T WHERE T.a=''1001'') T2 

ON (T2.cnt>0) 

WHEN MATCHED THEN 

UPDATE SET T1.b = T2.b 

WHEN NOT MATCHED THEN 

INSERT (a,b) VALUES(T2.a,T2.b);