PL/SQL program: Department-wise total salary expense

PL/SQL While Loop: Exercise-14 with Solution

Write a PL/SQL program to display the total salary expense for each department. Return depart name and salary expenses in tabular form.

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_namedepartments.department_name%TYPE;
v_total_salary_expense NUMBER;
v_department_count NUMBER;
  CURSOR c_departments IS
    SELECT department_name
    FROM departments;
  CURSOR c_employees (p_department_name IN departments.department_name%TYPE) IS
    SELECT e.salary
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
    WHERE d.department_name = p_department_name;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Department Name' || CHR(9) || 'Salary Expense');
  DBMS_OUTPUT.PUT_LINE('----------------------------------');
  OPEN c_departments;
  FETCH c_departments INTO v_department_name;
  WHILE c_departments%FOUND LOOP
v_total_salary_expense := 0;
v_department_count := 0;
    OPEN c_employees(v_department_name);
    FETCH c_employees INTO v_total_salary_expense;
    WHILE c_employees%FOUND LOOP
v_department_count := v_department_count + 1;
      FETCH c_employees INTO v_total_salary_expense;
    END LOOP;
    CLOSE c_employees;
    DBMS_OUTPUT.PUT_LINE(v_department_name || CHR(9) || v_total_salary_expense);
    FETCH c_departments INTO v_department_name;
  END LOOP;
  CLOSE c_departments;
END;
/

Sample Output:

Department Name	Salary Expense
----------------------------------
Administration	4400
Marketing	6000
Purchasing	2500
Human Resources	6500
Shipping	3000
IT	4200
Public Relation	10000
Sales	6200
Executive	17000
Finance	6900
Accounting	8300
Treasury	0
Corporate Tax	0
Control AndCre	0
Shareholder Ser	0
Benefits	0
Manufacturing	0
Construction	0
Contracting	0
Operations	0
IT Support	0

.....

Explanation:

The said code in Oracle's PL/SQL that calculates the total salary expense by summing up the salaries of employees in each department and displays the results.

The variables v_department_name, v_total_salary_expense, and v_department_count are declared to store department names, total salary expense, and a counter for employees in each department.

The two cursors c_departments that fetchs the department names from the departments table, and c_employees that fetch the salaries of employees based on the department name are defines.

A loop that executes as long as there are more departments to process on c_departments cursor that fetches the department names. The variables v_total_salary_expense and v_department_count initialized to zero for each department.

The cursor c_employees opens with the current v_department_name as a parameter and fetches the salary of the first employee into v_total_salary_expense and continues fetching until process all employees in the department.

Increments the v_department_count to keep track of the number of employees.

The DBMS_OUTPUT.PUT_LINE statement displays the department name and the corresponding total salary expense.

Flowchart:

Previous: Average salary by job title.
Next: Count Employees in Each Department.