MERGE操作可以用一条语句实现UPDATE和INSERT，而且它比传统的关联子查询可能效率更好，避免了FILTER操作，可以走HASH JOIN操作，适合大批量关联更新，下面就来聊聊MERGE语句：MERGE操作的语法如下：
MERGE INTOtable_name AS table_alias
USING(table|view|sub_query) AS alias //单表或视图不需要括号，内联视图要括号
ON(join condition)//必须要有括号
WHEN MATCHED THEN
UPDATE SET
col1 =
col_val1,
col2 =
col2_val--9i不可以有where条件，10g可以
WHEN NOT MATCHED THEN
INSERT(column_list)—多个列以逗号分割//可以不指定列
VALUES(column_values);--9i不可以有where条件，10g可以
作用：将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),依赖于on条件,好处是避免了多个insert和update操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insert或update操作。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。在update关联更新的时候，如果可以用merge搞定，特别是10g可以允许单个merge
update/insert,比一般的关联更新减少表的扫描提高效率。例子如下：
droptablet;
CREATETABLETASSELECTROWNUMID, A.*FROMDBA_OBJECTS A;
droptablet1;
CREATETABLET1AS
SELECTROWNUMID,
OWNER, TABLE_NAME,CAST('TABLE'ASVARCHAR2(100)) OBJECT_TYPE
FROMDBA_TABLES;
select*fromdba_objects;
select*fromdba_tables;
MERGEINTOT1USINGT
ON(T.OWNER = T1.OWNERANDT.OBJECT_NAME = T1.TABLE_NAMEANDT.OBJECT_TYPE = T1.OBJECT_TYPE)
WHENMATCHEDTHENUPDATESETT1.ID= T.ID
WHENNOTMATCHEDTHENINSERTVALUES(T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--insert后面不写表示插入全部列
--这个是对update的要求，对insert没有这个要求，可以与update inline view方法对比，nline view方法是要求有preserved key的，这个不需要，但是对t1列的每行必须唯一找到t的一行才行
MERGEINTOT1USINGT
ON(T.OWNER = T1.OWNERANDT.OBJECT_NAME = T1.TABLE_NAME)
WHENMATCHEDTHENUPDATESETT1.ID= T.ID
WHENNOTMATCHEDTHENINSERTVALUES(T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询
--只能一行被更新行找到一行稳定的数据源行，可以想办法做这个动作
MERGEINTOT1
USING(SELECTOWNER, OBJECT_NAME,MAX(ID)IDFROMTGROUPBYOWNER, OBJECT_NAME) T
ON(T.OWNER = T1.OWNERANDT.OBJECT_NAME = T1.TABLE_NAME)
WHENMATCHEDTHENUPDATESETT1.ID= T.ID
WHENNOTMATCHEDTHENINSERTVALUES(T.ID,
T.OWNER, T.OBJECT_NAME);
关于不能获取稳定更新行的错误探讨，其他update同样有这样的问题。。。。。。。
droptablea;
droptableb;
createtablea(idnumber,namevarchar2(10));
createtableb(idnumber,namevarchar2(10),extvarchar2(10));
insertintoavalues(1,'x');
insertintoavalues(2,'y');
insertintobvalues(1,'y','x');
insertintobvalues(1,'z','x');
insertintobvalues(2,'m','x');
INSERTINTObVALUES(3,'n','x');
commit;
SELECT*FROMa;
--1.报错，因为按条件每个a对用多个b的行，主要无法确定用哪行更新a
mergeintoausingb
on(a.id=b.idandb.ext='x')
whenmatchedthen
updateseta.name=b.name;
mergeintoausingb
on(a.id=b.idandb.ext='x')
whenmatchedthen
updateseta.name=b.name
ORA-30926:无法在源表中获得一组稳定的行
--加rownum可以更新
mergeintoausingb
on(a.id=b.idandb.ext='x'ANDROWNUM=1)
whenmatchedthen
updateseta.name=b.NAME;
--但是加了rownum,则有insert则会报错，因为有满足的会跑到insert里
mergeintoausingb
on(a.id=b.idandb.ext='x'ANDROWNUM=1)
whenmatchedthen
updateseta.name=b.NAME
WHENNOTMATCHEDTHEN
INSERT(ID,NAME)VALUES(b.ID,b.NAME);
--上面的直接insert不报错，因为这里的insert能取到稳定的不满足行
mergeintoausingb
on(a.id=b.idandb.ext='x')
WHENNOTMATCHEDTHEN
INSERT(ID,NAME)VALUES(b.ID,b.NAME);
--OK,对这种情况，则可以使用两次merge,一次单独的update+rownum,一次单独的insert
--合二唯一方案，获取取稳定的更新行，想办法拼凑，分组，排名，rowid等
mergeintoausing(SELECTid,name,extFROM(
SELECTID,NAME,ext,row_number()over(PARTITIONBYIDORDERBYROWID)rnFROMb
WHEREext='x'
)WHERErn=1)b
on(a.id=b.ID)
whenmatchedthen
updateseta.name=b.NAME
WHENNOTMATCHEDTHEN
INSERT(ID,NAME)VALUES(b.ID,b.NAME);
SELECTID,
OWNER, OBJECT_NAME, OBJECT_TYPEFROMT
MINUS
SELECT*FROMT1;
droptablesubs;
createtablesubs(msidnumber(9),
ms_typechar(1),
areacodenumber(3)
);
droptableacct;
createtableacct(msidnumber(9),
bill_monthnumber(6),
areacodenumber(3),
feenumber(8,2)default0.00);
insertintosubsvalues(905310001,0,531);
insertintosubsvalues(905320001,1,532);
insertintosubsvalues(905330001,2,533);
commit;
mergeintoacct a--操作的表
usingsubs bon(a.msid=b.msid)--使用原始数据来源的表，并且制定条件，条件必须有括号
whenmatchedthen
updateseta.areacode=b.areacode--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
whennotmatchedthen--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，则在insert后用括号标明，不指定是全部插入
insert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode);
另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错
select*fromacct;
select*fromsubs;
--10g新特性，单个操作
mergeintoacct a
usingsubs bon(a.msid=b.msid)
whennotmatchedthen--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
insert(a.msid,a.bill_month,a.areacode)values(b.msid,'200702',b.areacode);
updateacctsetareacode=800wheremsid=905320001;
deletefromacctwhereareacode=533orareacode=531;
insertintoacctvalues(905320001,'200702',800,0.00);
--删除重复行
deletefromsubs bwhereb.rowid
selectmax(a.rowid)fromsubs awherea.msid=b.msidanda.ms_type=b.ms_typeanda.areacode=b.areacode);
--10g新特性，merge操作之后，只有匹配的update操作才可以，用delete where子句删除目标表中满足条件的行。要注意，必须是在匹配的前提条件下，先更新原始表后，删除删除满足条件的行，也就是对更新的行调用删除条件删除,还有单个insert/update功能。
mergeintoacct a
usingsubs bon(a.msid=b.msid)
whenMATCHEDthen
updateseta.areacode=b.areacode
deletewhere(b.ms_type!=0)
whenNOTMATCHEDthen
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
whereb.ms_type=0;
--10g新特性，满足条件的插入和更新
mergeintoacct a
usingsubs bon(a.msid=b.msid)
whenMATCHEDthen
updateseta.areacode=b.areacode
whereb.ms_type=0
whenNOTMATCHEDthen
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
whereb.ms_type=0;
select*fromsubswherems_type=0;
(10g可以条件插入和条件更新
10g的update能加delete where)
droptablet1;
droptablet2;
createtablet1(idnumber,namevarchar2(10));
createtablet2(idnumber,namevarchar2(10));
insertintot1values(1,'a');
insertintot1values(2,'b');
insertintot1values(3,'c');
insertintot1values(5,'a');
insertintot2values(1,'x');
insertintot2values(2,'y');
insertintot2values(4,'z');
insertintot2values(5,'a');
commit;
---对t2中的id=1和5的更新完后，name变为a,然后调用删除条件
--去掉update的delete会报错的，delete必须在update后，是在匹配的前提下发生的，是update执行之后，按条件删除目标表t2中--的数据
mergeintot2
usingt1on(t1.id=t2.id)
whenMATCHEDthen
updatesett2.name=t1.name
deletewhere(t2.name='a')
whenNOTMATCHEDthen
insert
values(t1.id,t1.name);
--update delete中的delete条件可以是t1,t2中的任意列，因为按合并的删除
现在请问，如果在merge中使用delete的句子，那么删除的字段是否必须是条件判断中的出现的字段？---------------不需要，因为是按匹配的结果先update之后，这时候oracle是知道源表被更新后的数据和数据库的匹配数据的，所以update delete条件可以是源表更新后的结果和数据源的任意列ps:注意是更新后的
mergeintot2
usingt1on(t1.id=t2.id)
whenMATCHEDthen
updatesett2.name=t1.name
deletewhere(t1.name='a')
whenNOTMATCHEDthen
insert
values(t1.id,t1.name);
====》
当然是在匹配的前提下，先更新后删除，merge可以简单解释为，假设就一个id匹配条件，update name
if
t1.id=t2.id then
update set t2.name = t1.name;--先更新，后删除
--删除目标表数据
delete t2 where where exists (select 1 from t1
where t1.id=t2.id and t2….=....)//如果是用目标表条件删除，是这样的
or
delete t2 where exists (select 1 from t1 where
t1.id=t2.id and t1….=....) //如果是用数据源删除是这样的
else
insert into t1…
end
if;
--merge可以是多表关联
MERGE INTO SERV s
USING (
SELECT s.serv_id,m.arr_month,f.arr_fee
FROM serv s LEFT JOIN month m ON s.serv_id=m.serv_id
LEFT JOIN fee f ON s.serv_id=f.serv_id
) d
ON (s.serv_id=d.serv_id)
WHEN MATCHED THEN UPDATE SET s.arr_month=d.arr_month,s.arr_fee=d.arr_fee;
扫一扫
512
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
