在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;

下面我在windows xp 下10.2.0.1版本上做一个测试看看

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>
一、创建测试用的表
SQL> create table subs(msid number(9),
2 ms_type char(1),
3 areacode number(3)
4 );

表已创建。

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

表已创建。

SQL>
SQL> insert into subs values(905310001,0,531);

已创建 1 行。

SQL> insert into subs values(905320001,1,532);

已创建 1 行。

SQL> insert into subs values(905330001,2,533);

已创建 1 行。

SQL> commit;

提交完成。

SQL>


二、下面先演示一下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

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中增强一:条件操作

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;


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中增强二:删除操作
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>