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

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条件,好处是避免了多个insert和update 操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insert或update操作。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。例子如下：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/293106/viewspace-580568/