ORACLE 10 g的 merge into 用法，oraclemerge

ORACLE 10 g的 merge into 用法，oraclemerge
在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