本文转自:http://blog.itpub.net/7204674/viewspace-468420/

分类: Linux
         今天写程序的时候,遇到这样一个问题,假如存在记录则更新,不存在则插入。以前遇到这样的情况一般需要控制语句,游标来实现。偶然知道,MERGE语句,就可以实现Update和Insert的功能,特来实践一下。当然,首先来学习一下语法,看到oracle技术文档上是这样描述的:
语法:
merge::=
De.ion of merge.gif follows

merge_update_clause ::=
De.ion of merge_update_clause.gif follows

merge_insert_clause ::=
De.ion of merge_insert_clause.gif follows
where_clause::=
De.ion of where_clause.gif follows


INTO 字句
用于指定你所update或者Insert目的表。
USING Clause
用于指定你要update或者Insert的记录的来源,它可能是一个表,视图,子查询。
ON Clause
用于目的表和源表(视图,子查询)的关联,如果匹配(或存在),则更新,否则插入。
merge_update_clause
用于写update语句
merge_insert_clause
用于写insert语句

例子:
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> 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;
提交完成。

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

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

说明:本文参考了oracle文档,例子是参考的http://xsb.itpub.net/post/419/30766。