Types & Subtypes
Object Types and Subtypes
This article teaches you how to use subtypes or subclasses. You can define an object type with or without dependencies. Object types can have two types of dependencies. The simplest case occurs when you define an object attribute with an object type instead of a data type. The more complex case occurs when you define an object subtype because it inherits the behavior of the base object type. The base object type is a superclass and a parent class. The subtype is a subclass and a child class.
The ability to capture various result sets is a key use case for object types and subtypes. That’s because you can define a table’s column with the object type, and then you can store in that column the object type or any of its subtypes.
A base object type should contain a unique identifier and an object name. The Object Types & Bodies Basic article explains the best practice for unique identifiers. It suggests that you populate the unique ID value with a no argument constructor function. The object name attribute should hold the object type name.
I’d like to suggest we consider base_t as the name of our superclass. You can define a base_t object type like this:
SQL> CREATE OR REPLACE 2 TYPE base_t IS OBJECT 3 ( obj_id NUMBER 4 , obj_name VARCHAR2(30) 5 , CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT 6 , MEMBER FUNCTION to_string RETURN VARCHAR2) 7 INSTANTIABLE NOT FINAL; 8 / |
Line 2 and 3 define two attributes. They are the unique identifier, or ID, and the object. The no argument constructor function assigns values to the obj_id and obj_name attributes. It assigns the base_t_s sequence value to the obj_id attribute and it assigns a string literal to the obj_name attribute. The to_string member function returns a concatenated string of the obj_id and obj_name values. The return value of the to_string function is what you want to disclose about the contents of an object type.
Line 7 declares the class as instantiable and not final. You can create an instance of a class when its instantiable, and you can create subtypes of a type when it’s NOT FINAL.
You need to create the base_t_s sequence before we can compile the base_t object body. The following statement creates the base_t_s sequence as a set of values starting at 1:
SQL> CREATE SEQUENCE base_t_s; |
The object body for the base_t object type is:
SQL> CREATE OR REPLACE 2 TYPE BODY base_t IS 3 4 /* Default constructor. */ 5 CONSTRUCTOR FUNCTION base_t RETURN SELF AS RESULT IS 6 BEGIN 7 /* Assign a sequence value and string literal 8 to the instance. */ 9 self.obj_id := base_t_s.NEXTVAL; 10 self.obj_name := 'BASE_T'; 11 RETURN; 12 END; 13 14 /* A text output function. */ 15 MEMBER FUNCTION to_string RETURN VARCHAR2 IS 16 BEGIN 17 RETURN 'UID#: ['||obj_id||']'||CHR(10) 18 || 'Type: ['||obj_name||']'; 19 END; 20 / |
Line 9 assigns a base_t_s sequence value to the obj_id attribute, which serves as a unique identifier. Line 10 assigns a string literal to the obj_name attribute. The obj_name attribute identifies the object type. Line 17 and 18 prints the contents of the base_t object type as a two-row string.
You can test the construction of the base_t object type with this query:
SQL> SELECT base_t() FROM dual; |
It displays:
BASE_T()(OBJ_ID, OBJ_NAME) ---------------------------- BASE_T(1, 'BASE_T') |
Alternatively, you can test the to_string member function with the TREAT function, like:
SQL> SELECT TREAT(base_t() AS base_t).to_string() AS "Text" 2 FROM dual; |
It displays:
Text ---------------- UID#: [2] Type: [BASE_T] |
Alternatively, you can test to_string member function with an anonymous block (by enabling SERVEROUTPUT):
SQL> SET SERVEROUTPUT ON SIZE UNLIMITED SQL> BEGIN 2 dbms_output.put_line(base_t().to_string); 3 END; 4 / |
It displays:
Text ---------------- UID#: [2] Type: [BASE_T] |
There’s another way to query the object instance with a query. While I don’t think it’s effective for this situation, you should know how the syntax works. It requires that you create a collection of the base_t object type, which you can do with this syntax:
SQL> CREATE OR REPLACE 2 TYPE base_t_tab IS TABLE OF base_t; 3 / |
It displays:
Text ---------------- UID#: [2] Type: [BASE_T] |
You can query the base_t object type from inside a collection by using the CAST and COLLECT functions. The COLLECT function puts a single object instance into a base_t_tab collection. The CAST function puts the generic collection into a specific collection.
The syntax to perform this operation is:
SQL> COLUMN obj_id FORMAT 9999 SQL> COLUMN obj_name FORMAT A20 SQL> SELECT * 2 FROM TABLE(SELECT CAST(COLLECT(base_t()) AS base_t_tab) 3 FROM dual); |
Assuming the base_t_s sequence holds a current value of 3, the query returns:
OBJ_ID OBJ_NAME ------ -------------------- 5 BASE_T |
This type of query isn’t too useful in day-to-day programming. It’s more of a corner use case for testing an object type with a sequence value. While you expect an obj_id value of 4, the query returns a value of 5. Somewhere in the execution Oracle appears to call the sequence twice.
The COLLECT and TREAT functions increment the value of sequence when you put them inside object types. So, you shouldn’t use a sequence as a unique identifier inside an object type. I plan to cover the better approach in subsequent article.
Now that you have a solid base_t object, let’s create a hobbit_t subtype. The hobbit_t subtype adds one attribute to the two attributes in the base_t object type.
The following declares the hobbit_t object type as a subtype and overrides the to_string member function:
SQL> CREATE OR REPLACE 2 TYPE hobbit_t UNDER base_t 3 ( hobbit_name VARCHAR2(30) 4 , CONSTRUCTOR FUNCTION hobbit_t 5 ( hobbit_name VARCHAR2 ) RETURN SELF AS RESULT 6 , OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2) 7 INSTANTIABLE NOT FINAL; 8 / |
Assuming the base_t_s sequence holds a current value of 3, the query returns:
OBJ_ID OBJ_NAME ------ -------------------- 5 BASE_T |
Line 2 declares the hobbit_t subtype as UNDER the base_t object type. There isn’t a no argument constructor that mirrors the parent base_t object type. You also can’t call the parent type’s constructor like they do in Java.
Line 4 and 5 declare a single argument constructor. The hobbit_t object type’s constructor assigns values to the obj_id and obj_name attributes. More or less it performs the same function as its parent’s constructor. Then, the constructor assigns the parameter value to the hobbit_name attribute of the hobbit_t object type.
Line 6 declares an overriding to_string member function. The overriding to_string member function replaces the behavior of our parent class. It provides the subclass with its own a specialized behavior.
You implement the hobbit_t object type like this:
SQL> CREATE OR REPLACE 2 TYPE BODY hobbit_t IS 3 4 /* One argument constructor. */ 5 CONSTRUCTOR FUNCTION hobbit_t 6 ( hobbit_name VARCHAR2 ) RETURN SELF AS RESULT IS 7 BEGIN 8 /* Assign a sequence value and string literal 9 to the instance. */ 10 self.obj_id := base_t_s.NEXTVAL; 11 self.obj_name := 'HOBBIT_T'; 12 13 /* Assign a parameter to the subtype only attribute. */ 14 self.hobbit_name := hobbit_name; 15 RETURN; 16 END; 17 18 /* An output function. */ 19 OVERRIDING MEMBER FUNCTION to_string RETURN VARCHAR2 IS 20 BEGIN 21 RETURN (self AS base_t).to_string||CHR(10) 22 || 'Name: ['||hobbit_name||']'; 23 END; 24 END; 25 / |
Lines 10 assigns a sequence value to the obj_id attribute. Line 11 assigns a string literal to the obj_name attribute. Line 14 assigns the parameter value of the constructor to the hobbit_name attribute of the hobbit_t subtype. Line 21 is more complex than a simple assignment.
Line 21 contains a “generalized invocation” of the base_t object. A generalized invocation calls a parent or super class method. PL/SQL member functions or procedures are methods. Line 21 calls the base_t type’s to_string function. This way, the overriding to_string function returns a specialized result. It returns the result from the parent class and the value of its own hobbit_name attribute.
You can test the generalized invocation with the following query:
SQL> SELECT 2 TREAT( 3 hobbit_t('Bilbo') AS hobbit_t).to_string() AS "Text" 4 FROM dual; |
The query prints:
Text ----------------------- UID#: [1] Type: [HOBBIT_T] Name: [Bilbo] |
Together we’ve explored of how you create types and subtypes. You’ve learned a type is a generalization or superclass, and a subtype is a specialization or subclass. You’ve also learned how to create both a generalization and specialization. At this point, you may ask, “Why should I bother with subtypes?”
The benefit of subtypes is dynamic dispatch. Dynamic dispatch is the process of selecting an object type from an inverted tree of object types. The topmost object type is the root node or most generalized version of an object type. The bottom most object type is a leaf node or the most specialized version of an object type. All nodes between the root node and leaf nodes are simply nodes. Nodes become more specialized as you step down the hierarchy from the root node.
The process of selecting an object type from an inverted tree is polymorphism. Polymorphism means your program specifies the most general node at compile time. Then, the program accepts the root node or any subordinate nodes at runtime. Moreover, dynamic dispatch is like writing a function or procedure to do many things.
Another form of dynamic dispatch occurs when you overload a function or procedure in a PL/SQL package. Calls to overloaded functions or procedure choose which version to run based on the data types of the call parameters.
The key difference between overloading and selecting object types is simple. The first deals with choosing between different data types or object types. The second deals with choosing between object types in the same node tree.
You have two choices to demonstrate dynamic dispatch. One would use a SQL table or varray collection and the other would use column substitutability. Creating a table that uses substitutability seems the easiest approach.
The following creates a table of the base_t object type:
SQL> CREATE TABLE dynamic 2 ( character_type BASE_T ); |
You can now insert a base_t object type or any of the base_t subtypes. The base_t_s sequence is reset for the test case INSERT statements:
SQL> INSERT INTO dynamic VALUES (base_t()); SQL> INSERT INTO dynamic VALUES (hobbit_t('Bilbo Baggins')); SQL> INSERT INTO dynamic VALUES (hobbit_t('Peregrin Took')); |
The following query uses a CASE statement to identify whether the column returns a base_t or hobbit_t object type:
SQL> SELECT 2 CASE 3 WHEN TREAT(character_type AS hobbit_t) IS NOT NULL THEN 4 TREAT(character_type AS hobbit_t).to_string() 5 ELSE 6 TREAT(character_type AS base_t).to_string() 7 END AS "Text" 8 FROM dynamic; |
The query returns the following:
Text ----------------------- UID#: [3] Type: [BASE_T] UID#: [7] Type: [HOBBIT_T] Name: [Bilbo Baggins] UID#: [13] Type: [HOBBIT_T] Name: [Peregrin Took] |
The result set shows you that the character_type column holds different types of the base_t object type. It should also show you how you may store different result logs from DML row level triggers in a single table. Another article, I hope to write soon.
The unique identifier appears to increment three times with the first INSERT statement and five times with subsequent inserts. Actually, each INSERT statement increments the sequence five times. A debug statement would show you that it assigns the third call to the .NEXTVAL pseudo column value to the obj_id value. This is true for both the base_t and hobbit_t object type, and any other derived subtypes.
This article has shown you how to implement object types and subtypes. It also has explained how dynamic dispatch works and it provides a working example of dynamic dispatch leveraging column substitutability.
Subscribe via RSS