merge...into 语法的使用

一、merge的基本功能
1) matched 和not matched clauses 同时使用  
merge into acct a using subs b on (a.msid=b.msid) 
when MATCHED then       
update set a.areacode=b.areacode 
when NOT MATCHED then       
insert(msid,bill_month,areacode)        
values(b.msid,'200702',b.areacode);
2) 只有not matched clause,也就是只插入不更新 
merge into acct a  using subs b on (a.msid=b.msid)     
when NOT MATCHED then       
insert(msid,bill_month,areacode)        
values(b.msid,'200702',b.areacode);
3) 只有matched clause, 也就是只更新不插入  
merge into acct a     
using subs b on (a.msid=b.msid)  
when MATCHED then       
update set a.areacode=b.areacode

二、10g中增强一：条件操作

1) matched 和not matched clauses
同时使用   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;
2) 只有not matched clause,也就是只插入不更新  
merge into acct a     
using subs b on (a.msid=b.msid)     
when NOT MATCHED then       
insert(msid,bill_month,areacode)        
values(b.msid,'200702',b.areacode)       
where b.ms_type=0;
3) 只有matched clause, 也就是只更新不插入  
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;  

三、10g中增强二：删除操作
An optional DELETE WHERE clause can be used to clean up after a merge operation.
Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.
For example：

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

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;