创建测试表

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