Oracle PL/SQL - FETCH with FOR UPDATE Cursor After COMMIT Statement

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

Introduction

The rows of the result set are locked when you open a FOR UPDATE cursor, not as they are fetched.

The rows are unlocked when you commit or roll back the transaction.

After the rows are unlocked, you cannot fetch from the FOR UPDATE cursor.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w w w .j a v a  2 s .com

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-1980', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL>
SQL> DROP TABLE emp;

Table dropped.

SQL> CREATE TABLE emp AS SELECT * FROM emp;
SQL>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT * FROM emp
  4      FOR UPDATE OF salary
  5      ORDER BY empid;
  6
  7    emp_rec  emp%ROWTYPE;
  8  BEGIN
  9    OPEN c1;
 10    LOOP
 11      FETCH c1 INTO emp_rec; 
 12      EXIT WHEN c1%NOTFOUND;
 13      DBMS_OUTPUT.PUT_LINE (
 14        'emp_rec.empid = ' ||
 15        TO_CHAR(emp_rec.empid)
 16      );
 17
 18      UPDATE emp
 19      SET salary = salary * 1.05
 20      WHERE empid = 105;
 21
 22      COMMIT;  -- releases locks
 23    END LOOP;
 24  END;
 25  /

SQL>

Related Topic