drop table t1
/
CREATE TABLE t1 (c1 integer, state integer)
/
insert into t1 values (1,0)
/
insert into t1 values (1,1)
/
insert into t1 values (1,0)
/
CREATE or replace PROCEDURE p1
AS
  cursor cur1 is select state from t1 for update ; 
  state integer;
begin
  open cur1;
  loop
    fetch cur1 into state;
    if (cur1%NOTFOUND) then
      exit;
    end if;
    -- do some work  ...

    if (state=0) then
      -- delete the current row    
      delete from t1 where current of cur1;
    else
      -- update the current row    
      update t1 set state = 2 where current of cur1;
    end if;
  end loop;
  close cur1;
  commit;
end;
/
select * from user_errors where name='P1'
/
begin
  declare res varchar(20);
  begin
    p1();
  end;
end;
/
select * from t1
/
