Search code examples
sqloracle-databaseplsqlsql-deletefor-in-loop

PL/SQL - Delete current record in FOR IN LOOP

I am trying to figure out how to delete a record using the FOR .. IN .. LOOP

FOR REC IN (SELECT * FROM WORKLIST) LOOP
  GET_DATA(REC);
  --DELETE REC FROM WORKLIST
END LOOP;

The GET_DATA procedure explodes every row to multiple ones so the current one becomes obsolete. I am trying to delete just the current record. Not sure if this is possible using the REC object in this case.

Sadly, WORKLIST does not have any keys.

Solution

  • Use current of on cursor :

    DECLARE
CURSOR c1 IS 
    SELECT * from worklist FOR UPDATE <any column name>; 
BEGIN 
      open c1; 
      loop 
          fetch c1 into crow; 
          exit when c1%notfound;  
          DELETE FROM worklist WHERE CURRENT OF c1; 
      end loop;        
      COMMIT; 
      close c1; 
END;