PL/SQL Cursor Exercises: Print a list of managers and the name of the departments

PL/SQL Cursor: Exercise-21 with Solution

Write a program in PL/SQL to print a list of managers and the name of the departments.

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
  CURSOR cur_mgr IS
      SELECT first_name,
             last_name,
             department_name
      FROM employees e
      INNER JOIN departments d ON d.manager_id = e.employee_id;
 
  v_mgr cur_mgr%ROWTYPE;
BEGIN
  OPEN cur_mgr;
  LOOP
    -- fetch information from cursor into record
    FETCH cur_mgr INTO v_mgr;
    EXIT WHEN cur_mgr%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_mgr.department_name || ' :: ' ||
                         v_mgr.first_name || ' ' ||
                         v_mgr.last_name);
  END LOOP;
  CLOSE cur_mgr;
END;
/

Sample Output:

SQL> /
Executive :: Steven King
IT :: Alexander Hunold
Finance :: Nancy Greenberg
Purchasing :: Den Raphaely
Shipping :: Adam Fripp
Sales :: John Russell
Administration :: Jennifer Whalen
Marketing :: Michael Hartstein
Human Resources :: Susan Mavris
Public Relations :: Hermann Baer
Accounting :: Shelley Higgins

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to FETCH records with nested Cursors using Cursor FOR Loops.
Next: Write a program in PL/SQL to insert data into two tables from one table using cursor.