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 --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。例子如下：
 
    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;
更多详细内容请查看： http://www.111cn.net/database/111/b2c02da4e875c41a5653720c139bfdbc.htm