PL/SQL Cursor Exercises: FETCH multiple records with the uses of nested cursor

PL/SQL Cursor: Exercise-19 with Solution

Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor.

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
    e_dept_no employees.department_id%TYPE;
    CURSOR cur_deptartments IS
      SELECT *
      FROM   departments;
    CURSOR cur_employees IS
      SELECT *
      FROM   employees e
      WHERE  e.department_id = e_dept_no;
    v_deptrec departments%ROWTYPE;
    v_emprec  employees%ROWTYPE;
BEGIN
    OPEN cur_deptartments;
    LOOP
        FETCH cur_deptartments INTO v_deptrec;
        exit WHEN cur_deptartments%NOTFOUND;
        e_dept_no := v_deptrec.department_id;
		
		
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||v_deptrec.department_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');		

        OPEN cur_employees;
        LOOP
            FETCH cur_employees INTO v_emprec;
            exit WHEN cur_employees%NOTFOUND;
            dbms_output.Put_line('Employee: '
                                 || v_emprec.first_name
								 ||chr(9)||'Salary: '
								 || v_emprec.salary);
        END LOOP;
        CLOSE cur_employees;

    END LOOP;
    CLOSE cur_deptartments;
END; 
 /

Sample Output:

SQL> /
----------------------------------
Department Name : Administration
----------------------------------
Employee: Jennifer      Salary: 4400
----------------------------------
Department Name : Marketing
----------------------------------
Employee: Michael       Salary: 13000
Employee: Pat   Salary: 6000
----------------------------------
Department Name : Purchasing
----------------------------------
Employee: Den   Salary: 11000
Employee: Alexander     Salary: 3100
Employee: Shelli        Salary: 2900
Employee: Sigal Salary: 2800
Employee: Guy   Salary: 2600
Employee: Karen Salary: 2500
...

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to FETCH multiple records and more than one columns from different tables.
Next: Write a program in PL/SQL to FETCH records with nested Cursors using Cursor FOR Loops.