Oracle 10g中增強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');