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