Oracle10g中增强的Merge

创建测试表

CREATE TABLE test1 AS

SELECT *

FROM   all_objects

WHERE  1=2;

 

MATCHED和NOT MATCHED子句均为可选项

①两个子句都出现.

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN MATCHED THEN

    UPDATE SET a.status = b.status

  WHEN NOT MATCHED THEN

    INSERT (object_id, status)

    VALUES (b.object_id, b.status);

 

②只有插入操作，即只有NOT MATCHED子句

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN NOT MATCHED THEN

    INSERT (object_id, status)

    VALUES (b.object_id, b.status);

 

③只有更新操作，即只有MATCHED子句

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN MATCHED THEN

    UPDATE SET a.status = b.status;

 

源表可以包含WHERE子句

①更新和插入均包含WHERE子句

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN MATCHED THEN

    UPDATE SET a.status = b.status

    WHERE  b.status != 'VALID'

  WHEN NOT MATCHED THEN

    INSERT (object_id, status)

    VALUES (b.object_id, b.status)

    WHERE  b.status != 'VALID';

 

②插入操作包含WHERE子句

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN NOT MATCHED THEN

    INSERT (object_id, status)

    VALUES (b.object_id, b.status)

    WHERE  b.status != 'VALID';

 

③更新操作包含WHERE子句

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN MATCHED THEN

    UPDATE SET a.status = b.status

    WHERE  b.status != 'VALID';

 

DELETE子句

可以使用该子句删除源表中同时满足ON子句条件及DELETE WHERE子句条件的记录。

MERGE INTO test1 a

  USING all_objects b

    ON (a.object_id = b.object_id)

  WHEN MATCHED THEN

    UPDATE SET a.status = b.status

    WHERE  b.status != 'VALID'

  DELETE WHERE (b.status = 'VALID');

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

转载于:http://blog.itpub.net/24867611/viewspace-739688/