Average tenure of employees by job category package in PL/SQL

PL/SQL Package: Exercise-16 with Solution

Write a code in PL/SQL that create a package that contains a function to calculate the average tenure of employees in a specific job category.

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 emp_pkg IS
  FUNCTION get_average_tenure(job_category IN VARCHAR2) RETURN NUMBER;
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  FUNCTION get_average_tenure(job_category IN VARCHAR2) RETURN NUMBER IS
total_tenure NUMBER := 0;
employee_count NUMBER := 0;
avg_tenure NUMBER := 0;
  BEGIN
    SELECT SUM(MONTHS_BETWEEN(SYSDATE, hire_date))
    INTO total_tenure
    FROM employees
    WHERE job_id = job_category;
    SELECT COUNT(*) INTO employee_count
    FROM employees
    WHERE job_id = job_category;
    IF employee_count> 0 THEN
avg_tenure := total_tenure / employee_count;
    END IF;
    RETURN avg_tenure;
  END get_average_tenure;
END emp_pkg;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

DECLARE
avg_tenure NUMBER;
BEGIN
avg_tenure := emp_pkg.get_average_tenure('AD_VP'); -- Pass the job category as argument
  DBMS_OUTPUT.PUT_LINE('Average Tenure: ' || avg_tenure || ' months');
END;
/

Sample Output:

Statement processed.
Average Tenure: 432.866317204301075268817204301075268817 months

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that find the average tenure of employees belonging to a specific job category.

A function get_average_tenure is declared in this package. It takes a single input parameter job_category, representing the job category for which the average tenure of employees needs to be calculated and it returns a NUMBER.

The three local variables total_tenure, employee_count, and avg_tenure are declared inside the function.

The first query calculates the total_tenure by subtracting the hire date of each employee from the current date and then summing up the tenure for all employees in the specific job category and the second query determines the employee_count by counting the number of employees in the specified job category.

If there are employees in the job category , the function calculates the avg_tenure by dividing the total_tenure by the employee_count.

Finally, the calculated avg_tenure is returned as the result of the function.

Previous: Highest paid employees package in PL/SQL.
Next: Retrieve employees with the same job title and manager package in PL/SQL.