Skip to main content

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.

Comments

  1. https://en.wikipedia.org/wiki/Circle-ellipse_problem

    ReplyDelete
  2. Can't we add method to parent type without dropping child type using ALTER TYPE? Type evolution must have some use :)

    ReplyDelete
    Replies
    1. I confess that I have not spent lots of time with type evolution features and limitations. I will be covering that in a future post and then hopefully will become an overnight expert. :-)

      In the meantime, have you checked the documentation and tried adding the method?

      https://stevenfeuersteinonplsql.blogspot.com/2019/08/plsql-101-inheritance-and-object-types.html

      Feel free to post a script to LiveSQL demonstrating issues and send me the link.

      Delete
  3. Hello All,

    You can add a method to a parent type without dropping subtype(s),
    for example as follows:

    ALTER TYPE food_ot
    ADD MEMBER FUNCTION price RETURN NUMBER
    CASCADE
    /

    ALTER TYPE dessert_ot
    ADD OVERRIDING MEMBER FUNCTION price
    RETURN NUMBER
    CASCADE
    /


    But, if it happens that you need to DROP a method, sometimes you cannot do this with CASCADE, but instead you can only use INVALIDATE to avoid an error.
    It depends on the order in which the operations are performed.

    These options are indeed less frequently used and discussed,
    but it is useful to know that they exist :)

    Cheers & Best Regards,
    Iudith

    ReplyDelete
  4. Hi!
    I tried using type evolution but there are 2 limitations:
    1) In some scenarios using evolved type causes ORA-600. I had to use "alter type reset" to fix it.
    2) Type evolution is not compatible with Edition Based Redefinition.
    But I just found we can use "create type force". So we are not doomed to drop all dependent types when adding attribute to type :) Except types in table columns :)

    ReplyDelete
    Replies
    1. Thanks for sharing your experience, and I am glad you found a way forward! :-)

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel