About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（172）
分类： Oracle
2020-07-13 09:09:06
MERGE操作可以用一条语句实现UPDATE和INSERT，而且它比传统的关联子查询可能效率更好，避免了FILTER操作，可以走HASH JOIN操作，适合大批量关联更新，下面就来聊聊MERGE语句：
MERGE操作的语法如下：
MERGE INTO table_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,比一般的关联更新减少表的扫描提高效率。例子如下：
drop table t;
CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
drop table t1;
CREATE TABLE T1 AS
SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
FROM DBA_TABLES;
select * from dba_objects;
select * from dba_tables;
MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--insert后面不写表示插入全部列
--这个是对update的要求，对insert没有这个要求，可以与update inline view方法对比，nline view方法是要求有preserved key的，这个不需要，但是对t1列的每行必须唯一找到t的一行才行
MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询
--只能一行被更新行找到一行稳定的数据源行，可以想办法做这个动作
MERGE INTO T1
USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID FROM T GROUP BY OWNER, OBJECT_NAME) T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);
关于不能获取稳定更新行的错误探讨，其他update同样有这样的问题。。。。。。。
drop table a;
drop table b;
create table a(id number,name varchar2(10));
create table b(id number,name varchar2(10),ext varchar2(10));
insert into a values(1,'x');
insert into a values(2,'y');
insert into b values(1,'y','x');
insert into b values(1,'z','x');
insert into b values(2,'m','x');
INSERT INTO b VALUES(3,'n','x');
commit;
SELECT * FROM a;
--1.报错，因为按条件每个a对用多个b的行，主要无法确定用哪行更新a
merge into a using b
on (a.id=b.id and b.ext='x')
when matched then
update set a.name = b.name;
merge into a using b
on (a.id=b.id and b.ext='x')
when matched then
update set a.name = b.name
ORA-30926: 无法在源表中获得一组稳定的行
--加rownum可以更新
merge into a using b
on (a.id=b.id and b.ext='x' AND ROWNUM=1)
when matched then
update set a.name = b.NAME;
--但是加了rownum,则有insert则会报错，因为有满足的会跑到insert里
merge into a using b
on (a.id=b.id and b.ext='x' AND ROWNUM=1)
when matched then
update set a.name = b.NAME
WHEN NOT MATCHED THEN
INSERT (ID,NAME) VALUES(b.ID,b.NAME);
--上面的直接insert不报错，因为这里的insert能取到稳定的不满足行
merge into a using b
on (a.id=b.id and b.ext='x')
WHEN NOT MATCHED THEN
INSERT (ID,NAME) VALUES(b.ID,b.NAME);
--OK,对这种情况，则可以使用两次merge,一次单独的update+rownum,一次单独的insert
--合二唯一方案，获取取稳定的更新行，想办法拼凑，分组，排名，rowid等
merge into a using (SELECT id,name,ext FROM (
SELECT ID,NAME,ext,row_number() over(PARTITION BY ID ORDER BY ROWID) rn FROM b
WHERE ext='x'
) WHERE rn=1) b
on (a.id=b.ID)
when matched then
update set a.name = b.NAME
WHEN NOT MATCHED THEN
INSERT (ID,NAME) VALUES(b.ID,b.NAME);
SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE FROM T
MINUS
SELECT * FROM T1;
drop table subs;
create table subs(msid number(9),
ms_type char(1),
areacode number(3)
);
drop table acct;
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;
merge into acct a --操作的表
using subs b on (a.msid=b.msid)--使用原始数据来源的表，并且制定条件，条件必须有括号
when matched then
update set a.areacode=b.areacode--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
when not matched then--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，则在insert后用括号标明，不指定是全部插入
insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错
select * from acct;
select * from subs;
--10g新特性，单个操作
merge into acct a
using subs b on(a.msid=b.msid)
when not matched then--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);
update acct set areacode=800 where msid=905320001;
delete from acct where areacode=533 or areacode=531;
insert into acct values(905320001,'200702',800,0.00);
--删除重复行
delete from subs b where b.rowid<(
select max(a.rowid) from subs a where a.msid=b.msid and a.ms_type=b.ms_type and a.areacode=b.areacode);
--10g新特性，merge操作之后，只有匹配的update操作才可以，用delete where子句删除目标表中满足条件的行。要注意，必须是在匹配的前提条件下，先更新原始表后，删除删除满足条件的行，也就是对更新的行调用删除条件删除,还有单个insert/update功能。
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)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
--10g新特性，满足条件的插入和更新
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;
select * from subs where ms_type=0;
(10g可以条件插入和条件更新
10g的update能加delete where)
drop table t1;
drop table t2;
create table t1(id number,name varchar2(10));
create table t2(id number,name varchar2(10));
insert into t1 values(1,'a');
insert into t1 values(2,'b');
insert into t1 values(3,'c');
insert into t1 values(5,'a');
insert into t2 values(1,'x');
insert into t2 values(2,'y');
insert into t2 values(4,'z');
insert into t2 values(5,'a');
commit;
---对t2中的id=1和5的更新完后，name变为a,然后调用删除条件
--去掉update的delete会报错的，delete必须在update后，是在匹配的前提下发生的，是update执行之后，按条件删除目标表t2中--的数据
merge into t2
using t1 on (t1.id=t2.id)
when MATCHED then
update set t2.name=t1.name
delete where (t2.name='a')
when NOT MATCHED then
insert
values(t1.id,t1.name);
--update delete中的delete条件可以是t1,t2中的任意列，因为按合并的删除
现在请问，如果在merge中使用delete的句子，那么删除的字段是否必须是 条件判断中的出现的字段？
---------------
不需要，因为是按匹配的结果先update之后，这时候oracle是知道源表被更新后的数据和数据库的匹配数据的，所以update delete条件可以是源表更新后的结果和数据源的任意列
ps:注意是更新后的
merge into t2
using t1 on (t1.id=t2.id)
when MATCHED then
update set t2.name=t1.name
delete where (t1.name='a')
when NOT MATCHED then
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;
