返回PL/SQL中光标中已删除的行

Question

我有一个要求，希望在sys_refcursor中返回已删除的记录。我可以在delete语句之前检索游标中的数据，但有没有办法在delete语句之后检索它们？我的意思是，这个过程就像第一次删除，然后打开sys_refcursor来获取删除的记录。

Answer 1

可能有几个选项，但其中一个选项是使用RETURNING和BULK COLLECT。这里有一个简单的例子。

CREATE TABLE t (
a NUMBER,
b VARCHAR2(10),
c DATE
);
INSERT INTO t VALUES (1, 'a', SYSDATE);
INSERT INTO t VALUES (2, 'b', SYSDATE);
INSERT INTO t VALUES (3, 'c', SYSDATE);
INSERT INTO t VALUES (4, 'd', SYSDATE);
INSERT INTO t VALUES (5, 'e', SYSDATE);
CREATE OR REPLACE TYPE tt AS OBJECT (
a NUMBER,
b VARCHAR2(10),
c DATE
);
CREATE OR REPLACE TYPE tt_tab AS TABLE OF tt;
DECLARE
v_tt_tab tt_tab;
v_tt     tt;
v_cur    SYS_REFCURSOR;
BEGIN
DELETE FROM t
WHERE a < 4
RETURNING tt(a, b, c) BULK COLLECT INTO v_tt_tab;
OPEN v_cur FOR
SELECT tt(a,
b,
c)
FROM TABLE(v_tt_tab);
LOOP
FETCH v_cur
INTO v_tt;
EXIT WHEN v_cur%NOTFOUND;

dbms_output.put_line(v_tt.a || ' ' || v_tt.b || ' ' || v_tt.c);
END LOOP;
CLOSE v_cur;
END;
/
/*
1 a 07-OCT-20
2 b 07-OCT-20
3 c 07-OCT-20
*/

通过创建一个与我们想要保存的数据匹配的对象和该对象的表，我们可以将其返回到集合中，然后使用TABLE函数轻松地将其返回为光标。

您需要小心删除多少行，因为您不想耗尽内存。如果删除的行数超过几百行，我会对这种方法持谨慎态度。

另一个建议可能是使用GTT，插入您计划删除的行，然后使用GTT从第一个表中删除；键"；。

CREATE GLOBAL TEMPORARY TABLE t_gtt (
a NUMBER,
b VARCHAR2(10),
c DATE
);
DECLARE
v_tt_tab tt_tab;
v_tt     tt;
v_cur    SYS_REFCURSOR;
BEGIN
INSERT INTO t_gtt
SELECT *
FROM t
WHERE a < 4;
DELETE FROM t
WHERE EXISTS (SELECT NULL
FROM t_gtt
WHERE t_gtt.a = t.a);
OPEN v_cur FOR
SELECT tt(a,
b,
c)
FROM t_gtt;
LOOP
FETCH v_cur
INTO v_tt;
EXIT WHEN v_cur%NOTFOUND;

dbms_output.put_line(v_tt.a || ' ' || v_tt.b || ' ' || v_tt.c);
END LOOP;
CLOSE v_cur;
END;
/

如果您计划删除大量的行，此选项可能会更好。我再次使用了我的tt对象，但你真的不需要它。它只是让循环转储SYS_REFCURSOR的内容变得更容易。