Oracle PL/SQL - Query Result Set Processing with Subqueries

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

Introduction

You can use subquery in cursor to reduce nested loop.

The following defines explicit cursor c1 with a query whose FROM clause contains a subquery.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from w  w  w.  j a va 2s.  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> 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>
SQL>
SQL> --Subquery in FROM Clause of Parent Query
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT t1.department_id, department_name, staff
  4      FROM departments t1,
  5            ( SELECT department_id, COUNT(*) AS staff
  6             FROM emp
  7             GROUP BY department_id
  8           ) t2
  9      WHERE (t1.department_id = t2.department_id) AND staff >= 1
 10      ORDER BY staff;
 11
 12  BEGIN
 13     FOR dept IN c1
 14     LOOP
 15       DBMS_OUTPUT.PUT_LINE ('Department = '|| dept.department_name || ', staff = ' || dept.staff);
 16     END LOOP;
 17  END;
 18  /
Department = Marketing, staff = 1
Department = Administration, staff = 1

PL/SQL procedure successfully completed.

SQL>

Related Topic