Object Types and Inheritance

In my first post on object-oriented programming in PL/SQL, I introduced the object type (our version of a class) and showed how you needed to instantiate an object type instance before you could work with it.

In this post, I explore how to create a hierarchy of types. Type or class hierarchies are a key concept in object-oriented programming, because of the way that subtypes inherit attributes and methods from their parent types. All the code you see below can be run in Oracle LiveSQL through this script.

Let's revisit the root type of my hierarchy: food.

CREATE TYPE food_ot AS OBJECT (
   name        VARCHAR2(100),
   food_group  VARCHAR2 (50),
   grown_in    VARCHAR2 (100)
   )
   NOT FINAL
   ;

A very simple type: it contains just three attributes and no methods (procedures or functions). It also contains the NOT FINAL clause. This means, rather obviously, that I am "not done." What that means in the context of object types is that I might want to create a subtype of this type.

I don't have to do so. I can mark it as NOT FINAL and never create a subtype. But since this is a blog post about inheritance, let's do it!

If food is the most general type of, well, food, what would a more specific type of food be? If you, like me, have a ferocious sweet tooth, you will know very well that desserts are a type of food. And specifically:

Every dessert is a food, but not every item of food is a dessert.

Brussels sprouts, for example, are not a dessert - well, at least in my experience and according to my palette. I suppose there may be a truly sprouts-crazy culture (or parallel universe) in which you eat all sorts of sprouts for every phase of a meal, and Brussels sprouts are a highly acclaimed dessert.

However, in this universe, on this planet, in this blog post, no.

Cakes, pies, ice cream, fruits, cookies, candies, sorbets, donuts....now, those are desserts, according to my way of thinking. And every single one of them are also food. That means that dessert is a subtype of food. So let's create a dessert object type.

CREATE TYPE dessert_t UNDER food_ot (
   contains_chocolate    CHAR(1),
   year_created          NUMBER(4)
   )
   NOT FINAL
   ;

Notice the UNDER food_ot syntax in place of the IS OBJECT. Since I am creating a new type "under" food, it clearly is an object type, so that verbiage is unnecessary and redundant and not needed. Get it? :-)

Before going any further, let's use this type.

DECLARE
   l_cherry_pie   dessert_ot
                     := dessert_ot ('Cherry Pie',
                                    'Baked Goods',
                                    'Oven',
                                    'N',
                                    1492);
BEGIN
   DBMS_OUTPUT.put_line (l_cherry_pie.name);
   DBMS_OUTPUT.put_line (l_cherry_pie.year_created);
END;
/

Cherry Pie
1492

[Confession: I don't really know when the first cherry pie was made.]

Now, if you are familiar with object-oriented principles, this will come as no surprise. But if you are used to thinking in terms of tables and packages, say, what you see might be a real shock.

The dessert type is declared with just two attributes; contains_chocolate and year_created. So how is it possible that I supply five values in the call to its constructor function and can display the value for name?

The dessert instance inherited the name, food_group and grown_in attributes from its parent! They are automatically available in the constructor function (dessert's attributes are appended to the end of the list of food's attributes).

And since the dessert type was also defined as NOT FINAL, I can keep going. Desserts are a fairly
specific subset of foods, but then of course there are many different types of dessert. And so, I hereby give you....cakes!

CREATE TYPE cake_ot UNDER dessert_ot (
   diameter NUMBER,
   inscription VARCHAR2 (200)
   );
/

DECLARE
   l_yummy   cake_ot
                := cake_ot ('Marzepan Delight',
                           'Baked Goods',
                           'Oven',
                           'N',
                           1634,
                           8,
                           'Happy Birthday!');
BEGIN
    DBMS_OUTPUT.put_line (
      'Nothing says ' || l_yummy.inscription || ' like ' || l_yummy.name);
END;
/

Nothing says Happy Birthday! like Marzepan Delight

Hopefully you can how powerful and useful inheritance is. Without it, you would have to duplicate attributes all the way down the hierarchy. And it's not just attributes. You also inherit methods - the procedures and functions defined in super types.

My next post in the series will dive into methods, but let's start with a simple example to demonstrate inheritance right now.

Inheritance and methods

Lots of people grow their own food, but most people buy it. So let's add a method to calculate and return the price of any instance of food. Since I am returning a value, I will build a price function. Since it returns a value for an instance, it is a member method (as opposed to a static method, a distinction that will be explored in the next post).

Now, with a type as generic as food, and with so few attributes, it's hard to come up with any sort of sophisticated pricing model. Given that situation, I will implement this rule for price:

Add the number of characters in the name, food group and grown in attributes and multiply by 10.

Before I show you the new versions of the code, I must point out that since I have already created a hierarchy of two types in my schema, the dessert_ot type is dependent on the food_ot type. This means that I will not be able to "create or replace" the food type until I drop the dessert type, with:

DROP TYPE dessert_ot FORCE
/

OK, now I can add the method to my type specification:

CREATE OR REPLACE TYPE food_ot AS OBJECT (
   name VARCHAR2 (100),
   food_group VARCHAR2 (50),
   grown_in VARCHAR2 (100),
   MEMBER FUNCTION pricevRETURN NUMBER
)
   NOT FINAL;
/

The specification now includes the header for the price function. By using the "MEMBER" keyword, I indicate that this function is executed "for" a specific instance of this object type, following the syntax:

object_type.member_name

Next, my type body:

CREATE OR REPLACE TYPE BODY food_ot
IS
   MEMBER FUNCTION price
      RETURN NUMBER
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Food price!');

      RETURN   (  LENGTH (self.name)
                + LENGTH (self.food_group)
                + LENGTH (grown_in))
             * 10;
   END;
END;
/

Notice the use of the SELF keyword. This is how you say "Perform specified operation on the instance to which this subprogram is attached." In this case, I sum the lengths (number of characters) of the three attributes and multiply by 10.

And now I will invoke the method using dot notation as shown above:

DECLARE
   l_food      food_ot := food_ot ('Ramen Noodle Soup', 'Salt', 'Microwave');
BEGIN
   DBMS_OUTPUT.put_line ('What do we see?');
   DBMS_OUTPUT.put_line (
      'The price of ' || l_food.name || ' = ' || l_food.price());
END;
/

What do we see?
Food price!
The price of Ramen Noodle Soup = 300

Now it is time to revisit the dessert type - this is, after all, a post about inheritance. When I create a subtype of a type that has methods, I can either inherit the supertype method or override it.

First, let's go with the inherit approach and see how that works.

CREATE OR REPLACE TYPE dessert_ot UNDER food_ot (
   contains_chocolate CHAR (1),
   year_created NUMBER (4)
   )
   NOT FINAL;
/

Since I inherit, the dessert type looks the same as it did before. And just as I could reference attributes of the supertype in the instance of the subtype, I can do the same thing with methods:<

DECLARE
   /* M is for Maybe */
   l_dessert   dessert_ot
                  := dessert_ot ('Ice Cream',
                                 'Sugar',
                                 'Cow',
                                 'M',
                                 200);
BEGIN
   DBMS_OUTPUT.put_line ('What do we see?');
   DBMS_OUTPUT.put_line (
      'The price of ' || l_dessert.name || ' = ' || l_dessert.price());
END;
/

What do we see?
Food price!
The price of Ice Cream = 170

But what if I have a completely different calculation for the price of a dessert? After all - it might contain chocolate, which is worth a pretty penny, indeed!

In this case, I will override the parent method, replacing it completely with my new algorithm, influenced by the presence of chocolate and how long ago the dessert was created. Notice the use of the OVERRIDING keyword before MEMBER in the code below. It means exactly what it says. :-)

