merge into语句

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;

--MATCHED  update匹配更新
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