I am using PL / SQL (Oracle 11g) to update a table's salary column EMPLOYEES.
I used two separate scripts to do the same thing as updating staff salaries.
One script uses a statement FOR UPDATE OF, where another script does not use it. In both cases, I found that the oracle holds line level locks until we execute the ROLLBACKor command COMMIT.
Then what is the difference between the two scenarios?
Which one is better to use?
Here are two scenarios I'm talking about:
declare
cursor cur_emp
is
select employee_id,department_id from employees where department_id = 90 for update of salary;
begin
for rec in cur_emp
loop
update Employees
set salary = salary*10
where current of cur_emp;
end loop;
end;
declare
cursor cur_emp
is
select employee_id,department_id from employees where department_id = 90;
begin
for rec in cur_emp
loop
update Employees
set salary = salary*10
where Employee_ID = rec.employee_id;
end loop;
end;
I found that Oracle acquired row-level locks in both cases. So, what is the advantage of using FOR UPDATE OFand what is the best way to code?
source
share