PL/SQL Program: Count Employees in Each Department

PL/SQL While Loop: Exercise-15 with Solution

Write a PL/SQL program to display the number of employees in each department using a nested while loop. Return department name and number of employees.

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_count NUMBER;
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
  DBMS_OUTPUT.PUT_LINE('Employee Name' || CHR(9) || 'Number of Employees');
  DBMS_OUTPUT.PUT_LINE('-------------------------------');
  OPEN c_departments;
  FETCH c_departments INTO v_department_id, v_department_name;
  WHILE c_departments%FOUND LOOP
v_employee_count := 0;
    DBMS_OUTPUT.PUT(v_department_name || CHR(9) || '');
    OPEN c_employees(v_department_id);
    FETCH c_employees INTO v_employee_name;
    WHILE c_employees%FOUND LOOP
v_employee_count := v_employee_count + 1;
      FETCH c_employees INTO v_employee_name;
    END LOOP;
    CLOSE c_employees;
    DBMS_OUTPUT.PUT_LINE(v_employee_count);
    FETCH c_departments INTO v_department_id, v_department_name;
  END LOOP;
  CLOSE c_departments;
END;
/

Sample Output:

Employee Name	Number of Employees
-------------------------------
Administration	1
Marketing	2
Purchasing	6
Human Resources	1
Shipping	45
IT	5
Public Relation	1
Sales	34
Executive	3
Finance	6
Accounting	2
Treasury	0
Corporate Tax	0
Control AndCre	0
Shareholder Ser	0
Benefits	0

.....

Explanation:

The said code in Oracle's PL/SQL that retrieves the departments from the 'departments' table and count the number of employees in each department.

The cursor c_departments fetches the department details, then the outer WHILE loop iterates through each department.

A counter v_employee_count is initialized to zero for each department in the loop.

The inner WHILE loop fetches the employee names for that department with the current department ID from the cursor c_employees. The v_employee_count is incremented for each employee found.

The program prints the department name followed by the corresponding number of employees using.

The DBMS_OUTPUT.PUT_LINE procedure displays the department name followed by the corresponding number of employees The nested loops continue until all departments and employees have been processed.

Flowchart:

Previous: Average salary by job title.
Next: PL/SQL program to display total number of employees hired each year.