PL/SQL Cursor Exercises: Display the last name of manager, and their departments for a particular city, using parameters with a default value in explicit cursor

PL/SQL Cursor: Exercise-39 with Solution

Write a PL/SQL block to display the last name of manager, and their departments for a particular city, using parameters with a default value in explicit 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

location_id			integer
street_address			varchar(50)
postal_code			varchar(13)
city				varchar(25)
state_province			varchar(25)
country_id			varchar(2)

PL/SQL Code:

DECLARE
  CURSOR emp_cur (dep_location NUMBER DEFAULT 1700) IS
    SELECT d.department_name,
           e.last_name manager,
           l.city
    FROM departments d, employees e, locations l
    WHERE l.location_id = dep_location
      AND l.location_id = d.location_id
      AND d.department_id = e.department_id
    ORDER BY d.department_id;
 
  PROCEDURE dep_cur  IS
    depname departments.department_name%TYPE;
    dep_mgr employees.last_name%TYPE;
    dep_in_city locations.city%TYPE;
  BEGIN
    LOOP
      FETCH emp_cur INTO depname, dep_mgr, dep_in_city;
      EXIT WHEN emp_cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(rpad(depname,35) ||rpad(dep_mgr,15)|| dep_in_city);
    END LOOP;
  END dep_cur;
 
BEGIN

  DBMS_OUTPUT.PUT_LINE('DEPARTMENTS AT :');
  DBMS_OUTPUT.PUT_LINE('----------------------------');
  DBMS_OUTPUT.PUT_LINE(rpad('Department',35)||rpad('Manager',15)||'City');
  DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
  OPEN emp_cur;
  dep_cur; 
  CLOSE emp_cur;
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');
  OPEN emp_cur(1400); 
  dep_cur; 
  CLOSE emp_cur;
END;
/

Sample Output:

SQL> /
DEPARTMENTS AT :
----------------------------
Department                         Manager        City
--------------------------------------------------------
Administration                     Whalen         Seattle
Purchasing                         Colmenares     Seattle
Purchasing                         Baida          Seattle
Purchasing                         Himuro         Seattle
Purchasing                         Raphaely       Seattle
Purchasing                         Khoo           Seattle
Purchasing                         Tobias         Seattle
Executive                          Kochhar        Seattle
Executive                          De Haan        Seattle
Executive                          King           Seattle
Finance                            Popp           Seattle
Finance                            Greenberg      Seattle
Finance                            Faviet         Seattle
Finance                            Chen           Seattle
Finance                            Urman          Seattle
Finance                            Sciarra        Seattle
Accounting                         Gietz          Seattle
Accounting                         Higgins        Seattle
--------------------------------------------------------
IT                                 Austin         Southlake
IT                                 Ernst          Southlake
IT                                 Pataballa      Southlake
IT                                 Lorentz        Southlake
IT                                 Hunold         Southlake

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a PL/SQL block to display the last name, first name and overpaid amount by using parameters.
Next: Write a PL/SQL block to find out the start date for current job of a specific employee.