Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UPDATE statement vs CURSOR FOR UPDATE

590925Nov 23 2007 — edited Nov 27 2007
In 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 25 2007
Added on Nov 23 2007
12 comments
3,794 views