CREATE TYPE dessert_ot UNDER food_ot (
   contains_chocolate   CHAR (1)
 , year_created         NUMBER (4)
 , OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
)
NOT FINAL;
/

CREATE OR REPLACE TYPE BODY dessert_ot
IS
   OVERRIDING MEMBER FUNCTION price
      RETURN NUMBER
   IS
      multiplier   NUMBER := 1;
   BEGIN
      DBMS_OUTPUT.put_line ('Dessert price!');

      IF SELF.contains_chocolate = 'Y'
      THEN
         multiplier := 2;
      END IF;

      IF SELF.year_created < 1900
      THEN
         multiplier := multiplier + 0.5;
      END IF;

      RETURN (10.00 * multiplier);
   END;
END;
/

Now let's see how the price methods work for both food and dessert.

DECLARE
   l_food food_ot := food_ot ('Ramen Noodle Soup', 'Salt', 'Microwave');
   
   /* M is for Maybe */
   l_dessert   dessert_ot
                  := dessert_ot ('Ice Cream',
                                 'Sugar',
                                 'Cow',
                                 'M',
                                 200);
BEGIN
   DBMS_OUTPUT.put_line ('What do we see?');
   DBMS_OUTPUT.put_line (
      'The price of ' || l_food.name || ' = ' || l_food.price());
   DBMS_OUTPUT.put_line (
      'The price of ' || l_dessert.name || ' = ' || l_dessert.price());
END;
/

What do we see?
Food price!
The price of Ramen Noodle Soup = 300
Dessert price!
The price of Ice Cream = 15

As you can see, the different price functions for each of the types were properly invoked.

And the price of ice cream has plummeted!

Inheritance and constructors

In my first post on object types, I introduced constructors, which are used to initialize an instance of an object type. And which I used above in this post. You might then wonder: can constructors be inherited?

The answer is no.

Constructors are tied closely to the object type itself. The default constructor must accept a value for each attribute of that type and all its parent types up the hierarchy.

In that sense, it is (to coin a phrase from Richard Martens) the "signature of the object type".

You can create your own constructor that reduces the number of attributes that must be supplied to the function, but that special type of method also will not be inherited. It must, after all, have the name of that type, not the subtype from which it might be inherited.

What will you do with your inheritance?

There you have it: inheritance of attributes and methods. A very powerful feature of object-oriented development, and a big part of Oracle object types.

And now that I've introduced methods, my next post in the series will dive into that topic more deeply, and touch on:

• Member methods
• Static methods
• Non-instantiable methods

It's gonna be fun, don't miss it! :-)

Check Out the Series

Visit this post that gives you quick access to all the articles in the series.

P.S. Don't forget that if you'd like to try out all this code for yourself, all you have to do is run this LiveSQL script.