Oracle PL/SQL - PL SQL Cursor CURSOR Expressions

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.

/* Formatted on 3/16/2023 1:41:03 PM (QP5 v5.388) */
CREATE TABLE emp
(
    empid             NUMBER (6),
    first_name        VARCHAR2 (20),
    last_name         VARCHAR2 (25),
    email             VARCHAR2 (25),
    phone_number      VARCHAR2 (20),
    hire_date         DATE,
    job_id            VARCHAR2 (10),
    salary            NUMBER (8, 2),
    commission_pct    NUMBER (2, 2),
    manager_id        NUMBER (6),
    department_id     NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:11 PM (QP5 v5.388) */
INSERT INTO emp
     VALUES (100,
             'Steven',
             'King',
             'SKING',
             '123.123.4567',
             TO_DATE ('17-JUN-1987', 'dd-MON-yyyy'),
             'CODER',
             24000,
             NULL,
             NULL,
             10);
INSERT INTO emp
     VALUES (200,
             'Joe',
             'Lee',
             'abc',
             '123.123.9999',
             TO_DATE ('17-JUN-1980', 'dd-MON-yyyy'),
             'TESTER',
             25000,
             NULL,
             NULL,
             20);

/* Formatted on 3/16/2023 1:41:17 PM (QP5 v5.388) */
CREATE TABLE departments
(
    department_id      NUMBER (4),
    department_name    VARCHAR2 (30) CONSTRAINT dept_name_nn NOT NULL,
    manager_id         NUMBER (6),
    location_id        NUMBER (4)
);
/* Formatted on 3/16/2023 1:41:20 PM (QP5 v5.388) */
INSERT INTO departments
     VALUES (10,
             'Administration',
             200,
             1700);
INSERT INTO departments
     VALUES (20,
             'Marketing',
             201,
             1000);
INSERT INTO departments
     VALUES (30,
             'Purchasing',
             114,
             1700);

INSERT INTO departments

     VALUES (40,
             'Human Resources',
             203,
             1000);

INSERT INTO departments

     VALUES (50,
             'Shipping',
             121,
             1700);

 

 DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
     emp_cur    emp_cur_typ;
     dept_name  departments.department_name%TYPE;
     emp_name   emp.last_name%TYPE;
     CURSOR c1 IS
       SELECT department_name,
         CURSOR ( SELECT e.last_name
                 FROM emp e
                 WHERE e.department_id = d.department_id
                 ORDER BY e.last_name
                 ) emp
       FROM departments d
       ORDER BY department_name;
 BEGIN
     OPEN c1;
     LOOP 
       FETCH c1 INTO dept_name, emp_cur;
       EXIT WHEN c1%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
       LOOP 
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
       END LOOP;
     END LOOP;
     CLOSE c1;
 END;