PL/SQL program: Display Departments and Employees

PL/SQL While Loop: Exercise-12 with Solution

Write a PL/SQL program to display all the departments along with the names of employees working in each department.

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
v_department_iddepartments.department_id%TYPE;
v_department_namedepartments.department_name%TYPE;
v_employee_nameemployees.first_name%TYPE;

  CURSOR c_departments IS
    SELECT department_id, department_name
    FROM departments;
      CURSOR c_employees (p_department_id IN departments.department_id%TYPE) IS
    SELECT first_name
    FROM employees
    WHERE department_id = p_department_id;
BEGIN
  OPEN c_departments;
    FETCH c_departments INTO v_department_id, v_department_name;
    DBMS_OUTPUT.PUT_LINE('Department' || CHR(9) || 'First_Name');
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
    WHILE c_departments%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_department_name || CHR(9));
        OPEN c_employees(v_department_id);
        FETCH c_employees INTO v_employee_name;
        WHILE c_employees%FOUND LOOP
      DBMS_OUTPUT.PUT_LINE(CHR(9) || CHR(9) || v_employee_name);
        FETCH c_employees INTO v_employee_name;
    END LOOP;
      CLOSE c_employees;
      FETCH c_departments INTO v_department_id, v_department_name;
      DBMS_OUTPUT.PUT_LINE('-------------------------------------------------');
  END LOOP;
    CLOSE c_departments;
END;
/

Sample Output:

Department	First_Name
-------------------------------------------------
Administration	
		Jennifer   
-------------------------------------------------
Marketing	
		Michael    
		Pat        
-------------------------------------------------
Purchasing	
		Den        
		Alexander  
		Shelli     
		Sigal
		Guy        
		Karen      
-------------------------------------------------
Human Resources	
		Susan      
-------------------------------------------------
Shipping	
		Matthew    
		Adam       
		Payam

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves department information and their respective employees from a 'employees' and 'departments' tables and displays the results.

Three variables v_department_id, v_department_name, and v_employee_name are declared that stores the retrieved values from the departments and employees tables.

Two cursors c_departments that retrieves all department_id and department_name values from the departments table and the c_employees cursor is a nested cursor that retrieves the first_name of employees belonging to a specific department, identified by the p_department_id parameter are declared.

The FETCH statement retrieves the department_id and department_name from the c_departments cursor and assign into the variables.

The WHILE loop iterates over the departments retrieved by the c_departments cursor.

The another FETCH statement retrieves the first_name of an employee into the v_employee_name variable.

The nested WHILE loop iterates over the employees of the current department retrieved by the c_employees cursor.

The first_name of each employee is displayed using DBMS_OUTPUT.

The FETCH statement fetches the next employee's first_name.

The FETCH statement retrieves the next department_id and department_name from the c_departments cursor into the respective variables.

Continue looping: The loop continues until all departments are processed.

Flowchart:

Previous: Display department information.
Next: Average salary by job title.