ITPub博客

首页 > Linux操作系统 > Linux操作系统 > PL/SQL高级应用(4)-merge into-ZT

PL/SQL高级应用(4)-merge into-ZT

原创 Linux操作系统 作者:spritesong 时间:2009-03-29 22:42:16 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false MicrosoftInternetExplorer4 4.merge into合并资料

语法:(其中as可以省略)

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

WHEN NOT MATCHED THEN

INSERT (column_list)—多个列以逗号分割                      //可以不指定列

VALUES (column_values);

 

作用:将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),依赖于on条件,好处是避免了多个insertupdate 操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insertupdate操作。这个语法仅需要一次全表扫描就完成 了全部工作,执行效率要高于INSERTUPDATE。例子如下:

 

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后面不写表示插入全部列

 

 

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);

 

 

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子句删除目标表中满足条件的行。

  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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/293106/viewspace-580568/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-28

  • 博文量
    62
  • 访问量
    23725