PL/SQL - Object Oriented Programming

Hello there, aspiring programmers! Today, we're going to embark on an exciting journey into the world of Object-Oriented Programming (OOP) in PL/SQL. Don't worry if you're new to programming – I'll be your friendly guide, explaining everything step by step. So, let's dive in!

PL/SQL - Object Oriented

What is Object-Oriented Programming?

Before we start, let's understand what OOP is all about. Imagine you're building a house with Lego blocks. Each block represents an object, and you can combine these blocks in various ways to create more complex structures. That's essentially what OOP does in programming – it allows us to create and manipulate objects that represent real-world entities.

Instantiating an Object

Now, let's learn how to create (or "instantiate") an object in PL/SQL. Think of it as bringing a Lego character to life in your programming world!

CREATE OR REPLACE TYPE person AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  MEMBER FUNCTION say_hello RETURN VARCHAR2
);

CREATE OR REPLACE TYPE BODY person AS
  MEMBER FUNCTION say_hello RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello, my name is ' || name || ' and I am ' || age || ' years old.';
  END;
END;

DECLARE
  john person;
BEGIN
  john := person('John Doe', 30);
  DBMS_OUTPUT.PUT_LINE(john.say_hello());
END;

In this example, we've created a person object type with two attributes (name and age) and a method (say_hello). We then instantiated an object john and called its say_hello method.

Member Methods

Member methods are like special skills that our objects possess. Let's add another method to our person object:

CREATE OR REPLACE TYPE person AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  MEMBER FUNCTION say_hello RETURN VARCHAR2,
  MEMBER PROCEDURE have_birthday
);

CREATE OR REPLACE TYPE BODY person AS
  MEMBER FUNCTION say_hello RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello, my name is ' || name || ' and I am ' || age || ' years old.';
  END;

  MEMBER PROCEDURE have_birthday IS
  BEGIN
    age := age + 1;
    DBMS_OUTPUT.PUT_LINE(name || ' is now ' || age || ' years old.');
  END;
END;

DECLARE
  john person;
BEGIN
  john := person('John Doe', 30);
  john.have_birthday();
  DBMS_OUTPUT.PUT_LINE(john.say_hello());
END;

Here, we've added a have_birthday procedure that increases the person's age by 1. It's like giving our Lego character the ability to celebrate birthdays!

Using Map Method

The MAP method is used for comparing objects. It's like giving our objects a special ID card that helps us sort them. Let's see how it works:

CREATE OR REPLACE TYPE person AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  MAP MEMBER FUNCTION get_id RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY person AS
  MAP MEMBER FUNCTION get_id RETURN NUMBER IS
  BEGIN
    RETURN age;
  END;
END;

DECLARE
  john person := person('John Doe', 30);
  jane person := person('Jane Smith', 25);
BEGIN
  IF john > jane THEN
    DBMS_OUTPUT.PUT_LINE(john.name || ' is older');
  ELSE
    DBMS_OUTPUT.PUT_LINE(jane.name || ' is older');
  END IF;
END;

In this example, we use the age as an ID for comparison. It's like comparing Lego characters based on their height!

Using Order Method

The ORDER method is another way to compare objects, but it gives us more flexibility than the MAP method. It's like having a special judge to compare our Lego characters:

CREATE OR REPLACE TYPE person AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  ORDER MEMBER FUNCTION compare(p person) RETURN INTEGER
);

CREATE OR REPLACE TYPE BODY person AS
  ORDER MEMBER FUNCTION compare(p person) RETURN INTEGER IS
  BEGIN
    IF self.age < p.age THEN
      RETURN -1;
    ELSIF self.age > p.age THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END;
END;

DECLARE
  john person := person('John Doe', 30);
  jane person := person('Jane Smith', 30);
BEGIN
  IF john > jane THEN
    DBMS_OUTPUT.PUT_LINE(john.name || ' is older');
  ELSIF john < jane THEN
    DBMS_OUTPUT.PUT_LINE(jane.name || ' is older');
  ELSE
    DBMS_OUTPUT.PUT_LINE('They are the same age');
  END IF;
END;

This method allows us to define custom comparison logic. It's particularly useful when we want to compare objects based on multiple attributes.

Inheritance for PL/SQL Objects

Inheritance is like creating a family tree for our objects. We can create new object types that inherit properties and methods from existing ones. Let's see how:

CREATE OR REPLACE TYPE employee UNDER person (
  job_title VARCHAR2(50),
  salary NUMBER,
  MEMBER FUNCTION get_annual_salary RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY employee AS
  MEMBER FUNCTION get_annual_salary RETURN NUMBER IS
  BEGIN
    RETURN salary * 12;
  END;
END;

DECLARE
  emp employee := employee('Alice Johnson', 35, 'Manager', 5000);
BEGIN
  DBMS_OUTPUT.PUT_LINE(emp.say_hello());
  DBMS_OUTPUT.PUT_LINE('Annual salary: $' || emp.get_annual_salary());
END;

Here, employee is a child of person, inheriting its attributes and methods while adding its own. It's like creating a special type of Lego character that can do everything a regular character can, plus more!

Abstract Objects in PL/SQL

Abstract objects are like blueprints for other objects. They define a structure but can't be instantiated directly. Let's create an abstract object:

CREATE OR REPLACE TYPE shape AS OBJECT (
  name VARCHAR2(50),
  MEMBER FUNCTION get_area RETURN NUMBER
) NOT INSTANTIABLE NOT FINAL;

CREATE OR REPLACE TYPE circle UNDER shape (
  radius NUMBER,
  OVERRIDING MEMBER FUNCTION get_area RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY circle AS
  OVERRIDING MEMBER FUNCTION get_area RETURN NUMBER IS
  BEGIN
    RETURN 3.14159 * radius * radius;
  END;
END;

DECLARE
  c circle := circle('My Circle', 5);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Area of ' || c.name || ': ' || c.get_area());
END;

In this example, shape is an abstract object that defines a common structure for all shapes. circle is a concrete implementation of shape. It's like having a general Lego instruction manual (the abstract object) and then creating specific Lego models based on it!

And there you have it! We've covered the basics of Object-Oriented Programming in PL/SQL. Remember, practice makes perfect, so don't be afraid to experiment with these concepts. Happy coding!

Method	Description
Constructor	Creates and initializes an object
Member Function	Returns a value and can be used in SQL statements
Member Procedure	Performs an action but doesn't return a value
MAP Method	Used for comparing objects, based on a single value
ORDER Method	Used for comparing objects, allows custom comparison logic
FINAL Method	Cannot be overridden in a subtype
NOT INSTANTIABLE Method	Must be overridden in a subtype before objects can be instantiated

Credits: Image by storyset

Previous Tutorial:

PL/SQL - DBMS Output