ORACLE进阶(十一)MERGE INTO学习
【摘要】 前言
在工作中,查看到类似于如下的SQL语句:
MERGE INTO user.CUSBREACHTAB A USING (SELECT C.CUSTOMERID, -- 客户ID C.CUSTOMERCODE, -- 客户号 C.USERORGCDE, -- 区域编号 C.CUSTOMERNAME, -- 客户名称
12345
&nb...
前言
在工作中,查看到类似于如下的SQL语句:
MERGE INTO user.CUSBREACHTAB A USING (SELECT C.CUSTOMERID, -- 客户ID C.CUSTOMERCODE, -- 客户号 C.USERORGCDE, -- 区域编号 C.CUSTOMERNAME, -- 客户名称
- 1
- 2
- 3
- 4
- 5
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);
- 1
- 2
- 3
- 4
Oracle中,要实现相同的功能,要用到Merge into来实现(Oracle 9i引入的功能),其语法如下:
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (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);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
严格意义上讲,“在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数”。所以,要实现上面的功能,可以这样写:
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN INSERT (a,b) VALUES(T2.a,T2.b);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
演示
//表1
create table subs(msid number(9), ms_type char(1), areacode number(3) );
//表2
create 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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
基本功能
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);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
只有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);
- 1
- 2
- 3
- 4
- 5
只有matched clause, 也就是只更新不插入
merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode
- 1
- 2
- 3
- 4
10g增强介绍
条件操作
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
只有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;
- 1
- 2
- 3
- 4
- 5
- 6
只有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;
- 1
- 2
- 3
- 4
- 5
删除操作
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);
- 1
- 2
- 3
- 4
- 5
注意
- MERGE语句的UPDATE不能修改用于连接的列,否则会报错(on 后面的条件列就是联接);
- using 后面可以是(SELECT msid ,areacode FROM subs GROUP by msid );
文章来源: shq5785.blog.csdn.net,作者:No Silver Bullet,版权归原作者所有,如需转载,请联系作者。
原文链接:shq5785.blog.csdn.net/article/details/79436734
【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)