Oracle PL/SQL - PL SQL Cursor CURSOR Expressions

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Cursor Variables

Introduction

A CURSOR expression returns a nested cursor.

It has this syntax: 

CURSOR ( subquery )

You can use a CURSOR expression in a SELECT statement or pass it to a function.

You cannot use a cursor expression with an implicit cursor.

The following code declares and defines an explicit cursor for a query that includes a cursor expression.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   ww w  .j av a2 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 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    TYPE emp_cur_typ IS REF CURSOR;
  3      emp_cur    emp_cur_typ;
  4      dept_name  departments.department_name%TYPE;
  5      emp_name   emp.last_name%TYPE;
  6
  7      CURSOR c1 IS
  8        SELECT department_name,
  9          CURSOR ( SELECT e.last_name
 10                  FROM emp e
 11                  WHERE e.department_id = d.department_id
 12                  ORDER BY e.last_name
 13                  ) emp
 14        FROM departments d
 15        ORDER BY department_name;
 16  BEGIN
 17      OPEN c1;
 18      LOOP 
 19        FETCH c1 INTO dept_name, emp_cur;
 20        EXIT WHEN c1%NOTFOUND;
 21        DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
 22
 23        LOOP 
 24          FETCH emp_cur INTO emp_name;
 25          EXIT WHEN emp_cur%NOTFOUND;
 26          DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
 27        END LOOP;
 28      END LOOP;
 29      CLOSE c1;
 30  END;
 31  /
Department: Administration
-- Employee: King
Department: Human Resources
Department: Marketing
-- Employee: Lee
Department: Purchasing
Department: Shipping

PL/SQL procedure successfully completed.

SQL>

Related Topic