尝试从目标表中删除源表中不存在的记录。
对象类型
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 不再在源数据中,该记录仍保留在目标表中。
In a 合并删除 https://oracle-base.com/articles/10g/merge-enhancements-10g#delete_clause 仅删除目标表中同时匹配 ON 子句和 DELETE WHERE 的行。
You may FULL JOIN
桌子上有你的TMP
in the USING
查询并使用标志来删除不匹配的行。
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;
/
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)