NAMED STORE PROCEDURE SAMPLE

/* Lo script ripulisce di dati antecedenti alla data memorizzata nella variabile dateFrom.
* La variabile daysFrom indica di quanti giorni voglio arretrare la data di sistema.
*/
create or replace
PROCEDURE PURGE_CM_ORDERS_TABLES AS
daysFrom NUMBER := 45;
dateFrom DATE := SYSDATE - daysFrom;
begin
delete from service_request_tracking s
where s.REQUEST_ID in (
select r1.REQUEST_ID from request r1
where r1.REQUEST_DATE <= dateFrom
);
delete from iemx_order ord
where ord.REQUEST_ID in (
select r.REQUEST_ID from request r
where r.REQUEST_DATE <= dateFrom
);
delete from order_to_fullqualified_name fqn
where fqn.REQUEST_ID in (
select r1.REQUEST_ID from request r1
where r1.REQUEST_DATE <= dateFrom
);
delete from request_info info
where info.REQUEST_ID in (
select r.REQUEST_ID from request r
where r.REQUEST_DATE <= dateFrom
);
delete from request r
where r.REQUEST_DATE <= dateFrom;
delete from IEMX_APP.WORKLOG w
where w.CONFIGORDERID in (
select conf.CONFIGORDERID from IEMX_APP.CONFIGORDER conf
where conf.CREATIONDATE <= dateFrom
);
delete from IEMX_APP.CONFIGORDER conf
where conf.CREATIONDATE <= dateFrom;
COMMIT;
END PURGE_CM_ORDERS_TABLES;
/