Oracle 10g中增强的Merge

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