Using the FOR UPDATE Statement

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:

-- Script 1: Uses FOR UPDATE OF

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;


--Script 2: Does the same thing like script 1 but FOR UPDATE OF is not used here

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?

+5
source share
1

FOR UPDATE, , SELECT . FOR UPDATE , UPDATE . script SELECT , UPDATE.

SELECT, , , . FOR UPDATE SELECT -, , script , - , .

+12

All Articles