oracle之merge的深入介绍

最新推荐文章于 2019-08-13 17:34:49 发布

specialrun

最新推荐文章于 2019-08-13 17:34:49 发布

阅读量2.4k

点赞数 2

分类专栏： merge oracle 文章标签： oracle merge

本文链接：https://blog.csdn.net/u013755435/article/details/50854772

版权

oracle 同时被 2 个专栏收录

2 篇文章 0 订阅

订阅专栏

merge

1 篇文章 0 订阅

订阅专栏

文章概要:
1.merge的详细介绍
2.merge的语法结构
3.与merge相关的例题
4.相关参考网址
文章内容:
1.merge的详细介绍
MERGE语句是Oracle9i新增的语法，
你能够在一个SQL语句中对一个表同时执行inserts和updates操作.

连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。
Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

前提:
使用MERGE的前提条件，你必须有INSERT和UPDATE该表的权限，若DELETE时，必须要有DELETE的权限
USING相关:
在用USING时，可以是表，视图或查询结果
MERGE_UPDATE相关:
当满足ON的条件,UPDATE才会更新表。当定义WHERE时，只有满足该条件的行才会更新表，当不满足条件时，会跳过改行。
你不能更新ON里含有的字段
INSERT相关
当不满足ON的条件会触发INSERT，若后面跟WHERE，则当满足该条件时，触发插入操作。
ERROR_LOGGING_CLAUSE相关

2.merge的语法结构
MERGE [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;
这里写图片描述

3.与merge相关的例题
我有一个会员表，里面的内容是别处的另外一个应用所管理的。这个应用会定期插入到一张临时表，表示哪些会员需要被增/删/改:

create table plch_members (
id integer primary key
, name varchar2(10) not null
)
/

create table plch_members_stage (
id integer primary key
, name varchar2(10)
, dmltype char(1) not null check(dmltype in (‘I’,’U’,’D’))
)
/

insert into plch_members values (1, ‘Adam’)
/
insert into plch_members values (2, ‘Brian’)
/
insert into plch_members values (3, ‘Chuck’)
/
insert into plch_members values (4, ‘David’)
/
insert into plch_members values (5, ‘Eric’)
/

insert into plch_members_stage values (6, ‘Frank’ , ‘I’)
/
insert into plch_members_stage values (3, ‘Charles’, ‘U’)
/
insert into plch_members_stage values (2, null , ‘D’)
/

commit
/
这个应用插入到临时表中的信息表明会员Frank需要被插入，Chuck需要改成Charles，而Brian需要被删除。

哪些选项包含了一个或者多个DML，可以在PLCH_MEMBERS表上执行（不出错）PLCH_MEMBERS_STAGE里面所指定的信息，使得下列这个测试查询会返回如下结果：
select id, name
from plch_members
order by id
/
ID NAME

     1 Adam
     3 Charles
     4 David
     5 Eric
     6 Frank

(A)
merge into plch_members m
using plch_members_stage s
on (m.id = s.id)
when matched then
when s.dmltype = ‘U’ then update set m.name = s.name
when s.dmltype = ‘D’ then delete m
when not matched then
insert (m.id, m.name)
values (s.id, s.name)
/
(B)
merge into plch_members m
using plch_members_stage s
on (m.id = s.id)
when matched then
update set m.name = s.name
delete where s.dmltype = ‘D’
when not matched then
insert (m.id, m.name)
values (s.id, s.name)
/
(C)
merge into plch_members m
using plch_members_stage s
on (m.id = s.id)
when matched then
update set m.name = s.name
where s.dmltype = ‘U’
delete where s.dmltype = ‘I’
when not matched then
insert (m.id, m.name)
values (s.id, s.name)
/

(D)
merge into plch_members m
using plch_members_stage s
on (m.id = s.id)
when matched then
update set m.name = nvl(s.name, ‘DUMMY’)
delete where s.dmltype = ‘D’
when not matched then
insert (m.id, m.name)
values (s.id, s.name)
/
(E)
merge into plch_members m
using plch_members_stage s
on (m.id = s.id)
when matched then
update set m.name = case s.dmltype
when ‘U’ then s.name
when ‘D’ then ‘DUMMY’
end
delete where s.dmltype = ‘D’
when not matched then
insert (m.id, m.name)
values (s.id, s.name)
/

(F)
insert into plch_members (id, name)
select id, name
from plch_members_stage
where dmltype = ‘I’
/
update (
select m.name oldname, s.name newname
from plch_members_stage s
join plch_members m
on m.id = s.id
where s.dmltype = ‘U’
)
set oldname = newname
/
delete plch_members
where id in (
select id
from plch_members_stage
where dmltype = ‘D’
)
/
A: 这不是正确的MERGE语法，你不能够在WHEN MATCHED之后执行一个WHEN … THEN UPDATE 或者 WHEN … THEN DELETE
这会报错：
ORA-00905: missing keyword.

B: 临时表中说到Brian必须被删除的那一行的NAME列是个NULL, 而DELETE WHERE 仅仅在 update发生之后才会执行，所以对Brian的修改失败了：
ORA-01407: cannot update (“SCOTT”.”PLCH_MEMBERS”.”NAME”) to NULL.

C: 这会插入Frank并且将Chuck改成Charles，并且输出”2 rows merged”——Brian不会被删除，因为DELETE WHERE 仅仅发生在被修改的行，而 UPDATE子句中的WHERE子句使得Brian不会被修改，因而也不会被删除。本选项执行之后我们会看到这个错误输出：
ID NAME

     1 Adam
     2 Brian
     3 Charles
     4 David
     5 Eric
     6 Frank

D: 通过在删除之前把Brian的名字改成DUMMY，这个选项是可行的，输出”3 rows merged”，随后的查询会显示所需的结果。
E: （推荐）
这个方法也可行，因为在删除之前Brian的名字被改成DUMMY，就如D选项。但是如果临时表输入的数据有错，使得dmltype=’I’并且名字为NULL, 则前一选项会插入一个名字为DUMMY的数据，而这个选项会报ORA-01407, 因此从处理错误数据的角度而言，本选项更为正确。

F:
独立执行三个DML语句：INSERT, UPDATE 和 DELETE，这同样可行，但是可能不如MERGE高效。也有可能 DMLTYPE=’D’ 的百分比很高，那么就对 MERGE 有太多的负面影响，因为要删除的行必须执行一个隐性的UPDATE。在这种情况下你必须测试，看看哪种方法最好。

4.相关参考网址
例题参考网址:
http://www.itpub.net/forum.php?mod=viewthread&tid=2054324&page=1#pid23281785

https://search.oracle.com/search/search