I was asked to create PL/SQL procedure to clear warehouse table. The procedure when started should delete 10 oldest records from table. Oldest by the field, say, INSERTDATA. Also, deletion must be faster when the procedure is launched from more than one session. Obviously, I had to solve problems of blocked records. The code of procedure is shown below (documents - name of the table).
CREATE OR REPLACE PROCEDURE delete_oldest
IS
locked_row_founded EXCEPTION;
PRAGMA EXCEPTION_INIT(locked_row_founded, -54);
counter INTEGER := 0;
rec documents%ROWTYPE;
BEGIN
FOR cur IN (SELECT * FROM documents ORDER BY insertdata)
LOOP
BEGIN
SELECT * INTO rec
FROM documents
WHERE id = cur.id FOR UPDATE NOWAIT;
DELETE FROM documents WHERE id = cur.id;
counter := counter + 1;
EXCEPTION
WHEN locked_row_founded THEN NULL;
END;
EXIT WHEN counter = 10;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
No comments:
Post a Comment