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.youkuaiyun.com/jackpk/article/details/50336941
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2123300/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2123300/
扫一扫
1537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
