ORACLE 进阶（十一）MERGE INTO 学习总结

2022 年 7 月 10 日
本文字数：1900 字
阅读完需：约 6 分钟

一、前言

在工作中，查看到类似于如下的 SQL 语句：

 MERGE INTO user.CUSBREACHTAB A   USING (SELECT C.CUSTOMERID, -- 客户ID                 C.CUSTOMERCODE, -- 客户号                 C.USERORGCDE, -- 区域编号                 C.CUSTOMERNAME, -- 客户名称

复制代码

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。

例如，有一个表 T，有两个字段 a、b，我们想在表 T 中做Insert/Update，如果条件满足，则更新 T 中 b 的值，否则在 T 中插入一条记录。在 Microsoft 的 SQL 语法中，很简单的一句判断就可以了，SQL Server中的语法如下：

if exists(select 1 from T where T.a='1001' )    update T set T.b=2 Where T.a='1001' else     insert into T(a,b) values('1001',2);

复制代码

Oracle 中，要实现相同的功能，要用到Merge into来实现（Oracle 9i 引入的功能），其语法如下：

MERGE INTO table_name alias1USING (table|view|sub_query) alias2ON (join condition) WHEN MATCHED THEN     UPDATE table_name     SET col1 = col_val1,            col2 = col_val2 WHEN NOT MATCHED THEN     INSERT (column_list) VALUES (column_values);

复制代码

严格意义上讲，在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中 alias2 的记录数”。所以，要实现上面的功能，可以这样写：

MERGE INTO T T1USING (SELECT '1001' AS a,2 AS b FROM dual) T2ON ( T1.a=T2.a)WHEN MATCHED THEN    UPDATE SET T1.b = T2.bWHEN NOT MATCHED THEN     INSERT (a,b) VALUES(T2.a,T2.b);

复制代码

二、演示

//表1create table subs(msid number(9),                  ms_type char(1),                 areacode number(3)                 );//表2create table acct(msid number(9),                  bill_month number(6),                  areacode   number(3),                  fee        number(8,2) default 0.00);                  //测试数据                  insert into subs values(905310001,0,531);insert into subs values(905320001,1,532);insert into subs values(905330001,2,533);commit;

复制代码

2.1 基本功能

2.1.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.1.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);

复制代码

2.1.3 只有 matched clause, 也就是只更新不插入

   merge into acct a      using subs b on (a.msid=b.msid)   when MATCHED then        update set a.areacode=b.areacode

复制代码

三、10g 增强介绍

3.1 条件操作

3.1.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;

复制代码

3.1.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.1.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;

复制代码

3.1.4 删除操作

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);

复制代码

四、注意

MERGE语句的UPDATE不能修改用于连接的列，否则会报错(on 后面的条件列就是联接)；
using 后面可以是(SELECT msid ,areacode FROM subs GROUP by msid )；

发布于: 刚刚阅读数: 4

原文链接:【http://xie.infoq.cn/article/1dd44ed517b82fd608b783a4a】。文章转载请联系作者。

No Silver Bullet

关注

No Silver Bullet 2021.07.09 加入

岂曰无衣与子同袍

发布

暂无评论

创作场景

ORACLE 进阶（十一）MERGE INTO 学习总结

一、前言

二、演示

2.1 基本功能

2.1.1 matched 和 not matched clauses 同时使用

2.1.2 只有 not matched clause,也就是只插入不更新

2.1.3 只有 matched clause, 也就是只更新不插入

三、10g 增强介绍

3.1 条件操作

3.1.1 matched 和 not matched clauses 同时使用

3.1.2 只有 not matched clause,也就是只插入不更新

3.1.3 只有 matched clause, 也就是只更新不插入

3.1.4 删除操作

四、注意

No Silver Bullet

评论