Updating employee salaries based on performance rating

PL/SQL Package: Exercise-9 with Solution

Write a PL/SQL package that contains a procedure to update the salary of an employee based on their performance rating.

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 salary_update_pkg AS
  PROCEDURE update_salary(p_employee_id NUMBER, p_performance_rating NUMBER);
END salary_update_pkg;
/
CREATE OR REPLACE PACKAGE BODY salary_update_pkg AS
  PROCEDURE update_salary(p_employee_id NUMBER, p_performance_rating NUMBER) IS
v_salaryemployees.salary%TYPE;
  BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_employee_id;
    IF p_performance_rating = 1 THEN
v_salary := v_salary * 1.05;
    ELSIF p_performance_rating = 2 THEN
v_salary := v_salary * 1.10;
    ELSIF p_performance_rating = 3 THEN
v_salary := v_salary * 1.15;
    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'Invalid performance rating');
    END IF;
    UPDATE employees
    SET salary = v_salary
    WHERE employee_id = p_employee_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20002, 'Employee not found');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20003, 'Error: ' || SQLERRM);
  END update_salary;
END salary_update_pkg;
/

Sample Output:

Package created.
Package Body created.

Flowchart:

To execute the package:

BEGIN
salary_update_pkg.update_salary(100, 2);
  COMMIT;
END;
/

Sample Output:

Statement processed.

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that provides a procedure update_salary to adjust an employee's salary based on their performance rating.

A procedure update_salary(p_employee_id NUMBER, p_performance_rating NUMBER) are declared in the package.

The procedure takes an employee_id and performance_rating both of type number as inputs to update the employee's salary based on their performance rating.

Inside the procedure a local variable v_salary of the same data type as the salary column in the employees table is declared. The procedure retrieves the current salary of the employee identified by the employee_id using a SELECT query and stores it in the v_salary variable.

The procedure calculates the new salary for the employee and performs the update based on the provided p_performance_rating on the employees table.

If the p_performance_rating does not match any expected values (1, 2, or 3), it raises an application error indicating an "Invalid performance rating."

The procedure also handles exceptions such as NO_DATA_FOUND, which is raised when the employee with the given employee_id is not found.

Previous: Employee count package for job titles.
Next: PL/SQL package - Calculate Bonus Package.