UPDATE statement vs CURSOR FOR UPDATE
590925Nov 23 2007 — edited Nov 27 2007In order to write a procedure that updates one record (say SALARY) of the rows of table EMPLOYEE which satisfy the condition DEPT_CODE='D001' I can use a plain UPDATE statement like:
UPDATE EMPLOYEE SET SALARY = 10000 WHERE DEPT_CODE='D001'
or use a cursor for update construct:
cursor emp_cur (dept_no varchar2) is
select SALARY from EMPLOYEE where DEPT_CODE = dept_no
for update of EMPLOYEE;
empsal number(8);
begin
open emp_cur(dno);
loop
fetch emp_cur into empsal;
exit when emp_cur%NOTFOUND;
update EMPLOYEE set SALARY= 10000 where current of emp_cur;
end loop;
close emp_cur;
commit;
could somebody tell me which are the advantages of using the cursor for update contruct with respect to the plain UPDATE statement?
thanks in advance
Pietro