Chinaunix首页 | 论坛 | 博客
  • 博客访问: 682460
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3029
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(100)

文章存档

2020年(65)

2014年(3)

2013年(32)

分类: 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条件,好处是避免了多个insertupdate操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insertupdate操作。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERTUPDATEupdate关联更新的时候,如果可以用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=15的更新完后,name变为a,然后调用删除条件

--去掉updatedelete会报错的,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;

 

 

 


阅读(368) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~