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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/241379/viewspace-709766/，如需轉載，請註明出處，否則將追究法律責任。