Creating a Subclass "Manager" in PL/SQL Inheriting from "Employee" Class

PL/SQL OOP: Exercise-4 with Solution

Write a PL/SQL code to create a "Manager" subclass inheriting from the "Employee" class, and add an attribute to store the number of employees managed.

A subclasses "Manager" subclass that inherits from the "Employee" class and add an additional attribute to store the number of employees managed by the manager.

Sample Solution:

PL/SQL Code:

-- Create the base "Employee" class
CREATE OR REPLACE TYPE Employee AS OBJECT (
emp_id NUMBER, -- Employee ID attribute
emp_nameVARCHAR2(100), -- Employee name attribute
salary NUMBER, -- Salary attribute

    -- Method to calculate annual bonus
    MEMBER FUNCTION calculate_bonus RETURN NUMBER -- Method to calculate bonus
);
/

Create the body of the "Employee" class

PL/SQL Code:

CREATE OR REPLACE TYPE BODY Employee AS
    -- Method implementation to calculate the annual bonus
    MEMBER FUNCTION calculate_bonus RETURN NUMBER IS
bonus_percentage NUMBER; -- Percentage of bonus
bonus_amount NUMBER; -- Calculated bonus amount
    BEGIN
        -- Determine the bonus percentage based on salary
        IF self.salary< 50000 THEN
bonus_percentage := 0.1; -- 10% bonus for salaries below 50000
        ELSIF self.salary< 100000 THEN
bonus_percentage := 0.15; -- 15% bonus for salaries between 50000 and 100000
        ELSE
bonus_percentage := 0.2; -- 20% bonus for salaries above 100000
        END IF;

        -- Calculate the bonus amount
bonus_amount := self.salary * bonus_percentage;

        -- Return the calculated bonus
        RETURN bonus_amount;
    END;
END;
/

Create the "Manager" class with additional attribute and method

PL/SQL Code:

-- Create the "Manager" class with additional attribute and method
CREATE OR REPLACE TYPE Manager AS OBJECT (
emp_id NUMBER, -- Employee ID
emp_nameVARCHAR2(100), -- Employee Name
salary NUMBER, -- Salary
employees_managed NUMBER, -- Additional attribute for the "Manager" class

    -- Constructor for the "Manager" class
    CONSTRUCTOR FUNCTION Manager(
emp_id NUMBER,
emp_name VARCHAR2,
salary NUMBER,
employees_managed NUMBER
    ) RETURN SELF AS RESULT,

    -- Method to calculate bonus for managers (additional method for "Manager" class)
    MEMBER FUNCTION calculate_bonus RETURN NUMBER
);
/

Create the body of the "Manager" class

PL/SQL Code:

-- Create the body of the "Manager" class
CREATE OR REPLACE TYPE BODY Manager AS
    -- Constructor implementation for the "Manager" class
    CONSTRUCTOR FUNCTION Manager(
emp_id NUMBER,
emp_name VARCHAR2,
salary NUMBER,
employees_managed NUMBER
    ) RETURN SELF AS RESULT IS
    BEGIN
SELF.emp_id := emp_id; -- Set Employee ID
SELF.emp_name := emp_name; -- Set Employee Name
SELF.salary := salary; -- Set Salary
SELF.employees_managed := employees_managed; -- Set Number of Employees Managed
        RETURN; -- Return the created object
    END;

    -- Method implementation to calculate the annual bonus for managers
    MEMBER FUNCTION calculate_bonus RETURN NUMBER IS
bonus_percentage NUMBER; -- Bonus percentage based on salary
bonus_amount NUMBER; -- Calculated bonus amount
    BEGIN
        -- Determine the bonus percentage based on salary and number of employees managed
        IF self.salary< 50000 THEN
bonus_percentage := 0.1; -- 10% bonus for salaries below 50000
        ELSIF self.salary< 100000 THEN
bonus_percentage := 0.15; -- 15% bonus for salaries between 50000 and 100000
        ELSE
bonus_percentage := 0.2; -- 20% bonus for salaries above 100000
        END IF;

        -- Calculate the bonus amount including a bonus based on employees managed
bonus_amount := (self.salary + self.employees_managed * 1000) * bonus_percentage;

        -- Return the calculated bonus
        RETURN bonus_amount;
    END;
END;
/

To get the result for this code, createsan instances of both the "Employee" and "Manager" classes and call the calculate_bonus method for each of them.

PL/SQL Code:

-- Create an instance of the "Employee" class
DECLARE
emp_obj Employee; -- Declare an Employee object
emp_bonus NUMBER; -- Variable to store bonus
BEGIN
emp_obj := Employee(1, 'John Doe', 75000); -- Instantiate Employee object with ID, Name, and Salary
emp_bonus := emp_obj.calculate_bonus; -- Calculate bonus using the method
    DBMS_OUTPUT.PUT_LINE('Employee Bonus: ' || emp_bonus); -- Display the result
END;
/

-- Create an instance of the "Manager" class
DECLARE
manager_obj Manager; -- Declare a Manager object
manager_bonus NUMBER; -- Variable to store bonus
BEGIN
manager_obj := Manager(2, 'Jane Smith', 90000, 10); -- Instantiate Manager object with ID, Name, Salary, and Employees Managed
manager_bonus := manager_obj.calculate_bonus; -- Calculate bonus using the method
    DBMS_OUTPUT.PUT_LINE('Manager Bonus: ' || manager_bonus); -- Display the result
END;
/

Sample Output:

Statement processed.
Employee Bonus: 11250
Statement processed.
Manager Bonus: 15000

Flowchart:

Explanation:

In this example, the Employee class is subclassed by the Manager class. The Manager class, which implements specialized behavior while still inheriting the Employee class's functionality.

Previous: Method to calculate annual bonus based on salary in PL/SQL's "Employee" Class.
Next: Creating a "Shape" Class in PL/SQL with Area Calculation Method.