文档章节

Oracle 10g中对Merge语句的增强

hefeiuu
 hefeiuu
发布于 2017/05/04 20:30
字数 1212
阅读 1
收藏 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;

下面我在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://ufopw.iteye.com/blog/962679

共有 人打赏支持
hefeiuu
粉丝 3
博文 64
码字总数 0
作品 0
合肥
oracle merge从9i到10g的增强

MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+U...

长平狐
2012/09/19
107
0
Oracle 中MERGE语句的用法

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

zh119893
2013/06/20
201
0
oracle-merge用法详解

转自 http://blog.chinaunix.net/u/12457/showart_346642.html oracle-merge用法详解 Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个...

晨曦之光
2012/04/25
118
0
在PL/SQL中使用MERGE语句

MERGE Statement Enhancements in Oracle Database 10g Oracle 10g includes a number of amendments to the statement making it more flexible. Test Table Optional Clauses Conditional ......

moonstarseu
2013/11/12
0
0
Merge into的使用详解

Merge是一个非常有用的功能,类似于Mysql里的insert into on duplicate key. Oracle在9i引入了merge命令, 通过这个merge你能够在一个SQL语句中对一个表同时执行inserts和updates操作. 当然是...

mrliuze
2015/07/20
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Ubuntu18.04 显卡GF-940MX安装NVIDIA-390.77

解决办法: 下面就给大家一个正确的姿势在Ubuntu上安装Nvidia驱动: (a)首先去N卡官网下载自己显卡对应的驱动:www.geforce.cn/drivers (b)下载后好放在英文路径的目录下,怎么简单怎么来...

AI_SKI
今天
0
0
深夜胡思乱想

魔兽世界 最近魔兽世界出了新版本, 周末两天升到了满级,比之前的版本体验好很多,做任务不用抢怪了,不用组队打怪也是共享拾取的。技能简化了很多,哪个亮按哪个。 运维 服务器 产品 之间的...

Firxiao
今天
0
0
MySQL 8 在 Windows 下安装及使用

MySQL 8 带来了全新的体验,比如支持 NoSQL、JSON 等,拥有比 MySQL 5.7 两倍以上的性能提升。本文讲解如何在 Windows 下安装 MySQL 8,以及基本的 MySQL 用法。 下载 下载地址 https://dev....

waylau
今天
0
0
微信第三方平台 access_token is invalid or not latest

微信第三方开发平台code换session_key说的特别容易,但是我一使用就带来无穷无尽的烦恼,搞了一整天也无济于事. 现在记录一下解决问题的过程,方便后来人参考. 我遇到的这个问题搜索了整个网络也...

自由的开源
今天
2
0
openJDK之sun.misc.Unsafe类CAS底层实现

注:这篇文章参考了https://www.cnblogs.com/snowater/p/8303698.html 1.sun.misc.Unsafe中CAS方法 在sun.misc.Unsafe中CAS方法如下: compareAndSwapObject(java.lang.Object arg0, long a......

汉斯-冯-拉特
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部