Oracle PL/SQL - Cursor for Correlated Subquery

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursors

Introduction

An ordinary subquery is evaluated for each table.

A correlated subquery is evaluated for each row.

The following code shows how to Cursor for Correlated Subquery.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w  w .jav a 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>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT department_id, last_name, salary
  4      FROM emp t
  5      WHERE salary >=  ( SELECT AVG(salary)
  6     FROM emp
  7     WHERE t.department_id = department_id
  8   )
  9      ORDER BY department_id, last_name;
 10  BEGIN
 11    FOR person IN c1
 12    LOOP
 13      DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);
 14    END LOOP;
 15  END;
 16  /
Making above-average salary = King
Making above-average salary = Lee

PL/SQL procedure successfully completed.

SQL>

Related Topic