Employee count package for job titles

PL/SQL Package: Exercise-8 with Solution

Write a PL/SQL package that contains a function to retrieve the total number of employees in a specific job title.

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 employee_count_pkg AS
  FUNCTION get_employee_count(p_job_title VARCHAR2) RETURN NUMBER;
END employee_count_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_count_pkg AS
  FUNCTION get_employee_count(p_job_title VARCHAR2) RETURN NUMBER IS
v_employee_count NUMBER := 0;
  BEGIN
    SELECT COUNT(*) INTO v_employee_count
    FROM employees
    WHERE job_id = p_job_title;
    RETURN v_employee_count;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END get_employee_count;
END employee_count_pkg;
/

Sample Output:

Package created.
Package Body created

Flowchart:

To execute the package:

SELECT employee_count_pkg.get_employee_count('SA_MAN') AS total_count FROM dual;

Sample Output:

TOTAL_COUNT
-----------------------
5

Explanation:

The said code in Oracle's PL/SQL package that provides a function get_employee_count that takes a job title as input and returns the number of employees who hold that specific job title.

A function get_employee_count(p_job_title VARCHAR2) declared that takes a job title as input and returns a NUMBER as output.

Inside the function a local variable v_employee_count of type NUMBER and initializes it to 0 is declared.

The function then performs a SELECT query on the employees table to count the number of rows where the job_id matches the input p_job_title. The result is stored in the v_employee_count variable. The function returns the v_employee_count if the query is successful and if any exception occurs during the query execution, it catches the exception with the WHEN OTHERS block and returns NULL.

Previous: Calculate_avg_salary.
Next: Updating employee salaries based on performance rating.