ITPub博客

首页 > Linux操作系统 > Linux操作系统 > merge into

merge into

原创 Linux操作系统 作者:hjianping 时间:2011-04-26 19:52:13 0 删除 编辑

在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博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693805/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 多表插入
请登录后发表评论 登录
全部评论

注册时间:2011-04-24

  • 博文量
    80
  • 访问量
    72860