`

Merge用法:Oracle 10g中对Merge语句的增强

 
阅读更多

转自：http://www.eygle.com/digest/2009/02/mergeoracle_10gmerge.html

Merge用法:Oracle 10g中对Merge语句的增强

网址: 

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

更为详尽的语法，请参考Oracle SQL Reference手册！

原文链接：http://tomszrp.itpub.net/post/11835/263865
分享到：
| oracle使用 merge 更新或插入数据（总结）
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    SQL中Merge用法详解

    通过MERGE语句，根据一张表（原数据表，source table）或子查询的连接条件对另外一张（目标表，target table）表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了...

    精通Oracle 10g SQL和PL/SQL

    　本书是专门为oracle开发人员而提供的编程指南 通过学习本书 读者不仅可以掌握编写sql语句和pl／sql块的基础知识 而且还可以掌握sql高级特征 正则表达式 flashback查询 merge语句 sql：1999连接 和pl／sql高级特征 ...

    OCPOCA认证考试指南全册：Oracle Database 11g（1Z0-051，1Z0-052，1Z0-053）--详细书签版(第2/2部分)

    　Bob Bryla是Oracle 9i和10g的认证专家，他在数据库设计、数据库应用程序开发、培训和Oracle数据库管理等方面拥有20多年的工作经验,他也足Dodgeville的Land'End公司的首席Internet数据库设计师和Oracle DBA. ...

    Oracle_merge

    在Oracle 9i R2版中引入的MERGE语句通常被称作“更新插入”(upsert)，因为使用MERGE可以在同一个步骤中更新(update)并插入(insert)数据行。。。。。。

    使用BULK COLLECT, MERGE 语句提高sql执行效率

    详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询

    精通Oracle 10g SQL和PL SQL.pdf

    通过学习《精通Oracle 10g SQL和PL/SQL》，读者不仅可以掌握SQL和PL/SQL的基础知识，而且还可以掌握SQL高级特征（正则表达式、Flashback查询、MERGE语句、SQL:1999连接）和PL/SQL高级特征（记录类型、集合类型、对象...

    update语句的优化-oracle .pdf

    实际项目中遇到的问题总结：数据量百万级，千万级。Oracle中update语句的优化，一共四种方案，工作中遇到该类问题可以参考。

    MySQL中实现插入或更新操作（类似Oracle的merge语句）

    主要介绍了在MySQL中实现插入或更新操作（类似Oracle的merge语句)的相关资料,非常不错，具有参考借鉴价值，需要的朋友可以参考下

    OCPOCA认证考试指南全册：Oracle Database 11g（1Z0-051，1Z0-052，1Z0-053）--详细书签版(第1/2部分)

    　Bob Bryla是Oracle 9i和10g的认证专家，他在数据库设计、数据库应用程序开发、培训和Oracle数据库管理等方面拥有20多年的工作经验,他也足Dodgeville的Land'End公司的首席Internet数据库设计师和Oracle DBA. ...

    Oracle10g 知识总结

    有关oracle10g的知识总结 1.SQL语句分类 (1).查询语句——select (2).数据操作语句(DML)：insert into 、delete、update、merge (3).数据定义语句(DDL)：create 、alter、drop、truncate (4).数据控制语句(DCL)：...

    Oracle merge合并更新函数实例详解

    MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。 通过MERGE语句，根据一张表或多表联合查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次...

    oracle10g课堂练习I（2）

    Oracle Database 10 g ：“g”代表网格 1-6 Oracle 数据库体系结构 1-8 数据库结构 1-9 Oracle 内存结构 1-10 进程结构 1-12 Oracle 实例管理 1-13 服务器进程和数据库缓冲区高速缓存 1-14 物理数据库结构 1-...

    merge用法详解

    无论你在使用任何DBMS，你总是难以避免的将会遇到上面提到的这种需求，如果你不使用merge语句，你将会不得不在程序中增加大段的代码，或者是在oracle用很长的代码来实现。好在现在我们有了merge,可以帮我们省下很多...

    Oracle MERGE INTO的用法示例介绍

    1）主要功能 提供有条件地更新和插入数据到数据库表中 如果该行存在，执行一个UPDATE操作，如果是一个新行，执行INSERT操作 — 避免了分开更新 — 提高性能并易于使用 — 在数据仓库应用中十分有用 2）MERGE语句的...

    oracle sqlldr;；merge；分组排序；条件赋值；表连接。简单示例

    实用基础SQL语句；oracle sqlldr;SQL基础语句；merge；分组排序；条件赋值；表连接。简单示例，Oracle数据库文档数据导入

Magicbox
Global site tag (gtag.js) - Google Analytics