| oracle10g的merge功能增強(qiáng)(轉(zhuǎn)) =========================================================== 作者: zhouwf0726(http://zhouwf0726.) 發(fā)表于:2007.03.14 16:10 分類: oracle開發(fā) 出處：http://zhouwf0726./post/9689/271773 --------------------------------------------------------------- 在Oracle 10g之前，merge語句支持匹配更新和不匹配插入2種簡單的用法，在10g中Oracle對(duì)merge語句做了增強(qiáng)，增加了條件選項(xiàng)和DELETE操作。下面我通過一個(gè)demo來簡單介紹一下10g中merge的增強(qiáng)和10g前merge的用法。 參考Oracle 的SQL Reference，大家可以看到Merge Statement的語法如下： 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; 下面我在windows xp 下10.2.0.1版本上做一個(gè)測(cè)試看看 SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production SQL> 一、創(chuàng)建測(cè)試用的表 SQL> create table subs(msid number(9), 2 ms_type char(1), 3 areacode number(3) 4 ); 表已創(chuàng)建。 SQL> create table acct(msid number(9), 2 bill_month number(6), 3 areacode number(3), 4 fee number(8,2) default 0.00); 表已創(chuàng)建。 SQL> SQL> insert into subs values(905310001,0,531); 已創(chuàng)建 1 行。 SQL> insert into subs values(905320001,1,532); 已創(chuàng)建 1 行。 SQL> insert into subs values(905330001,2,533); 已創(chuàng)建 1 行。 SQL> commit; 提交完成。 SQL> 二、下面先演示一下merge的基本功能 1) matched 和not matched clauses 同時(shí)使用 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode); 2) 只有not matched clause,也就是只插入不更新 merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode); 3) 只有matched clause, 也就是只更新不插入 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as study SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533 SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 when NOT MATCHED then 6 insert(msid,bill_month,areacode) 7 values(b.msid,'200702',b.areacode); Done SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 SQL> insert into subs values(905340001,3,534); 1 row inserted SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905340001 3 534 905310001 0 531 905320001 1 532 905330001 2 533 SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when NOT MATCHED then 4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode); Done SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534 0.00 SQL> update subs set areacode=999; 4 rows updated SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905340001 3 999 905310001 0 999 905320001 1 999 905330001 2 999 SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534 0.00 SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode; Done SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905320001 200702 999 0.00 905330001 200702 999 0.00 905310001 200702 999 0.00 905340001 200702 999 0.00 SQL> 三、10g中增強(qiáng)一：條件操作 1) matched 和not matched clauses 同時(shí)使用 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode 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; 2) 只有not matched clause,也就是只插入不更新 merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode) where b.ms_type=0; 3) 只有matched clause, 也就是只更新不插入 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode where b.ms_type=0; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as study SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533 SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 where b.ms_type=0 6 when NOT MATCHED then 7 insert(msid,bill_month,areacode) 8 values(b.msid,'200702',b.areacode) 9 where b.ms_type=0; Done SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905310001 200702 531 0.00 SQL> insert into subs values(905360001,0,536); 1 row inserted SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905360001 0 536 905310001 0 531 905320001 1 532 905330001 2 533 SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when NOT MATCHED then 4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode) 6 where b.ms_type=0; Done SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905310001 200702 531 0.00 905360001 200702 536 0.00 SQL> update subs set areacode=888 where ms_type=0; 2 rows updated SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905360001 0 888 905310001 0 888 905320001 1 532 905330001 2 533 SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905310001 200702 531 0.00 905360001 200702 536 0.00 SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 where b.ms_type=0; Done SQL> select * from acct; MSID BILL_MONTH AREACODE FEE ---------- ---------- -------- ---------- 905310001 200702 888 0.00 905360001 200702 888 0.00 SQL> 四、10g中增強(qiáng)二：刪除操作 An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted. merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode delete where (b.ms_type!=0); SQL> select * from subs; MSID MS_TYPE AREACODE ---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533 SQL> select * from acct; MSID MS_TYPE AREACODE ---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533 SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 delete where (b.ms_type!=0); Done SQL> select * from acct; MSID MS_TYPE AREACODE ---------- ------- -------- 905310001 0 531 SQL> 更為詳盡的語法，請(qǐng)參考Oracle SQL Reference手冊(cè)！ |
|
|
