ORACLE10g的mergeinto用法

ORACLE10g的mergeinto用法

在Oracle 10g之前，merge語句支持匹配更新和不匹配插入2種簡單的用法，在10g中Oracle對merge語句做了增強，增加了條件選項和DELETE操作。下面我通過一個demo來簡單介紹一下10g中merge的增強和10g前merge的用法。
　
參考Oracle 的SQL Reference 下面我在一下環境中做一個測試看看

創建表subs 和 acct

 

create table subs(
       msid     number(9),
       ms_type  char(1),
       areacode number(3)
);

create table acct(
       msid       number(9),
       bill_month number(6),
       areacode   number(3),
       fee        number(8,2) default 0.00
);

插入數據

 

 

insert into subs values(905310001,0,531);
insert into subs values(905320001,1,532);
insert into subs values(905330001,2,533);
commit

 

語法

--  語法
merge [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;

測試

 

 

---  matched：更新    not matched：插入  兩者可以同步執行也可以只要一個條件
merge into acct a
   using subs b
      on (a.msid = b.msid)
         when matched then
            update set a.areacode = 22 
         when not matched then
            insert (msid, bill_month, areacode) values (b.msid, '200702', b.areacode);
commit

增強條件查詢操作

 

 

merge into acct a
   using subs b
      on (a.msid = b.msid)
         when matched then
            update set a.areacode = 22 where b.ms_type = 0
         when not matched then
            insert (msid, bill_month, areacode) values (b.msid, '200702', b.areacode) where b.ms_type = 0;
commit

增強刪除操作

merge into acct a
   using subs b
      on (a.msid = b.msid)
         when matched then
            update set a.areacode = 22
            delete where (b.ms_type != 0);
commit