如何让Oracle MERGE语句实现PostgreSQL插入更新时的删除效果？

我同时使用PostgreSQL和Oracle执行同逻辑的数据库操作：需要将目标表内容同步为指定数据集——匹配的条目更新，不存在的条目插入，原表中不在指定数据集内的条目删除。

在PostgreSQL中，通过以下语句实现需求：

INSERT INTO IAG_PERMISSION(id,bid,aid,s_type,subject,r_type,object,permissions,tid,person)
VALUES(?,?,?,?,?,?,?,?,?,?)
ON CONFLICT ON CONSTRAINT IAG_PERM_UK1
DO  
UPDATE SET SUBJECT=?,PERMISSIONS=?

但Oracle默认的MERGE语句无法实现删除未更新条目的效果，我最初写的Oracle语句如下：

MERGE INTO IAG_PERMISSION iap
USING dual
ON (iap.AID    = ?
AND iap.R_TYPE = ? 
AND iap.OBJECT = ?  
AND iap.R_TYPE = ? 
AND iap.PERSON = ? )
WHEN MATCHED THEN UPDATE SET SUBJECT = ? , PERMISSIONS = ? 
WHEN NOT MATCHED THEN INSERT (iap.id,iap.bid,iap.aid,iap.s_type,iap.subject,iap.r_type,iap.object,iap.permissions,iap.tid,iap.person)
VALUES (?,?,?,?,?,?,?,?,?,?);

我尝试添加DELETE子句但未生效，尝试的语句如下：

MERGE INTO IAG_PERMISSION IAP 
USING dual d
ON (iap.id='AccessDataStaging' AND iap.R_TYPE ='App1' AND iap.OBJECT ='Obj' AND iap.S_type ='User'
AND iap.PERSON='Application New')
WHEN NOT MATCHED THEN    
INSERT(iap.id,iap.bid,iap.aid,iap.s_type,iap.subject,iap.r_type,iap.object,iap.permissions,iap.tid,iap.person)
VALUES ('of2167aa-769e-4621-b7ff-61b2f44741ea',64,  'AccessDataStaging','User','Sakshi','App1','Obj','N',64,'Application New')
WHEN MATCHED THEN UPDATE SET iap.Subject = 'Sakshi', iap.PERMISSIONS ='K'
DELETE WHERE iap.PERSON='Application New' AND iap.Permissions != 'K';

问题原因 #

1. USING dual的局限性：dual仅返回一行数据，无法关联目标表中所有需要保留的行，导致DELETE子句只能作用于匹配的单一行，无法覆盖其他需要删除的条目。
2. MERGE DELETE子句的限制：Oracle MERGE的DELETE WHERE仅能删除满足ON匹配条件的行（即目标表中存在且与USING数据源匹配的行），无法删除未匹配的行（目标表中不在USING数据源内的行）。

解决方案（推荐分两步操作） #

要实现全量同步效果，需分开执行插入更新和删除操作：

步骤1：执行MERGE完成插入与更新 #

将本次需要保留的所有数据作为USING数据源，通过唯一约束关联目标表，完成匹配行更新、不存在行插入：

MERGE INTO IAG_PERMISSION iap
USING (
    -- 放入本次所有需要保留的记录，多条记录用UNION ALL拼接
    SELECT 
        'of2167aa-769e-4621-b7ff-61b2f44741ea' id,
        64 bid,
        'AccessDataStaging' aid,
        'User' s_type,
        'Sakshi' subject,
        'App1' r_type,
        'Obj' object,
        'K' permissions,
        64 tid,
        'Application New' person
    FROM dual
    -- 示例：添加第二条记录
    -- UNION ALL
    -- SELECT 'another-id', 64, 'AnotherAid', 'Role', 'John', 'App2', 'AnotherObj', 'Y', 64, 'Admin' FROM dual
) src
ON (
    -- 关联PostgreSQL中唯一约束IAG_PERM_UK1的列，确保能唯一匹配一行
    iap.aid = src.aid
    AND iap.r_type = src.r_type
    AND iap.object = src.object
    AND iap.person = src.person
)
WHEN MATCHED THEN
    UPDATE SET 
        iap.subject = src.subject,
        iap.permissions = src.permissions,
        iap.bid = src.bid,
        iap.s_type = src.s_type,
        iap.tid = src.tid,
        iap.id = src.id -- 若ID无需更新可删除此句
WHEN NOT MATCHED THEN
    INSERT (id, bid, aid, s_type, subject, r_type, object, permissions, tid, person)
    VALUES (src.id, src.bid, src.aid, src.s_type, src.subject, src.r_type, src.object, src.permissions, src.tid, src.person);

步骤2：删除不在数据集内的行 #

执行DELETE语句，清除目标表中未出现在本次数据集里的条目：

DELETE FROM IAG_PERMISSION iap
WHERE NOT EXISTS (
    SELECT 1 FROM (
        -- 与MERGE中USING的数据集完全一致，仅保留关联唯一约束的列即可
        SELECT 
            'AccessDataStaging' aid,
            'App1' r_type,
            'Obj' object,
            'Application New' person
        FROM dual
        -- 对应MERGE中的其他记录
        -- UNION ALL
        -- SELECT 'AnotherAid', 'App2', 'AnotherObj', 'Admin' FROM dual
    ) src
    WHERE iap.aid = src.aid
    AND iap.r_type = src.r_type
    AND iap.object = src.object
    AND iap.person = src.person
);

内容的提问来源于stack exchange，提问作者Sakshi