Tuesday, May 22, 2012

any wrong on this DELETE procedure. can we write like this or is there any better way to write same proc

Here we are just deleting the records from master and child table prior to 90days history records. Assume that tables have more than 20k records are there to delete. And here I put commit for each 5k records. Please let me know if I am wrong here?



create or replace Procedure PURGE_CLE_ALL_STATUS  ( days_in IN number ) 
IS

reccount NUMBER := 0;

CURSOR del_record_cur IS

SELECT EXCEPTIONID FROM EXCEPTIONREC
WHERE trunc(TIME_STAMP) < trunc(sysdate - days_in );


BEGIN
FOR rec IN del_record_cur LOOP

delete from EXCEPTIONRECALTKEY -- child table
where EXCEPTIONID =rec.EXCEPTIONID ;

delete from EXCEPTIONREC -- master table
where EXCEPTIONID =rec.EXCEPTIONID;


reccount := reccount + 1;

IF (reccount >= 1000) THEN
COMMIT;
count := 0;
END IF;
commit;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from <OWNER>.<TABLE_NAME>.');
END;
/




No comments:

Post a Comment