Wednesday, July 4, 2012

How to delete 10 oldest records from many sessions

 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