Using PL/SQL WHERE CURRENT OF & FOR UPDATE

Ij
Posted by Ij under Oracle category on | Points: 40 | Views : 388
Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly
updated.
Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock
a set of Oracle rows for the duration of a transaction.
DECLARE
CURSOR c is select empno,ename,sal from emp
where comm is null
for update of comm;
var_comm NUMBER(10,2);
BEGIN
FOR r in c loop
IF r.sal<5000 THEN
var_comm:=r.sal*0.25;
ELSIF r.sal<1000 THEN
var_comm:=r.sal*0.20;
ELSIF r.sal<3000 THEN
var_comm:=r.sal*0.15;
ELSE
var_comm:=r.sal*0.12;
END IF;
UPDATE emp
SET comm=var_comm
WHERE CURRENT OF C;
END LOOP;
END;
/

Comments or Responses

Login to post response