PL/SQL Package: Top n employees with highest salary

PL/SQL Package: Exercise-11 with Solution

Write a PL/SQL package that contains a function to retrieve the top N employees with the highest salaries.

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 TopSalaryPackage IS
  FUNCTION GetTopEmployees(
p_top_count IN NUMBER
  ) RETURN SYS_REFCURSOR;
END TopSalaryPackage;
/
CREATE OR REPLACE PACKAGE BODY TopSalaryPackage IS
  FUNCTION GetTopEmployees(
p_top_count IN NUMBER
  ) RETURN SYS_REFCURSOR IS
v_cursor SYS_REFCURSOR;
  BEGIN
    OPEN v_cursor FOR
      SELECT employee_id, first_name, salary
      FROM (
        SELECT employee_id, first_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
        FROM employees
      )
      WHERE salary_rank<= p_top_count;

    RETURN v_cursor;
  END GetTopEmployees;
END TopSalaryPackage;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

DECLARE
v_top_count NUMBER := 5;
v_employee_id NUMBER;
v_employee_nameVARCHAR2(100);
v_salary NUMBER;
v_cursor SYS_REFCURSOR;
BEGIN
v_cursor := TopSalaryPackage.GetTopEmployees(v_top_count);
  LOOP
    FETCH v_cursor INTO v_employee_id, v_employee_name, v_salary;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
    DBMS_OUTPUT.PUT_LINE('-----------------------');
  END LOOP;
  CLOSE v_cursor;
END;
/

Sample Output:

Statement processed.
Employee ID: 100
Employee Name: Steven 
Salary: 24000
-----------------------
Employee ID: 101
Employee Name: Neena
Salary: 17000
-----------------------
Employee ID: 102
Employee Name: Lex
Salary: 17000
-----------------------
Employee ID: 145
Employee Name: John 
Salary: 14000
-----------------------
Employee ID: 146
Employee Name: Karen 
Salary: 13500
-----------------------

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that retrieves the top N employees with the highest salaries from the employees table by calling the function GetTopEmployees(p_top_count).

A function GetTopEmployees is declared inside the package. It takes a single input parameter p_top_count, which specifies the number of top employees to be retrieved.

The function returns a SYS_REFCURSOR, which is a special type of cursor used to return query results from the function.

A local variable v_cursor of type SYS_REFCURSOR is used to the function to hold the query results.

Within the function, a query is executed to select the employee_id, first_name, and salary from the employees table.

To determine the ranking of employees by salary, the RANK() window function is used in a subquery, ordering the employees in descending order of their salaries .

An additional column salary_rank represents the salary rank of each employee in the subquery result.

The main query then filters the results, returning only those employees whose salary_rank is less than or equal to the input parameter p_top_count.

Finally, the cursor v_cursor is opened for the constructed query, and it is returned as the result of the function.

Previous: Calculate Bonus Package.
Next: Salary cost package in PL/SQL.