在Oracle 10g之前，merge语句支持匹配更新和不匹配插入2种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和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;
一、创建测试用的表
SQL> create table subs
(msid number(9),
ms_type char(1),
areacode number(3)
);
表已创建。
SQL> create table acct
(msid number(9),
bill_month number(6),
areacode number(3),
fee number(8,2)
default 0.00
);
SQL> insert into subs values(905310001,0,531);
SQL> insert into subs values(905320001,1,532);
SQL> insert into subs values(905330001,2,533);
SQL> commit;
二、下面先演示一下merge的基本功能
1、matched 和not matched clauses 同时使用
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
例：
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> 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);
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);
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905340001 3 534
905310001 0 531
905320001 1 532
905330001 2 533
SQL> 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);
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;
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> merge into acct a
using subs b on (a.msid=b.msid)
when matched then
update set a.areacode=b.areacode;
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
三、10g中增强一：条件操作
1、matched 和not matched clauses 同时使用
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;
例：
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> 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;
SQL> select * from acct;
MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905310001 200702 531 0.00
SQL> insert into subs values(905360001,0,536);
SQL> select * from subs;
MSID MS_TYPE AREACODE
---------- ------- --------
905360001 0 536
905310001 0 531
905320001 1 532
905330001 2 533
SQL> 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;
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;
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> 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;
SQL> select * from acct;
MSID BILL_MONTH AREACODE FEE
---------- ---------- -------- ----------
905310001 200702 888 0.00
905360001 200702 888 0.00
四、10g中增强二：删除操作
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> 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 acct;
MSID MS_TYPE AREACODE
---------- ------- --------
905310001 0 531
更为详尽的语法，请参考Oracle SQL Reference手册
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17012874/viewspace-693805/ ，如需转载，请注明出处，否则将追究法律责任。
