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）中删除记录时，该记录将不再存在于源数据中，该记录将保留在目标表中。

SQL Oracle PLSQL ORACLE12C

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;
/

演示

Oracle Merge delete 子句，其中数据在目标中，但不在源中

Oracle Merge delete clause where data in target but NOT in source

评论

评论