merge into语句介绍:
merge into可以同时执行insert和update语句,支持
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;
创建基础表:
create table test02 as select pri_number,loan_contract_no,loan_name_phone,id_card,occupation,loan_purpose from test01;
create table test03 as select pri_number,loan_contract_no,loan_name_phone,id_card,occupation,loan_purpose from test01 where 1=2;
select * from test01;
select count(*) from test02;
select count(*) from test03;
-- NOT MATCHED insert不匹配插入
merge into test03 new
using test02 old
on (new.pri_number=old.pri_number and new.loan_contract_no=old.loan_contract_no)
when not matched then
insert
(pri_number,loan_contract_no,loan_name_phone,id_card,occupation,loan_purpose)
values(old.pri_number,old.loan_contract_no,old.loan_name_phone,old.id_card,old.occupation,old.loan_purpose);
commit;
当不匹配时插入
select * from test03;
alter table test02 add (loan_name varchar(20));
alter table test02 add (business_manager varchar(20));-----增加两个字段
merge into test02 new
using test01 old
on (new.pri_number=old.pri_number and new.loan_contract_no=old.loan_contract_no)
when matched then
update
set new.loan_name=old.loan_name,
new.business_manager=old.business_manager;
commit;
--MATCHED update匹配更新(方法同上)
merge into test02 new
using test01 old
on (new.pri_number=old.pri_number )
when matched then
update
set new.loan_name=old.loan_name,
new.business_manager=old.business_manager
where
new.loan_contract_no=old.loan_contract_no;
commit;
当匹配时更新数据
--带where条件的insert和update
alter table test03 add (loan_name varchar(20));
alter table test03 add (business_manager varchar(20));---test03表加入两个字段
delete test03 where rownum<10000; ---清除rownum小于10000的行
merge into test03 new
using test02 old
on (new.pri_number=old.pri_number )
when matched then
update set
new.loan_name=old.loan_name,
new.business_manager=old.business_manager
where
new.loan_purpose is not null
when not matched then
insert
(pri_number,loan_contract_no,LOAN_NAME_PHONE,id_card,occupation,loan_purpose,loan_name,business_manager)
values
(old.pri_number,old.loan_contract_no,old.LOAN_NAME_PHONE,old.id_card,old.occupation,old.loan_purpose,old.loan_name,old.business_manager)
where
old.loan_purpose is not null;
commit;
select * from test03 where loan_purpose is null
--无条件的insert,常量,在on使用1=2,插入式将不用连接表,不检查表test03的行是否存在
merge into test03 new
using test02 old
on (1=2)
when not matched then
insert
(pri_number,loan_contract_no,LOAN_NAME_PHONE,id_card,occupation,loan_purpose,loan_name,business_manager)
values
(old.pri_number,old.loan_contract_no,old.LOAN_NAME_PHONE,old.id_card,old.occupation,old.loan_purpose,old.loan_name,old.business_manager)
验证
select * from test03 where loan_purpose is null
--delete 语句
merge into test03 new
using test02 old
on (new.pri_number=old.pri_number )
when matched then
update set
new.loan_name=old.loan_name,
new.business_manager=old.business_manager
delete where(new.loan_purpose is null)
when not matched then
insert
(pri_number,loan_contract_no,LOAN_NAME_PHONE,id_card,occupation,loan_purpose,loan_name,business_manager)
values
(old.pri_number,old.loan_contract_no,old.LOAN_NAME_PHONE,old.id_card,old.occupation,old.loan_purpose,old.loan_name,old.business_manager)
where
old.loan_purpose is not null;
删除new.loan_purpose为空的行
验证
select * from test03 where loan_purpose is null--存储过程
create or replace procedure merge_test
is
begin
merge into test03 new
using test02 old
on (new.pri_number=old.pri_number )
when matched then
update set
new.loan_name=old.loan_name,
new.business_manager=old.business_manager
delete where(new.loan_purpose is null)
when not matched then
insert
(pri_number,loan_contract_no,LOAN_NAME_PHONE,id_card,occupation,loan_purpose,loan_name,business_manager)
values
(old.pri_number,old.loan_contract_no,old.LOAN_NAME_PHONE,old.id_card,old.occupation,old.loan_purpose,old.loan_name,old.business_manager)
where
old.loan_purpose is not null;
commit;
exception
when others then
rollback;
end;
执行存储过程
begin
merge_test;
end;
验证
select count(*) from test03;
select * from test03 where loan_purpose is null
以上参考:
http://blog.sina.com.cn/s/blog_634c33eb0101603k.html