Qual a melhor forma para excluir dados em tabelas master/details no oracle?
Atualmente me deparei com essa proc.
create or replace procedure SP_CLEAR_DATABASE is
DT DATE;
ID VARCHAR2(50);
BOX VARCHAR2(50);
ORDIO NUMBER;
ORDIOTYPE VARCHAR2(10);
SKUIO NUMBER;
SKUTYPE VARCHAR2(10);
CURSOR P IS
SELECT A.ORD_NUMBER FROM ORDER A WHERE A.ORD_INSERT_TIMESTAMP < DT;
CURSOR C IS
SELECT B.ORDER_BOX_ID FROM ORDER_BOX B WHERE B.ORD_NUMBER = ID;
CURSOR D IS
SELECT A.ID_ORDER_IO,A.ORDIO_IN_OUT FROM ORDER_IO A WHERE A.ORDIO_INSERT_TIMESTAMP < DT;
CURSOR E IS
SELECT A.ID_SKU_IO,A.SKUIO_IN_OUT FROM SKU_IO A WHERE A.SKUIO_INSERT_TIMESTAMP < DT;
begin
DT := TO_DATE('01/03/2012 00:00:00', 'DD/MM/YYYY HH24:MI:SS'); --SYSDATE-90;
DELETE FROM PICK A WHERE A.PICK_START < DT;
DELETE FROM SCANNER_LOG A WHERE A.SCALOG_TIMESTAMP < DT;
DELETE FROM ORDER_ITEM A WHERE A.ORDIT_START < DT;
DELETE FROM ORDER_BOX A WHERE A.ORDBOX_START < DT;
COMMIT;
open P;
loop
fetch P
into ID;
exit when P%notfound;
open C;
loop
fetch C into BOX;
exit when C%notfound;
DELETE PICK X WHERE X.ORDER_BOX_ID = BOX;
DELETE ORDER_ITEM B WHERE B.ORDER_BOX_ID = BOX;
DELETE ORDER_BOX A WHERE A.ORDER_BOX_ID = BOX;
end loop;
close C;
DELETE ORDER A WHERE A.ORD_NUMBER = ID;
COMMIT;
end loop;
close P;
open D;
loop
fetch D into ORDIO,ORDIOTYPE;
exit when D%notfound;
IF ORDIOTYPE = 'I' THEN
DELETE ORDER_IN A WHERE A.ID_ORDER_IO = ORDIO;
ELSE
DELETE ORDER_OUT A WHERE A.ID_ORDER_IO = ORDIO;
END IF;
DELETE ORDER_IO A WHERE A.ID_ORDER_IO = ORDIO;
COMMIT;
end loop;
close D;
open E;
loop
fetch E into SKUIO,SKUTYPE;
exit when E%notfound;
IF SKUTYPE = 'I' THEN
DELETE SKU_IN A WHERE A.ID_SKU_IO=SKUIO;
ELSE
DELETE SKU_OUT A WHERE A.ID_SKU_IO=SKUIO;
END IF;
DELETE SKU_IO A WHERE A.ID_SKU_IO=SKUIO;
COMMIT;
end loop;
close E;
-- DELETE FROM SKU A WHERE A.SKU_DISABLE_TIMESTAMP IS NOT NULL AND A.SKU_DISABLE_TIMESTAMP < DT;
UPDATE CONNECTION A
SET A.CONN_TIMESTAMP = SYSDATE
WHERE A.ID_CONNECTION = 1;
COMMIT;
end SP_CLEAR_DATABASE;
