Substituting and Converting Object Types in a Hierarchyby Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition
Editor's note: In Part 2 in this series on new features in Oracle 9i, Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition, explores the advantages and flexibilities of object-type hierarchies by examining substitutability and type conversion. (For a quick review of object types and how you can build object-type hierarchies by taking advantage of inheritance, see Steven's first article, Inherit the Database: Oracle9i's Support for Object Type Inheritance.) Beware, however, that by the time you've finished reading this article, you may find yourself to be a bit hungry.
In Part 1 in this series, I introduced one of Oracle9i's most significant enhancements to the SQL and PL/SQL language: support for object-type inheritance. With inheritance, a subtype inherits all of the attributes and methods from their supertypes, and not just the immediate subtype, but any subtype or ancestor in the resulting object-type hierarchy. Inheritance allows you to implement business logic at low levels in the hierarchy and then make them automatically available in all object types derived from those supertypes. You don't have to code that business rule multiple times to make it available in the different object types in the hierarchy.
Inheritance also allows developers to take advantage of "dynamic polymorphism," which means that at the time your code is run, Oracle identifies and executes the "nearest" or most specific method in the object hierarchy that corresponds to your method invocation.
What Is Substitutability?
When you define a type hierarchy, you start with a root type, from which all other
subtypes are derived. In the Java language, for example, all classes (roughly equivalent to
Oracle "object types") derive from the root
Object class. In Oracle, where the object model has been layered on top of a relational database, there's no built-in and global hierarchy. So every time you work with object types, you define your own root.
For this article, we'll work with a very simple type hierarchy. In this
hierarchy, the food type,
food_t, is the root. The dessert type,
dessert_t, is a subtype of food, and cake, represented by
cake_t, is a further subtype of
dessert_t. Here are the definitions of these types (showing attributes only, and no associated PL/SQL methods):
CREATE TYPE food_t AS OBJECT ( name VARCHAR2(100), food_group VARCHAR2 (100), grown_in VARCHAR2 (100) ) NOT FINAL ; / CREATE TYPE dessert_t UNDER food_t ( contains_chocolate CHAR(1), year_created NUMBER(4) ) NOT FINAL ; / CREATE TYPE cake_t UNDER dessert_t ( diameter NUMBER, inscription VARCHAR2(200) ) ; /
Each type has its own type-specific attributes. Each subtype, don't forget, also inherits the attributes of its supertype(s). So if I want to instantiate an object of type cake, I need to supply a total of seven attributes, as shown here:
DECLARE my_favorite cake_t := cake_t ( 'Marzepan Delight', 'CARBOHYDRATE', 'Swedish Bakery', 'N', 1634, 8, 'Happy Birthday!' ); BEGIN DBMS_OUTPUT.put_line (my_favorite.NAME); DBMS_OUTPUT.put_line (my_favorite.inscription); END; /
Notice that I reference and display an attribute from the base food type and an attribute from the cake subtype. They're all equally available to me in an object instantiated from cake.
The way to think about such a hierarchy is this: A cake is a type of dessert, which in turn is a type of food. But not all desserts are cake, and not all foods are dessert (putting aside the obvious cultural complications here, for instance, something that's not considered a dessert in the United States may well be considered one in, say, Ecuador). Any characteristic of food applies to cakes, but not all characteristics of a cake will necessarily make sense for a food, such as a cucumber.
Once you've defined your hierarchy, you'll want to work with and make changes to the types in that hierarchy. In some cases, you may wish to select and view all types across the entire hierarchy. In other cases, you may only want to update a specific level in the hierarchy, such as all cakes. And then there are situations where you'll want to work with, say, all desserts that are not cakes. And that leads us directly to the concept of substitutability.
A supertype is substitutable if one of its subtypes can substitute for it in some location, such as in a column or in a program variable, in which the declared type is the supertype (and not that particular supertype).