PL/SQL - Delete current record in FOR IN LOOP

Question

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.

Answer 1

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;

Answer 2

If WORKLIST has a primary key, just use it:

DELETE FROM WORKLIST WHERE <PK> = REC.<PK>

If no primary key I would try:

DECLARE
  vTmp schema.worklist%rowtype;
BEGIN
  FOR rec IN (SELECT rowid, w.* FROM WORKLIST w) LOOP
    vTmp := null;
    vTmp.col1 := rec.col1;
    vTmp.col2 := rec.col2;
    -- And so on ...
    GET_DATE(vTmp);
    DELETE FROM WORKLIST WHERE rowid = rec.rowid;
  END LOOP;
END;