欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle 10g中增强的Merge

程序员文章站 2022-04-01 08:52:35
...

创建测试表CREATE TABLE test1 ASSELECT *FROM all_objectsWHERE 1=2;MATCHED和NOT MATCHED子句均为可选项 ①两个子句都出现.

创建测试表
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');

Oracle 10g中增强的Merge