oracle 10 merge,ORACLE 10 g的 merge into 用法

创建表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

原文：http://blog.csdn.net/xiaokui_wingfly/article/details/45168965