Salary cost package in PL/SQL

PL/SQL Package: Exercise-12 with Solution

Write a PL/SQL code that create a package that contains a function to calculate the total salary cost for a specific department and its sub-departments.

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:

CREATE OR REPLACE PACKAGE SalaryCostPackage IS
  FUNCTION CalculateTotalSalary(
p_department_id IN NUMBER
  ) RETURN NUMBER;
END SalaryCostPackage;
/
CREATE OR REPLACE PACKAGE BODY SalaryCostPackage IS
  FUNCTION CalculateTotalSalary(
p_department_id IN NUMBER
  ) RETURN NUMBER IS
v_total_salary NUMBER := 0;
  BEGIN
    FOR dept IN (SELECT department_id FROM departments WHERE department_id = p_department_id OR manager_id = p_department_id)
    LOOP
      FOR emp IN (SELECT salary FROM employees WHERE department_id = dept.department_id)
      LOOP
v_total_salary := v_total_salary + emp.salary;
      END LOOP;
    END LOOP;
    RETURN v_total_salary;
  END CalculateTotalSalary;
END SalaryCostPackage;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

DECLARE
v_department_id NUMBER := 100; -- Specify the department ID for which to calculate the total salary cost
v_total_salary_cost NUMBER;
BEGIN
v_total_salary_cost := SalaryCostPackage.CalculateTotalSalary(v_department_id);
  DBMS_OUTPUT.PUT_LINE('Total Salary Cost: ' || v_total_salary_cost);
END;
/

Sample Output:

Statement processed.
Total Salary Cost: 109600

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that will sum up the salaries of all employees within the specified department and its sub-departments, returning the calculated total salary cost.

A function CalculateTotalSalary declared in the package which takes a single input parameter p_department_id, representing the department for which the total salary cost needs to be calculated.

Inside the function, a local variable v_total_salary of type NUMBER is declared to store the cumulative salary cost.

The function uses two nested loops to calculate the total salary cost.

The outer loop iterates through the departments that match the given p_department_id as the department_id, or where the department's manager_id matches the p_department_id.

The inner loop iterates through the employees of each department obtained from the outer loop.

For each employee, their salary is added to the v_total_salary.

Finally, the cumulative v_total_salary is returned as the result of the function.

Previous: Top n employees with highest salary.
Next: Calculate average age of employees in PL/SQL.