Oracle PL/SQL - FOR UPDATE Cursor

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

Introduction

SELECT FOR UPDATE on an explicit cursor creates a FOR UPDATE cursor.

Only a FOR UPDATE cursor can appear in the CURRENT OF clause of an UPDATE or DELETE statement.

In the following code, a FOR UPDATE cursor appears in the CURRENT OF clause of an UPDATE statement.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w  ww.  j a  va2s  . c om

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> DECLARE
  2    my_emp_id NUMBER(6);
  3    my_job_id VARCHAR2(10);
  4    my_sal    NUMBER(8,2);
  5    CURSOR c1 IS
  6      SELECT empid, job_id, salary
  7      FROM emp FOR UPDATE;
  8  BEGIN
  9    OPEN c1;
 10    LOOP
 11      FETCH c1 INTO my_emp_id, my_job_id, my_sal;
 12      IF my_job_id = 'SA_REP' THEN
 13        UPDATE emp
 14        SET salary = salary * 1.02
 15        WHERE CURRENT OF c1;
 16      END IF;
 17      EXIT WHEN c1%NOTFOUND;
 18    END LOOP;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL>

Related Topics