Oracle MERGE DELETE子句，其中数据在目标中，而不在源中

正在尝试从目标表中删除不在源表中的记录。

对象类型

CREATE OR REPLACE type dbo.P_REC AS OBJECT
(
    ATTR1    VARCHAR2(64 BYTE),
    ATTR2    VARCHAR2(128 BYTE),
    ATTR3    VARCHAR2(128 BYTE),
    ATTR4    VARCHAR2(128 BYTE)
);

集合类型

CREATE OR REPLACE type dbo.P_REC_LIST is table of P_REC;

存储过程

PROCEDURE testProc(tmpPList IN P_REC_LIST, resultCursor out sys_refcursor)

IF tmpPList.count > 0 THEN
MERGE INTO [targetTable] TT
        USING (SELECT abc.Attr1 abc.Attr2 FROM TABLE(tmpPList) abc) TMP
        ON (TT.Attr1 = TMP.Attr1)
      WHEN MATCHED THEN
        UPDATE SET TT.Attr2 = TMP.Attr2 
        DELETE WHERE TT.Attr1 NOT IN (SELECT Attr1 FROM TABLE(tmpPList))
      WHEN NOT MATCHED THEN
        INSERT (Attr1)
        VALUES (TMP.Attr1);
END IF;

目前，我可以从截断的表开始，插入部分可以工作。当再次运行时，更新部分工作。 但是，当从tmpPList(IE)中删除记录时，该记录将保留在目标表中。

---

解决方案

在MERGE delete中，仅删除目标表中与ON子句和DELETE WHERE都匹配的那些行。

您可以FULL JOIN在USING查询中使用TMP表，并使用标志删除不匹配的行。

create or replace  PROCEDURE testProc(tmpPList IN P_REC_LIST)
AS
BEGIN
 IF tmpPList.count > 0 THEN
MERGE INTO targetTable tt
        USING ( with abc as
                 (
                  select * from TABLE(tmpPList)
                  )
                  select COALESCE(abc.attr1,t.attr1) as attr1,
                                     abc.attr2,CASE WHEN abc.attr1 IS NULL
                                           THEN 'Y' ELSE 'N' END 
                                          match_flag FROM abc
                          FULL JOIN targetTable t ON t.attr1 = abc.attr1
               ) tmp
        ON (tt.Attr1 = tmp.attr1)
      WHEN MATCHED THEN
         UPDATE SET tt.Attr2 = tmp.Attr2 
        DELETE WHERE match_flag = 'Y' --This flag to identify the unmatched row
      WHEN NOT MATCHED THEN
        INSERT (Attr1)
        VALUES (tmp.Attr1);
  END IF;
END testProc;
/

Demo