MERGE函數(shù)用法
?
??? 在PLSQL中操作數(shù)據(jù)轉(zhuǎn)化過(guò)程中經(jīng)常會(huì)用到MERGE語(yǔ)句，
MERGE函數(shù)是Oracle9i新增的函數(shù)，基本上就是等于update和insert兩個(gè)函數(shù)的相加。使用MERGE函數(shù)，通過(guò)判斷兩個(gè)表之間某字段的關(guān)聯(lián)情況，如果關(guān)聯(lián)匹配上的話就進(jìn)行update操作，如果沒(méi)有匹配就執(zhí)行insert。這個(gè)函數(shù)一來(lái)是減少了程序的長(zhǎng)度，而且只對(duì)表進(jìn)行一次全表掃描，效率也會(huì)有所提高。
1、
簡(jiǎn)單舉例：
?
??? create
table
t1_a
as
???
?
select
rownum
id
,table_name
name
??? ???
from
user_tables;
?
??? create
table
t1_b
as
???
?
select
rownum
id
,table_name
name
??? ???
from
user_tables
??? ??
where
table_name
like
'T%'
;
--
比
t1_a
記錄少
?
??? merge
into
t1_b
??? using
t1_a
??? on
(t1_b.name = t1_a.name)
??? when
matched
then
???
?
update
set
t1_b.id = t1_b.id +
100000
??? when
not
matched
then
???
?
insert
values
(t1_a.id, t1_a.name);
??? --t1_b
表中沒(méi)有的記錄插入，有的記錄把
id+100000
?
??? 注：被修改的必然是在前面的表，后面的表是附加進(jìn)來(lái)進(jìn)行判斷的。
?
?
2、只寫一半：
?
??? 假設(shè)在匹配時(shí)不想進(jìn)行操作，則：
?
??? merge
into
t1_b
??? using
t1_a
??? on
(t1_b.name = t1_a.name)
??? --when matched then
??? --? update set t1_b.id = t1_b.id
??? when
not
matched
then
???
?
insert
values
(t1_a.id, t1_a.name);
?
??? 如果使用9i版本，此時(shí)報(bào)錯(cuò)：ORA-00905: missing keyword
??? 如果使用10g，則順利執(zhí)行。
?
?
3、多值報(bào)錯(cuò)：
?
??? truncate
table
t1_a;
??? truncate
table
t1_b;
??? insert
into
t1_a
values
(
1
,
'a'
);
??? insert
into
t1_b
values
(
1
,
'c'
);
??? insert
into
t1_b
values
(
1
,
'b'
);
??? commit
;
??? select
*
from
t1_a;
??? select
*
from
t1_b;
?
??? merge
into
t1_a
??? using
t1_b
??? on
(t1_b.id = t1_a.id)
??? when
matched
then
???
?
update
set
t1_a.name = t1_b.name
??? when
not
matched
then
???
?
insert
values
(t1_b.id, t1_b.name);
???
?
??? --ORA-30926: unable to get a stable set of rows in the source tables
?
?
4、不能修改作為關(guān)聯(lián)的列
?
??? truncate
table
t1_a;
??? truncate
table
t1_b;
??? insert
into
t1_a
values
(
1
,
'a'
);
??? insert
into
t1_b
values
(
1
,
'b'
);
??? commit
;
?
??? merge
into
t1_a
??? using
t1_b
??? on
(t1_b.id = t1_a.id)
??? when
matched
then
???
?
update
set
t1_a.id = t1_b.id
??? when
not
matched
then
???
?
insert
values
(t1_b.id, t1_b.name);
??? --ORA-00904: "T1_A"."ID": invalid identifier
?
?
?