Highest paid employees package in PL/SQL

PL/SQL Package: Exercise-15 with Solution

Write a code in PL/SQL that create a package that contains a function to retrieve the highest paid employee in each 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 emp_pkg IS
  TYPE employee_record_type IS RECORD (
department_idemployees.department_id%TYPE,
employee_idemployees.employee_id%TYPE,
first_nameemployees.first_name%TYPE,
last_nameemployees.last_name%TYPE,
salaryemployees.salary%TYPE
  );
  TYPE employee_report_type IS TABLE OF employee_record_type;
  FUNCTION get_highest_paid_employees RETURN employee_report_type;
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  FUNCTION get_highest_paid_employees RETURN employee_report_type IS
highest_paid_employeesemployee_report_type;
  BEGIN
    SELECT department_id, employee_id, first_name, last_name, salary
    BULK COLLECT INTO highest_paid_employees
    FROM (
      SELECT e.department_id, e.employee_id, e.first_name, e.last_name, e.salary,
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS ranking
      FROM employees e
    )
    WHERE ranking = 1;
    RETURN highest_paid_employees;
  END get_highest_paid_employees;
END emp_pkg;
/

Sample Output:

Package created.
Package Body created

Flowchart:

To execute the package:

DECLARE
highest_paid_employeesemp_pkg.employee_report_type;
BEGIN
highest_paid_employees := emp_pkg.get_highest_paid_employees;
  FOR i IN 1..highest_paid_employees.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Department ID: ' || highest_paid_employees(i).department_id);
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || highest_paid_employees(i).employee_id);
    DBMS_OUTPUT.PUT_LINE('Name: ' || highest_paid_employees(i).first_name || ' ' || highest_paid_employees(i).last_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || highest_paid_employees(i).salary);
    DBMS_OUTPUT.PUT_LINE('----------------------------------');
  END LOOP;
END;
/

Sample Output:

statement processed.
Department ID: 10
Employee ID: 200
Name: Jennifer Whalen 
Salary: 4400
----------------------------------
Department ID: 20
Employee ID: 201
Name: Michael Hartstein
Salary: 13000
----------------------------------
Department ID: 30
Employee ID: 114
Name: Den Raphaely
Salary: 11000
----------------------------------
Department ID: 40
Employee ID: 203
Name: Susan Mavris
Salary: 6500
----------------------------------
.....

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that retrieve highest paid employee in each department and returns the information in a collection of employee records, where each record contains the department_id, employee_id, name, and salary of the highest paid employee in the respective department.

A function get_highest_paid_employees are declared in this package with no input parameters and it returns a collection of employee records representing the highest paid employees in each department.

Inside the function, one local variable highest_paid_employees of type employee_report_type is declared.

The function uses a SQL query with the RANK() window function to rank employees by salary within each department.

The query selects the department_id, employee_id, first_name, last_name, and salary from the "employees" table and assigns a ranking to each employee based on their salary within their respective department.

The outer query filters the results to include only those employees with a ranking of 1, which represents the highest-paid employee in each department.

Finally, the collection highest_paid_employees containing the highest paid employees from each department is returned as the result of the function.

Previous: Package to retrieve employees with the same manager in PL/SQL.
Next: Average tenure of employees by job category package in PL/SQL.