PL/SQL Cursor Exercises: FETCH records with nested Cursors using Cursor FOR Loops

PL/SQL Cursor: Exercise-20 with Solution

Write a program in PL/SQL to FETCH records with nested Cursors using Cursor FOR Loops.

Nested Cursors Using Cursor FOR Loops

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

DECLARE
  emp_dept_id departments.department_id%TYPE;
 CURSOR cur_dept IS
  SELECT * 
  FROM departments
  WHERE manager_id IS NOT NULL
  ORDER BY department_name;
 CURSOR cur_emp IS
  SELECT * 
  FROM employees
  WHERE department_id = emp_dept_id;
  
BEGIN
    FOR r_dept IN cur_dept
    LOOP
      emp_dept_id := r_dept.department_id;
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
           FOR r_emp IN cur_emp 
           LOOP
             DBMS_OUTPUT.PUT_LINE('Employee: '||r_emp.last_name);
           END LOOP;   
    END LOOP;
END;
 /

Sample Output:

SQL> /
----------------------------------
Department Name : Accounting
----------------------------------
Employee: Higgins
Employee: Gietz
----------------------------------
Department Name : Administration
----------------------------------
Employee: Whalen
----------------------------------
Department Name : Executive
----------------------------------
Employee: King
Employee: Kochhar
Employee: De Haan
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor.
Next: Write a program in PL/SQL to print a list of managers and the name of the departments.