I am trying to create a stored procedure with following functionality.
I need to select a table where status = 'G' and return the row to the
application and then update all those rows that I just selected to
change status to 'P'. This table is being inserted all the time.
can i do this :
create a procedure sp1
....
RESULT SETS 1
....
declare cursor c1 with return for
select ....
from tb1
where status = 'G' for update of status
....
....
open c1
fetch c1 into outvariable
while .. do
update tb1 set status = 'P'
where current of c1
fetch c1 into outvariable
end while
....
Will this work ? or is there any other way to accomplish this.
Thanks