`

:Oracle 中对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
 
存储过程的实现新增去重复
tzxd@webora9> drop table t ;

Table dropped.

tzxd@webora9> create table t (t1 number(5),t2 varchar2(20));

Table created.

tzxd@webora9> create index idx_t_t1 on t(t1);

Index created.

tzxd@webora9> declare
  2     v_dummy number(1);
  3  begin
  4     select count(*) into v_dummy from t where t1 = 1 for update;
  5     if v_dummy = 1 then
  6             dbms_output.put_line('value 1 of t1 is exists ');
  7     else
  8             insert into t values(1,'test');
  9     end if;
10  end;
11  /

PL/SQL procedure successfully completed.

tzxd@webora9> ed
Wrote file aaa.sql

declare
        v_dummy number(1);
begin
        select count(*) into v_dummy from t where t1 = 1 for update;
        if v_dummy = 1 then
                dbms_output.put_line('value 1 of t1 is exists ');
        else
                insert into t values(1,'test');
        end if;
end;
/
  1  declare
  2     v_dummy number(1);
  3  begin
  4     select count(*) into v_dummy from t where t1 = 1 for update;
  5     if v_dummy = 1 then
  6             dbms_output.put_line('value 1 of t1 is exists ');
  7     else
  8             insert into t values(1,'test');
  9     end if;
10* end;
tzxd@webora9> /

PL/SQL procedure successfully completed.

tzxd@webora9> select * from t;

        T1 T2
---------- --------------------
         1 test 

 
分享到:
评论

相关推荐

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

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

    Mybatis批量foreach merge into的用法

    首先,需要使用Oracle 9i及其以后版本支持的merge into语句,该语句可以实现insertOrUpdate的功能。然后,使用Mybatis的动态SQL语法foreach循环插入,待插入的实体bean的List通过查询数据库dual形成表。foreach的 ...

    SQL中Merge用法详解

    MERGE是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表(原数据表,source table)或子查询的连接条件对另外一张(目标表,target table)表进行查询,连接条件匹配上的进行UPDATE,...

    oracle死锁故障分析和诊断解决

    4. **优化SQL语句**:合理使用SQL提示(如`/*+ NO_MERGE */`),减少不必要的全表扫描和索引合并,提高查询效率,减少死锁的可能性。 #### 五、案例分析 假设在一个事务中,会话A锁定表T1进行更新,然后尝试锁定表...

    oracle数据匹配merge into的实例详解

    Oracle的`MERGE INTO`语句是数据库管理中用于数据集成和同步的一种高效工具,尤其在处理大量数据的场景下,它可以替代多个独立的INSERT、UPDATE和DELETE操作。在这个实例中,我们看到如何利用`MERGE INTO`来解决特定...

    基于Oracle数据库的几种常见SQL优化策略.pdf

    MERGE命令是Oracle9i及以后版本新增的命令,其字面上的意思就是合并、兼并,用来合并UPDATE和INSERT语法。通过 MERGE 命令,根据一张表或子查询的连接条件对数据进行合并、更新和插入操作,可以提高数据库的处理性能...

    对Oracle多表关联更新的应用研究.pdf

    在性能方面,UPDATE语句可能更适合简单的更新操作,而MERGE语句在处理复杂的条件判断和多表操作时可能更为高效。因此,应根据具体业务逻辑和数据量来选择合适的更新方法。通过对比UPDATE和MERGE的执行成本,可以优化...

    sql练习(oracle)

    SQL是Structured Query Language的缩写,它是用于管理和...这些基本的SQL语句和操作是学习Oracle数据库管理的基础,对于初学者来说非常有帮助,通过不断的练习,可以逐步掌握在Oracle环境中使用SQL语句的技巧和方法。

    02_Oracle_Royallin.pdf

    ### Oracle复习资料知识点详解 #### 一、Oracle 相关(基础+环境) **1.1 Windows XP 下 Oracle 10g 的安装** - **图形界面安装:** - 准备工作:确保Windows XP系统满足Oracle 10g的最低配置要求。 - 启动安装...

    SQL袖珍参考手册(第3版)

    - **数据合并**: 使用MERGE语句合并数据。 - **空值处理**: 如NULL的使用及其特殊处理方式。 - **数值转换**: 在不同的数据库系统中如何进行数值类型的转换。 - **数值/数学函数**: 包括基本的数学运算函数。 - **...

    oracle学习资料

    ### Oracle学习资料知识点详解 #### 一、Oracle相关(基础+环境) ...以上内容是对给定文件中的Oracle学习资料进行了详细的解读和扩展,希望能帮助初学者更好地理解和掌握Oracle数据库的相关知识。

    第8章 数据操作与事务控制.pdf

    MERGE语句是Oracle 9i新增的功能,用于在一个目标表中根据匹配条件插入或更新数据。其语法较为复杂,但功能强大。 ##### 5. 事务控制命令 - **COMMIT**:提交当前事务,使所有数据变更生效。 - **ROLLBACK**:撤销...

    Oracle事例

    20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段...

    Java jdk1.7.0-x64

    新增了Fork/Join框架,用于并行执行任务,以及新的并发集合,如ConcurrentMap的split()和merge()方法。 8. **动态语言支持**: 提供了JSR 292,允许Java虚拟机支持其他动态语言,比如Groovy和JRuby。 9. **JVM...

    SQL性能优化

     在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行...

    用ADO.NET 2.0进行数据库应用程序开发(上)

    ADO.NET 2.0引入了一些增强功能,比如改进的性能和效率,增强了对XML的支持,以及更好的异步操作能力。例如,新增的SqlDependency类允许应用程序监听数据库中的数据更改,从而实现数据驱动的更新通知。此外,DataSet...

    jdk11版本安装包下载

    `java.util.Map`接口新增了`merge()`方法,用于合并键值对。 10. **安全性更新**:JDK 11持续关注安全问题,提供了一系列安全修复和增强,确保应用在处理敏感数据时的安全性。 安装JDK 11时,你需要根据你的操作...

Global site tag (gtag.js) - Google Analytics