PL/SQL code to find highest salary employee in each department

PL/SQL While Loop: Exercise-19 with Solution

Write a PL/SQL program to display the employees who have the highest salary in each department using a nested while loop.

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:

DECLARE
v_dept_idemployees.department_id%TYPE;
v_max_salary NUMBER;
v_employee_idemployees.employee_id%TYPE;
v_cursor     SYS_REFCURSOR;
BEGIN
  OPEN v_cursor FOR
    SELECT DISTINCT department_id
    FROM employees;
  WHILE TRUE LOOP
    FETCH v_cursor INTO v_dept_id;
    EXIT WHEN v_cursor%NOTFOUND;
v_max_salary := 0;
    DECLARE
v_employee_recemployees%ROWTYPE;
v_employee_cursor SYS_REFCURSOR;
    BEGIN
      OPEN v_employee_cursor FOR
        SELECT *
        FROM employees
        WHERE department_id = v_dept_id;
      WHILE TRUE LOOP
        FETCH v_employee_cursor INTO v_employee_rec;
        EXIT WHEN v_employee_cursor%NOTFOUND;
        IF v_employee_rec.salary>v_max_salary THEN
v_max_salary := v_employee_rec.salary;
v_employee_id := v_employee_rec.employee_id;
        END IF;
      END LOOP;
      CLOSE v_employee_cursor;
    END;
DBMS_OUTPUT.PUT_LINE('Department ' || v_dept_id || '   : Employee ID = ' || v_employee_id || ',       Highest Salary = ' || v_max_salary);
  END LOOP;
  CLOSE v_cursor;
END;
/

Sample Output:

Department 50   : Employee ID = 121,       Highest Salary = 8200
Department 40   : Employee ID = 203,       Highest Salary = 6500
Department 110   : Employee ID = 205,       Highest Salary = 12000
Department 90   : Employee ID = 100,       Highest Salary = 24000
Department 30   : Employee ID = 114,       Highest Salary = 11000
Department 70   : Employee ID = 204,       Highest Salary = 10000
Department    : Employee ID = 204,       Highest Salary = 0
Department 10   : Employee ID = 200,       Highest Salary = 4400
Department 20   : Employee ID = 201,       Highest Salary = 13000
Department 60   : Employee ID = 103,       Highest Salary = 9000
Department 100   : Employee ID = 108,       Highest Salary = 12000
Department 80   : Employee ID = 145,       Highest Salary = 14000

Explanation:

The said code in Oracle's PL/SQL that finds the employee with the highest salary in each department.

The variables v_dept_id that stores the department ID, v_max_salary that stores the highest salary found, and v_employee_id that stores the employee ID with the highest salary and a cursor v_cursor that fetches distinct department IDs from the 'employees' table are declared.

A loop defines that continues until all department IDs have been processed and inside the loop, the code fetches the department ID from the cursor into v_dept_id.

To manage the employee records within each department the variable v_employee_rec that stores the employee record, and a cursor v_employee_cursor that fetches employees from the 'employees' table based on the department ID.

An other loop defines that fetches employee records into v_employee_rec from the v_employee_cursor and checks whether the salary of the current employee is higher than the previously stored v_max_salary. If happens true, the v_max_salaryupdates with the new highest salary and v_employee_id with the corresponding employee ID.

The DBMS_OUTPUT.PUT_LINE procedure displays the department ID, the employee ID, and the highest salary.

The outer loop continues until all departments have been processed.

Flowchart:

Previous: PL/SQL Program to Display Average Salary for Each Department.
Next: Find the employee with the lowest salary in each department.