PL/SQL package - Calculate Bonus Package

PL/SQL Package: Exercise-10 with Solution

Write a PL/SQL package that contains a function to calculate the bonus amount for employees based on their salary and years of service.

Sample Solution:

PL/SQL Code:

CREATE OR REPLACE PACKAGE BonusPackage IS
  FUNCTION CalculateBonus(
p_salary IN NUMBER,
p_years_of_service IN NUMBER
  ) RETURN NUMBER;
END BonusPackage;
/
CREATE OR REPLACE PACKAGE BODY BonusPackage IS
  FUNCTION CalculateBonus(
p_salary IN NUMBER,
p_years_of_service IN NUMBER
  ) RETURN NUMBER IS
v_bonus NUMBER;
  BEGIN
    IF p_years_of_service>= 5 THEN
v_bonus := p_salary * 0.1; -- 10% bonus
    ELSE
v_bonus := p_salary * 0.05; -- 5% bonus
    END IF;
    RETURN v_bonus;
  END CalculateBonus;
END BonusPackage;
/

Sample Output:

Statement processed.
Package Body created.

Flowchart:

To execute the package:

DECLARE
v_salary NUMBER := 5000;
v_years_of_service NUMBER := 7;
v_bonus_amount NUMBER;
BEGIN
v_bonus_amount := BonusPackage.CalculateBonus(v_salary, v_years_of_service);
  DBMS_OUTPUT.PUT_LINE('Bonus amount: ' || v_bonus_amount);
END;
/

Sample Output:

Statement processed.
Bonus amount: 500

Flowchart:

Explanation:

The said code in Oracle's PL/SQL package that calculate bonuses for employees based on their salary and years of service.

A function CalculateBonus with two input parameters p_salary and p_years_of_service is declared in this package. The function is defined to return a NUMBER, representing the bonus amount.

The CalculateBonus function calculates the bonus based on the given inputs, determining whether the employee is eligible for a 10% bonus if their years of service are 5 or more or a 5% bonus if their years of service are less than 5. The calculated bonus amount is stored in a local variable v_bonus, and then it is returned as the function's result.

Previous: Updating employee salaries based on performance rating.
Next:Top n employees with highest salary.