У меня есть требование, когда я хочу вернуть удаленные записи в sys_refcursor. Я могу получить данные в курсоре перед оператором удаления, но есть ли способ получить их после оператора удаления? Я имею в виду, что процесс будет похож на сначала удаление, а затем открытие sys_refcursor для извлечения удаленных записей.
Вернуть удаленные строки в курсоре в PL / SQL
Ответы (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
.