Oracle PL/SQL - Simulating CURRENT OF Clause with ROWID Pseudo column

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

Description

Simulating CURRENT OF Clause with ROWID Pseudo column

Demo

SQL>
SQL> drop table emp;

Table dropped.--  w w  w .j a  va  2 s.co  m

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 last_name, job_id, rowid
  4      FROM emp;  -- no FOR UPDATE clause
  5
  6    my_lastname   emp.last_name%TYPE;
  7    my_jobid      emp.job_id%TYPE;
  8    my_rowid      UROWID;
  9  BEGIN
 10    OPEN c1;
 11    LOOP
 12      FETCH c1 INTO my_lastname, my_jobid, my_rowid;
 13      EXIT WHEN c1%NOTFOUND;
 14
 15      UPDATE emp
 16      SET salary = salary * 1.02
 17      WHERE rowid = my_rowid;  -- simulates WHERE CURRENT OF c1
 18
 19      COMMIT;
 20    END LOOP;
 21    CLOSE c1;
 22  END;
 23  /

PL/SQL procedure successfully completed.

SQL>

Related Topic