w3resource
PL/SQL Cursor Exercises

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:

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:

Flowchart: 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

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.

What is the difficulty level of this exercise?