where current of

WHERE CURRENT OF:
-------------------

WHERE CURRENT OF clause is used in some UPDATE statements.

The WHERE CURRENT OF clause is an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated.

We must declare the cursor with FOR UPDATE clause to use this feature.

When the session opens the cursor with the FOR UPDATE clause, all rows in the return set will hold row-level locks. Other sessions can only query for the rows, but cannot update or delete.


declare
cursor c1
is
select empno,ename,sal from emp
where comm is null
for update of comm;

var_comm number(4);
begin
for cur_rec in c1 loop
if cur_rec.sal < 2000 then
var_comm:=200;
elsif cur_rec.sal <4000 then
var_comm:=400;
else
var_comm:=100;
end if;

update emp set comm=var_comm
where current of c1;

end loop;
end;
/