By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE TB_ZAB (id_zab, id_zbez) AS
SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 5;
5 rows affected
CREATE TABLE TB_ZBEZ (id_zbez, id_bp) AS
SELECT LEVEL, 14058 FROM DUAL CONNECT BY LEVEL <= 4;
4 rows affected
CREATE TABLE TB_EGZAB (id_zab) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;
3 rows affected
DELETE FROM TB_EGZAB
WHERE ID_ZAB in (
SELECT zab.ID_ZAB
FROM TB_ZAB zab
INNER JOIN TB_ZBEZ zbez
ON zab.ID_ZBEZ = zbez.ID_ZBEZ
WHERE zbez.ID_BP = 14058
);
3 rows affected
ROLLBACK;
CREATE TYPE number_list IS TABLE OF NUMBER;
DECLARE
bp_id TB_ZBEZ.ID_ZBEZ%TYPE := 14058;
zab_ids number_list;
BEGIN
SELECT zab.ID_ZAB
BULK COLLECT INTO zab_ids
FROM TB_ZAB zab
INNER JOIN TB_ZBEZ zbez
ON zab.ID_ZBEZ = zbez.ID_ZBEZ
WHERE zbez.ID_BP = bp_id;
DELETE FROM TB_EGZAB
WHERE ID_ZAB MEMBER OF zab_ids;
FOR i IN 1 .. zab_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(zab_ids(i));
END LOOP;
END;
/
1 rows affected
dbms_output:
1
2
3
4
ROLLBACK;
DECLARE
TYPE t_num IS TABLE OF NUMBER;
v_ids t_num;
BEGIN
DELETE FROM TB_EGZAB
WHERE ID_ZAB in (
SELECT zab.ID_ZAB
FROM TB_ZAB zab
INNER JOIN TB_ZBEZ zbez
ON zab.ID_ZBEZ = zbez.ID_ZBEZ
WHERE zbez.ID_BP = 14058
)
RETURNING id_zab BULK COLLECT INTO v_ids;
-- Do something with the array in PL/SQL, since it is only defined in PL/SQL
FOR i IN 1 .. v_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_ids(i));
END LOOP;
END;
/
1 rows affected
dbms_output:
1
2
3