Oracle PL/SQL - SELECT FOR UPDATE Statement for Multiple Tables

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

Introduction

In the following code, SELECT FOR UPDATE queries the tables emp and DEPARTMENTS, but only SALARY appears in the FOR UPDATE clause.

SALARY is a column of emp, SELECT FOR UPDATE locks only rows of emp.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from   w  ww . j  a v  a2 s .  c o  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> drop TABLE departments;

Table dropped.

SQL> CREATE TABLE departments(
  2  department_id NUMBER(4),
  3  department_name VARCHAR2(30)CONSTRAINT dept_name_nn NOT NULL,
  4  manager_id NUMBER(6),
  5  location_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO departments VALUES( 10, 'Administration', 200, 1700);
SQL> INSERT INTO departments VALUES( 20, 'Marketing', 201, 1000);
SQL> INSERT INTO departments VALUES( 30, 'Purchasing', 114, 1700);
SQL> INSERT INTO departments VALUES( 40, 'Human Resources', 203, 1000);
SQL> INSERT INTO departments VALUES( 50, 'Shipping', 121, 1700);
SQL>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name, department_name
  4      FROM emp, departments
  5      WHERE emp.department_id = departments.department_id
  6      FOR UPDATE OF salary;
  7  BEGIN
  8    NULL;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>

Related Topic