Oracle PL/SQL - SELECT FOR UPDATE command

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. FOR UPDATE Cursor

Introduction

To lock all the records while you're working on them, use a SELECT FOR UPDATE command.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  w w.  j a v  a  2 s .  c om

Elapsed: 00:00:00.02
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.

Elapsed: 00:00:00.02
SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL>
SQL>
SQL> declare
  2     cursor c_empInDept is
  3        select * from emp
  4        for update of sal;
  5  begin
  6     for r_emp in c_empInDept loop
  7        if r_emp.sal < 5000 then
  8          update emp
  9           set sal = sal * 1.1
 10          where current of c_empInDept;
 11        end if;
 12     end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
SQL>

Related Topic