02_oracle中merge into用法解析

–oracle中merge into用法解析
–oracle在9i引入了merge，在oracle10g中merge有如下改进
–1.update或insert子句是可选的
–2.update和insert子句是可选的
–3.在on条件中使用常量过滤谓词来insert所有行到目标表中，不需要连接源表和目标表。
–4.update子句后面可以跟delete子句来除一些不需要的行
merge into 的形式
merge into [target-table] a using [source-table sql] b on([conditional expression] and […]…)
when matched then
[update sql]
when not matched then
[insert sql]
作用：判断B表和A表是否满足ON中的条件，如果满足则用B去更新A表，
如果不满足，则将B表数据插入A表。但是有很多选项，如下：
1.正常模式
2.只update或者只insert
3.带条件的update或带条件的insert
4.全插入insert实现
5.带delete的update
–1.正常模式
–1.测试建以下表
create table a_merge(id number,name varchar2(12),year number);
create table b_merge(id number,aid number,name varchar2(12),year number,city varchar2(12));
create table c_merge(id number,name varchar2(12),city varchar2(12));
–2.预埋数据
truncate table a_merge;
insert into a_merge values(1,‘liuwei’,20);
insert into a_merge values(2,‘zhangbin’,21);
insert into a_merge values(3,‘fuguo’,20);

truncate table b_merge;
insert into b_merge values(1,2,‘zhangbin’,33,‘colorado’);
insert into b_merge values(2,4,‘yihe’,33,‘virgin’);
insert into b_merge values(3,3,‘fuguo’,55,‘maine’);
insert into b_merge values(11,11,‘ligd’,55,‘vermont’);
insert into b_merge values(12,22,‘test’,55,‘montana’);
commit;
–3.使用merge into 用b_merge来更新a_merge中的数据
Merge into a_merge a
using (select b.aid, b.name, b.year from b_merge b) c
on (a.id = c.aid)
when matched then
update set a.year = c.year
when not matched then
insert (a.id, a.name, a.year) values (c.aid, c.name, c.year);
commit;
–4.查询结果
select * from a_merge;
select * from b_merge;

–2.只update或者只insert模式
–2.1只update
Merge into a_merge a
using (select b.aid, b.name, b.year from b_merge b) c
on (a.id = c.aid)
when matched then
update set a.year = c.year;
commit;
–2.2只insert
Merge into a_merge a
using (select b.aid, b.name, b.year from b_merge b) c
on (a.id = c.aid)
when not matched then
insert (a.id, a.name, a.year) values (c.aid, c.name, c.year);
commit;

–3.带where条件的insert和update模式
Merge into a_merge a
using (select b.aid, b.name, b.year,b.city from b_merge b) c
on (a.id = c.aid)
when matched then
update set a.year = c.year where c.city=‘vermont’
when not matched then
insert (a.id, a.name, a.year) values (c.aid, c.name, c.year) where c.city=‘vermont’;
commit;

–4.无条件的insert
Merge into a_merge a
using (select b.aid, b.name, b.year,b.city from b_merge b) c
on (1=2)
when not matched then
insert (a.id, a.name, a.year) values (c.aid, c.name, c.year);
commit;
–4.带delete的update
Merge into a_merge a
using (select b.aid, b.name, b.year,b.city from b_merge b) c
on (1=1)
when matched then
update set a.year = c.year where c.city=‘vermont’
delete where a.id=1;
commit;