1.merge into作用

    在执行大批量的插入更新操作时，按照原有的方式插入或更新效率可能比较低下，而且如果插入多张表的数据时，可能因为违反唯一约束而导致插入失败。

    merge into是9i出现的新功能，可以实现在插入数据时，指定某个字段，如果插入的数据已经存在时，进行update的更新或delete删除或不进行操作，然后在插入没有的数据，简单点说就是“有就更新/删除/不操作，无则插入”。

    10g之后，mege into做了写改进，改进如下：

1.update或insert子句是可选的

2.update和insert子句可以加where子句

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

4.update子句后面可以跟delete自己来取出一些不需要的行

 

2.merge into 语法

 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}

 

 

3.示列

----环境准备

----创建第一张表

create table shall

 (id number not null,

  name varchar2(20),

  constraint shall_id primary key(id));

 

begin

  for i in 1..10000 loop

   insert into shall values(i,'zhong');

  end loop;

end;

/

 

SQL> select count(*) from shall;

  COUNT(*)

----------

     10000

 

 

----创建第二张表

SQL> create table shall2 as select * from shall where 1=2;

Table created.

 

SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='SHALL2';

no rows selected

----没有唯一索引

SQL> alter table shall2 add constraint shall2_id primary key(id);

Table altered.

 

begin

  for i in 9000..10002 loop

   insert into shall2 values(i,'shall');

  end loop;

end;

/

 

SQL> select count(*) from shall2;

  COUNT(*)

----------

      1003

 

 

----创建第三张表

SQL> create table shall3 as select * from shall where 1=2;

Table created.

 

SQL> alter table shall3 add constraint shall3_id primary key(id);

Table altered.

 

select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='SHALL3';

 

----计算数据量（复习集合操作函数）

----shall和shall2表id都有的数据有多少 1001条

SQL> select count(*) from shall t1,shall2 t2 where t1.id=t2.id;

  COUNT(*)

----------

      1001

 

select count(*) from

  (select id from shall

    intersect

   select id from shall2);

 

----shall表中有的id，而shall2中没有的数据量 8999条

SQL> select count(*) from shall where id not in (select id from shall2);

  COUNT(*)

----------

      8999

 

select count(*) from

  (select id from shall

    minus

   select id from shall2);

 

----shall2表中有的id，而shall中没有的数据量 2条

SQL> select count(*) from shall2 where id not in (select id from shall);

  COUNT(*)

----------

         2

 

select count(*) from

  (select id from shall2

    minus

   select id from shall);

 

----shall和shall2中id不重复的数据量 10002条

SQL> select count(*) from shall t1 full join shall2 t2 on t1.id=t2.id;

  COUNT(*)

----------

     10002

 

select count(*) from

  (select id from shall2

    union

   select id from shall);

 

----shall和shall2中共有多少数据 11003条

SQL> select (select count(*) from shall) + (select count(*) from shall2) total from dual;

     TOTAL

----------

     11003

 

select count(*) from

  (select id from shall2

    union all

   select id from shall);

 

----下面开始演示merge into

----先将表shall插入到shall3表中。当插入shall3中已经存在的id，不覆盖数据

merge into shall3 t3

  using (select id,name from shall) t

 on (t3.id=t.id)

 when matched then update set t3.name=t.name where 1=2

 when not matched then insert values (t.id,t.name);

 

10000 rows merged.

 

----再将shall2插入到shall3表中。一样，当shall3中已经存在id号的，不覆盖数据

merge into shall3 t3

  using (select id,name from shall2) t

 on (t3.id=t.id)

 when matched then update set t3.name=t.name where 1=2

 when not matched then insert values (t.id,t.name);

 

2 rows merged.

 

----验证一下，是不是第二次插入数据只插入了最后两行

SQL> select * from (select * from shall3 order by id desc) where rownum<5;

        ID NAME

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

     10002 shall

     10001 shall

     10000 zhong

      9999 zhong

 

 

----再次执行merge into插入shall2表数据，要求当shall3表中id存在的时候，更新name字段

merge into shall3 t3

  using (select id,name from shall2) t

 on (t3.id=t.id)

 when matched then update set t3.name=t.name

 when not matched then insert values (t.id,t.name);

 

1003 rows merged.

 

----验证一下，是不是第二次插入数据只插入了最后两行

SQL> select * from (select * from shall3 order by id desc) where rownum<5;

        ID NAME

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

     10002 shall

     10001 shall

     10000 shall

      9999 shall

SQL> select * from shall3 where id between 8999 and 9000;

        ID NAME

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

      8999 zhong

      9000 shall

 

 

----最后试试在on条件中加入 > 或< 条件看看效果

merge into shall3 t3

  using (select id,name from shall) t

 on (t3.id>t.id)

 when matched then update set t3.name=t.name where 1=2

 when not matched then insert values (t.id,t.name);

 

 

4.merge into 10g新特性演示

----语法如下 

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 ! ] 

 

4.1 UPDATE或INSERT子句是可选的 

    在9i里由于必须insert into和update都要存在，也就是不是update就是insert，不支持单一的操作，虽然还是可以曲线救国，呵呵 但是有些过于强势了。而10g里就是可选了，能符合我们更多的需求了 

比如 

我们可以只存在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 

这里，如果匹配就更新，不存在就不管了。 

 

4.2 UPDATE和INSERT子句可以加WHERE子句 

    这也是一个功能性的改进，能够符合我们更多的需求，这个where的作用很明显是一个过滤的条件，是我们加入一些额外的条件，对只对满足where条件的进行更新和insert 

merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 

when matched then 

update set p.product_name = np.product_name where np.product_name like 'OL%' 

这里表示只是对product_name开头是'OL'的匹配上的进行update，如果开头不是'OL'的就是匹配了也不做什么事情，insert里也可以加入where 

比如 

merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 

when matched then 

update set p.product_name = np.product_name where np.product_name like 'OL%' 

when not matched then 

insert values(np.product_id, np.product_name, np.category) where np.product_name like 'OL%' 

 

这里注意比较一下，他们返回的结果行数，是有着差异的。 

 

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

 

merge into products p using (select * from newproducts) np on (1=0) 

when matched then 

update set p.product_name = np.product_name 

when not matched then 

insert values(np.product_id, np.product_name, np.category) 

个人觉得这个功能没有太大的意义，我们的insert into本身就支持这样的功能，没有必要使用merge 

 

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

delete只能和update配合，从而达到删除满足where条件的子句的纪录 

merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 

when matched then 

update set p.product_name = np.product_name delete where p.product_id = np.product_id where np.product_name like 'OL%' 

when not matched then 

insert values(np.product_id, np.product_name, np.category) 

这里我们达到的目的就是 会把匹配的记录的prodcut_name更新到product里，并且把product_name开头为OL的删除掉。

 

merge into也是一个dml语句，和其他的dml语句一样需要通过rollback和commit 结束事务。 

 

 

 

 

reference    http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

http://blog.csdn.net/jackpk/article/details/50336941