Salary Package PL/SQL: calculate_avg_salary

PL/SQL Package: Exercise-7 with Solution

Write a PL/SQL package that contains a procedure to calculate the average salary of employees in a specific 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

PL/SQL Code:

CREATE OR REPLACE PACKAGE salary_pkg AS
  PROCEDURE calculate_avg_salary(p_department_id NUMBER);
END salary_pkg;
/
CREATE OR REPLACE PACKAGE BODY salary_pkg AS
  PROCEDURE calculate_avg_salary(p_department_id NUMBER) IS
v_total_salary NUMBER := 0;
v_employee_count NUMBER := 0;
v_avg_salary NUMBER;
  BEGIN
    FOR emp IN (SELECT salary FROM employees WHERE department_id = p_department_id) LOOP
v_total_salary := v_total_salary + emp.salary;
v_employee_count := v_employee_count + 1;
    END LOOP;
    IF v_employee_count> 0 THEN
v_avg_salary := v_total_salary / v_employee_count;
      DBMS_OUTPUT.PUT_LINE('Average Salary for Department ' || p_department_id || ': ' || v_avg_salary);
    ELSE
      DBMS_OUTPUT.PUT_LINE('No employees found in Department ' || p_department_id);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
  END calculate_avg_salary;
END salary_pkg;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

BEGIN
salary_pkg.calculate_avg_salary(30);
END;
/

Sample Output:

Average Salary for Department 30: 4150

Explanation:

The said code in Oracle's PL/SQL package that calculates the average salary and outputs it using the DBMS_OUTPUT.PUT_LINE function.

This code defines a package 'salary_pkg' and the package has a procedure 'calculate_avg_salary'.

It initializes variables 'v_total_salary' and 'v_employee_count' to zero, and 'v_avg_salary' to store the calculated average salary.

The procedure takes one input parameter 'p_department_id' of type NUMBER, representing the department for which the average salary needs to be calculated.

In the procedure a FOR loop iterates through the employees for a specified department.

It calculates the total salary and counts the number of employees in the department.

If there are employees, it calculates the average salary and displays it using the DBMS_OUTPUT.PUT_LINE function.

The code includes an exception block to handle any errors that may occur during the process and displays the error message using DBMS_OUTPUT.PUT_LINE.

Previous: PL/SQL Package for date calculation and format conversion.
Next: Employee count package for job titles.