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:

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.