Release 2 (9.2)
Part Number A96594-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Object-Relational Features Release 2 (9.2) Part Number A96594-01 |
|
This chapter provides basic information about working with objects. It explains what object types, methods, and collections are and describes how to create and work with a hierarchy of object types that are derived from a shared root type and are connected by inheritance.
This chapter contains these topics:
Object-relational functionality introduces a number of new concepts and resources. These are briefly described in the following sections.
An object type is a kind of datatype. You can use it in the same ways that you use more familiar datatypes such as NUMBER or VARCHAR2. For example, you can specify an object type as the datatype of a column in a relational table, and you can declare variables of an object type. You use a variable of an object type to contain a value of that object type. A value of an object type is an instance of that type. An object instance is also called an object.
Object types also have some important differences from the more familiar datatypes that are native to a relational database:
Attributes hold the data about an object's features of interest. For example, a soldier object type might have the attributes name, rank, and serial number. An attribute has a declared datatype which can in turn be another object type. Taken together, the attributes of an object instance contain that object's data.
Methods are procedures or functions provided to enable applications to perform useful operations on the attributes of the object type. Methods are an optional element of an object type. They define the behavior of objects of that type and determine what (if anything) that type of object can do.
You can think of an object type as a structural blueprint or template and an object as an actual thing built according to the template.
Object types are database schema objects, subject to the same kinds of administrative control as other schema objects (see Chapter 4, "Managing Oracle Objects").
You can use object types to model the actual structure of real-world objects. Object types enable you to capture the structural interrelationships of objects and their attributes instead of flattening this structure into a two-dimentional, purely relational schema of tables and columns. With object types you can store related pieces of data in a unit along with the behaviors defined for that data. Application code can then retrieve and manipulate these units as objects.
You can specialize an object type by creating subtypes that have some added, differentiating feature, such as an additional attribute or method. You create subtypes by deriving them from a parent object type, which is called a supertype of the derived subtypes.
Subtypes and supertypes are related by inheritance: as specialized versions of their parent, subtypes have all the parent's attributes and methods plus any specializations that are defined in the subtype itself. Subtypes and supertypes connected by inheritance make up a type hierarchy.
When you create a variable of an object type, you create an instance of the type: the result is an object. An object has the attributes and methods defined for its type. Because an object instance is a concrete thing, you can assign values to its attributes and call its methods.
Methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform.
A principal use of methods is to provide access to an object's data. You can define methods for operations that an application is likely to want to perform on the data so that the application does not have to code these operations itself. To perform the operation, an application calls the appropriate method on the appropriate object.
You can also define methods to compare object instances and to perform operations that do not use any particular object's data but instead are global to an object type.
An object table is a special kind of table in which each row represents an object.
For example, the following statements create a person object type and define an object table for person objects:
CREATE TYPE person AS OBJECT ( name VARCHAR2(30), phone VARCHAR2(20) ); CREATE TABLE person_table OF person;
You can view this table in two ways:
person object, allowing you to perform object-oriented operationsperson, namely name and phone, occupies a column, allowing you to perform relational operationsFor example, you can execute the following instructions:
INSERT INTO person_table VALUES ( "John Smith", "1-800-555-1212" ); SELECT VALUE(p) FROM person_table p WHERE p.name = "John Smith";
The first statement inserts a person object into person_table, treating person_table as a multi-column table. The second selects from person_table as a single-column table, using the VALUE function to return rows as object instances.
| See Also:
"VALUE" for information on the |
Objects that occupy complete rows in object tables are called row objects. Objects that occupy table columns in a larger row, or are attributes of other objects, are called column objects.
An object view (see Chapter 5, "Applying an Object Model to Relational Data") is a way to access relational data using object-relational features. It lets you develop object-oriented applications without changing the underlying relational schema.
A REF is a logical "pointer" to a row object. It is an Oracle built-in datatype. REFs and collections of REFs model associations among objects--particularly many-to-one relationships--thus reducing the need for foreign keys. REFs provide an easy mechanism for navigating between objects. You can use the dot notation to follow the pointers. Oracle does joins for you when needed, and in some cases can avoid doing joins.
You can use a REF to examine or update the object it refers to. You can also use a REF to obtain a copy of the object it refers to. You can change a REF so that it points to a different object of the same object type or assign it a null value.
In declaring a column type, collection element, or object type attribute to be a REF, you can constrain it to contain only references to a specified object table. Such a REF is called a scoped REF. Scoped REF types require less storage space and allow more efficient access than unscoped REF types.
The following example shows REF column address_ref scoped to an object table of address_objtyp.
CREATE TABLE people ( id NUMBER(4) name_obj name_objtyp, address_ref REF address_objtyp SCOPE IS address_objtab, phones_ntab phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab2 ;
A REF can be scoped to an object table of the declared type (address_objtyp in the example) or of any subtype of the declared type. If scoped to an object table of a subtype, the REF column is effectively constrained to hold references only to instances of the subtype (and its subtypes, if any) in the table.
Subtypes are a feature of type inheritance.
It is possible for the object identified by a REF to become unavailable--through either deletion of the object or a change in privileges. Such a REF is called dangling. Oracle SQL provides a predicate (called IS DANGLING) to allow testing REFs for this condition.
Accessing the object referred to by a REF is called dereferencing the REF. Oracle provides the DEREF operator to do this.
Dereferencing a dangling REF returns a null object.
Oracle also provides implicit dereferencing of REFs. For example, consider the following:
CREATE TYPE person AS OBJECT ( name VARCHAR2(30), manager REF person );
If X represents an object of type PERSON, then the SQL expression:
x.manager.name;
follows the pointer from the person X to another person, X's manager, and retrieves the manager's name. (Following the REF like this is allowed in SQL, but not in PL/SQL.)
You can obtain a REF to a row object by selecting the object from its object table and applying the REF operator. For example, you can obtain a REF to the purchase order with identification number 1000376 as follows:
DECLARE OrderRef REF to purchase_order; SELECT REF(po) INTO OrderRef FROM purchase_order_table po WHERE po.id = 1000376;
The query must return exactly one row.
For modeling one-to-many relationships, Oracle supports two collection datatypes: varrays and nested tables. Collection types can be used anywhere other datatypes can be used: you can have object attributes of a collection type, columns of a collection type, and so forth. For example, you might give a purchase order object type a nested table attribute to hold the collection of line items for a given purchase order.
You use the CREATE TYPE statement to define object types and collection types.
The following CREATE TYPE statements define the object types person, lineitem, lineitem_table, and purchase_order. lineitem_table is a collection type--a nested table type. The purchase_order object type has an attribute lineitems of this type. Each row in this nested table is an object of type lineitem.
The indented elements name, phone, item_name, and so on in the CREATE TYPE statements are attributes. Each has a datatype declared for it.
CREATE TYPE person AS OBJECT ( name VARCHAR2(30), phone VARCHAR2(20) ); CREATE TYPE lineitem AS OBJECT ( item_name VARCHAR2(30), quantity NUMBER, unit_price NUMBER(12,2) ); CREATE TYPE lineitem_table AS TABLE OF lineitem; CREATE TYPE purchase_order AS OBJECT ( id NUMBER, contact person, lineitems lineitem_table, MEMBER FUNCTION get_value RETURN NUMBER );
This is a simplified example. It does not show how to specify the body of the method get_value, which you do with the CREATE OR REPLACE TYPE BODY statement.
Defining an object type does not allocate any storage.
Once they are defined as types, lineitem, person, and purchase_order can be used in SQL statements in most of the same places you can use types like NUMBER or VARCHAR2.
For example, you might define a relational table to keep track of your contacts:
CREATE TABLE contacts ( contact person date DATE );
The CONTACTS table is a relational table with an object type as the datatype of one of its columns. Objects that occupy columns of relational tables are called column objects (see "Row Objects and Column Objects").
This section describes object types and references, including:
A table column, object, object attribute, collection, or collection element is NULL if it has been initialized to NULL or has not been initialized at all. Usually, a NULL value is replaced by an actual value later on.
An object whose value is NULL is called atomically null. An atomically null object is different from one that simply happens to have null values for all its attributes. When all the attributes of an object are null, these attributes can still be changed, and the object's methods can be called. With an atomically null object, you can do neither of these things.
For example, consider the CONTACTS table defined as follows:
CREATE TYPE person AS OBJECT ( name VARCHAR2(30), phone VARCHAR2(20) ); CREATE TABLE contacts ( contact person date DATE );
The statement
INSERT INTO contacts VALUES ( person (NULL, NULL), '24 Jun 1997' );
gives a different result from
INSERT INTO contacts VALUES ( NULL, '24 Jun 1997' );
In both cases, Oracle allocates space in CONTACTS for a new row and sets its DATE column to the value given. But in the first case, Oracle allocates space for an object in the PERSON column and sets each of the object's attributes to NULL. In the second case, Oracle sets the PERSON field itself to NULL and does not allocate space for an object.
In some cases, you can omit checks for null values. A table row or row object cannot be null. A nested table of objects cannot contain an element whose value is NULL.
A nested table or array can be null, so you do need to handle that condition. A null collection is different from an empty one, that is, a collection containing no elements.
When you declare a table column to be of an object type or collection type, you can include a DEFAULT clause. This provides a value to use in cases where you do not explicitly specify a value for the column. The default clause must contain a literal invocation of the constructor method for that object or collection.
A literal invocation of a constructor method is a call to the constructor method in which any arguments are either literals, or further literal invocations of constructor methods. No variables or functions are allowed.
For example, consider the following statements:
CREATE TYPE person AS OBJECT ( id NUMBER name VARCHAR2(30), address VARCHAR2(30) ); CREATE TYPE people AS TABLE OF person;
The following is a literal invocation of the constructor method for the nested table type PEOPLE:
people ( person(1, 'John Smith', '5 Cherry Lane'), person(2, 'Diane Smith', NULL) )
The following example shows how to use literal invocations of constructor methods to specify defaults:
CREATE TABLE department ( d_no CHAR(5) PRIMARY KEY, d_name CHAR(20), d_mgr person DEFAULT person(1,'John Doe',NULL), d_emps people DEFAULT people() ) NESTED TABLE d_emps STORE AS d_emps_tab;
Note that the term PEOPLE( ) is a literal invocation of the constructor method for an empty PEOPLE table.
You can define constraints on an object table just as you can on other tables.
You can define constraints on the leaf-level scalar attributes of a column object, with the exception of REFs that are not scoped.
The following examples illustrate the possibilities.
The first example places a primary key constraint on the SSNO column of the object table PERSON_EXTENT:
CREATE TYPE location ( building_no NUMBER, city VARCHAR2(40) ); CREATE TYPE person ( ssno NUMBER, name VARCHAR2(100), address VARCHAR2(100), office location ); CREATE TABLE person_extent OF person ( ssno PRIMARY KEY );
The DEPARTMENT table in the next example has a column whose type is the object type LOCATION defined in the previous example. The example defines constraints on scalar attributes of the LOCATION objects that appear in the DEPT_LOC column of the table.
CREATE TABLE department ( deptno CHAR(5) PRIMARY KEY, dept_name CHAR(20), dept_mgr person, dept_loc location, CONSTRAINT dept_loc_cons1 UNIQUE (dept_loc.building_no, dept_loc.city), CONSTRAINT dept_loc_cons2 CHECK (dept_loc.city IS NOT NULL) );
You can define indexes on an object table or on the storage table for a nested table column or attribute just as you can on other tables.
You can define indexes on leaf-level scalar attributes of column objects, as shown in the following example. You can only define indexes on REF attributes or columns if the REF is scoped.
Here, DEPT_ADDR is a column object, and CITY is a leaf-level scalar attribute of DEPT_ADDR that we want to index:
CREATE TABLE department ( deptno CHAR(5) PRIMARY KEY, dept_name CHAR(20), dept_addr address ); CREATE INDEX i_dept_addr1 ON department (dept_addr.city);
Wherever Oracle expects a column name in an index definition, you can also specify a scalar attribute of an object column.
You can define triggers on an object table just as you can on other tables. You cannot define a trigger on the storage table for a nested table column or attribute.
You cannot modify LOB values in a trigger body. Otherwise, there are no special restrictions on using object types with triggers.
The following example defines a trigger on the PERSON_EXTENT table defined in an earlier section:
CREATE TABLE movement ( ssno NUMBER, old_office location, new_office location ); CREATE TRIGGER trig1 BEFORE UPDATE OF office ON person_extent FOR EACH ROW WHEN new.office.city = 'REDWOOD SHORES' BEGIN IF :new.office.building_no = 600 THEN INSERT INTO movement (ssno, old_office, new_office) VALUES (:old.ssno, :old.office, :new.office); END IF; END;
In Oracle, a REF column or attribute can be unconstrained or constrained using a SCOPE clause or a referential constraint clause. When a REF column is unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.
Oracle does not ensure that the object references stored in such columns point to valid and existing row objects. Therefore, REF columns may contain object references that do not point to any existing row object. Such REF values are referred to as dangling references. Currently, Oracle does not permit storing object references that contain a primary-key based object identifier in unconstrained REF columns.
A REF column may be constrained to be scoped to a specific object table. All the REF values stored in a column with a SCOPE constraint point at row objects of the table specified in the SCOPE clause. The REF values may, however, be dangling.
A REF column may be constrained with a REFERENTIAL constraint similar to the specification for foreign keys. The rules for referential constraints apply to such columns. That is, the object reference stored in these columns must point to a valid and existing row object in the specified object table.
PRIMARY KEY constraints cannot be specified for REF columns. However, you can specify NOT NULL constraints for such columns.
Oracle SQL lets you omit qualifying table names in some relational operations. For example, if ASSIGNMENT is a column in PROJECTS and TASK is a column in DEPTS, you can write:
SELECT * FROM projects WHERE EXISTS (SELECT * FROM depts WHERE assignment = task);
Oracle determines which table each column belongs to.
Using the dot notation, you can qualify the column names with table names or table aliases to make things more maintainable:
SELECT * FROM projects WHERE EXISTS (SELECT * FROM depts WHERE projects.assignment = depts.task); SELECT * FROM projects pj WHERE EXISTS (SELECT * FROM depts dp WHERE pj.assignment = dp.task);
In some cases, object-relational features require you to specify the table aliases.
Using unqualified names can lead to problems. If you add an ASSIGNMENT column to DEPTS and forget to change the query, Oracle automatically recompiles the query such that the inner SELECT uses the ASSIGNMENT column from the DEPTS table. This situation is called inner capture.
To avoid inner capture and similar problems resolving references, Oracle requires you to use a table alias to qualify any dot-notational reference to methods or attributes of objects. Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation.
For example, the following statements define an object type PERSON and two tables. ptab1 is an object table for objects of type PERSON, and ptab2 is a relational table that contains a column of an object type.
CREATE TYPE person AS OBJECT (ssno VARCHAR(20)); CREATE TABLE ptab1 OF person; CREATE TABLE ptab2 (c1 person);
The following queries show some correct and incorrect ways to reference attribute ssno:
SELECT ssno FROM ptab1 ; --Correct SELECT c1.ssno FROM ptab2 ; --Illegal SELECT ptab2.c1.ssno FROM ptab2 ; --Illegal SELECT p.c1.ssno FROM ptab2 p ; --Correct
SELECT statement, ssno is the name of a column of ptab1. It references this top-level attribute directly, without using the dot notation, so no table alias is required.SELECT statement, ssno is the name of an attribute of the PERSON object in the column named c1. This reference uses the dot notation and so requires a table alias, as shown in the fourth SELECT statement.SELECT uses the table name itself to qualify this the reference. This is incorrect; a table alias is required.You must qualify a reference to an object attribute or method with a table alias rather than a table name even if the table name is itself qualified by a schema name.
For example, the following expression tries to refer to the scott schema, projects table, assignment column, and duedate attribute of that column. But the expression is incorrect because projects is a table name, not an alias.
scott.projects.assignment.duedate
The same requirement applies to attribute references that use REFs.
Table aliases should uniquely pick out the same table throughout a query and should not be the same as schema names that could legally appear in the query.
User-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. You cannot use a database link to do any of the following:
Methods are functions or procedures that you can declare in an object type definition to implement behavior that you want objects of that type to perform. An application calls the methods to invoke the behavior.
For example, you might declare a method get_sum() to get a purchase order object to return the total cost of its line items. The following line of code calls such a method for purchase order po and returns the amount into sum_line_items:
sum_line_items = po.get_sum();
The parentheses are required. Unlike with PL/SQL functions and procedures, Oracle requires parentheses with all method calls, even ones that do not have arguments.
Methods can be written in PL/SQL or virtually any other programming language. Methods written in PL/SQL or Java are stored in the database. Methods written in other languages, such as C, are stored externally.
Two general kinds of methods can be declared in a type definition:
There is also a third kind of method, called a constructor method, that the system defines for every object type. You call a type's constructor method to construct or create an object instance of the type.
Member methods are the means by which an application gains access to an object instance's data. You define a member method in the object type for each operation that you want an object of that type to be able to perform. For example, the method get_sum()that sums the total cost of a purchase order's line items operates on the data of a particular purchase order and is a member method.
Member methods have a built-in parameter named SELF that denotes the object instance on which the method is currently being invoked. Member methods can reference the attributes and methods of SELF without a qualifier. This makes it simpler to write member methods. For example, the following code shows a method declaration that takes advantage of SELF to omit qualification of the attributes num and den:
CREATE TYPE Rational AS OBJECT ( num INTEGER, den INTEGER, MEMBER PROCEDURE normalize, ... ); CREATE TYPE BODY Rational AS MEMBER PROCEDURE normalize IS g INTEGER; BEGIN g := gcd(SELF.num, SELF.den); g := gcd(num, den); -- equivalent to previous line num := num / g; den := den / g; END normalize; ... END;
SELF does not need to be explicitly declared, although it can be. It is always the first parameter passed to the method. In member functions, if SELF is not declared, its parameter mode defaults to IN. In member procedures, if SELF is not declared, its parameter mode defaults to IN OUT.
You invoke a member method using the "dot" notation object_variable.method(). The notation specifies first the object on which to invoke the method and then the method to call. Any parameters occur inside the parentheses, which are required.
The values of a scalar datatype such as CHAR or REAL have a predefined order, which allows them to be compared. But an object type, such as a customer_typ, which can have multiple attributes of various datatypes, has no predefined axis of comparison. To be able to compare and order variables of an object type, you must specify a basis for comparing them.
Two special kinds of member methods can be defined for doing this: map methods and order methods.
A map method is an optional kind of method that provides a basis for comparing objects by mapping object instances to one of the scalar types DATE, NUMBER, VARCHAR2 or to an ANSI SQL type such as CHARACTER or REAL. With a map method, you can order any number of objects by calling each object's map method once to map that object to a position on the axis used for the comparison (a number or date, for example).
From the standpoint of writing one, a map method is simply a parameterless member function that uses the MAP keyword and returns one of the datatypes just listed. What makes a map method special is that, if an object type defines one, the method is called automatically to evaluate such comparisons as obj_1 > obj_2 and comparisons implied by the DISTINCT, GROUP BY, and ORDER BY clauses. Where obj_1 and obj_2 are two object variables that can be compared using a map method map(), the comparison:
obj_1 > obj_2
is equivalent to:
obj_1.map() > obj_2.map()
And similarly for other relational operators besides ">".
The following example defines a map method area() that provides a basis for comparing rectangle objects by their area:
CREATE TYPE Rectangle_typ AS OBJECT ( len NUMBER, wid NUMBER, MAP MEMBER FUNCTION area RETURN NUMBER, ... ); CREATE TYPE BODY Rectangle_typ AS MAP MEMBER FUNCTION area RETURN NUMBER IS BEGIN RETURN len * wid; END area; ... END;
An object type can declare at most one map method (or one order method). A subtype can declare a map method only if its root supertype declares one.
Order methods make direct object-to-object comparisons. Unlike map methods, they cannot map any number of objects to an external axis. They simply tell you that the current object is less than, equal to, or greater than the other object that it is being compared to, with respect to the criterion used by the method.
An order method is a function with one declared parameter for another object of the same type. The method must be written to return either a negative number, zero, or a positive number. The return signifies that the object picked out by the SELF parameter is respectively less than, equal to, or greater than the other parameter's object.
As with map methods, an order method, if one is defined, is called automatically whenever two objects of that type need to be compared.
Order methods are useful where comparison semantics may be too complex to use a map method. For example, to compare binary objects such as images, you might create an order method to compare the images by their brightness or number of pixels.
An object type can declare at most one order method (or one map method). Only a type that is not derived from another type can declare an order method: a subtype cannot define one.
The following example shows an order method that compares customers by customer ID:
CREATE TYPE Customer_typ AS OBJECT ( id NUMBER, name VARCHAR2(20), addr VARCHAR2(30), ORDER MEMBER FUNCTION match (c Customer_typ) RETURN INTEGER ); CREATE TYPE BODY Customer_typ AS ORDER MEMBER FUNCTION match (c Customer_typ) RETURN INTEGER IS BEGIN IF id < c.id THEN RETURN -1; -- any negative number will do ELSIF id > c.id THEN RETURN 1; -- any positive number will do ELSE RETURN 0; END IF; END; END;
A map method maps object values into scalar values and can order multiple values by their position on the scalar axis. An order method directly compares values for two particular objects.
You can declare a map method or an order method but not both. If you declare a method of either type, you can compare objects in SQL and procedural statements. However, if you declare neither method, you can compare objects only in SQL statements and only for equality or inequality. (Two objects of the same type count as equal only if the values of their corresponding attributes are equal.)
When sorting or merging a large number of objects, use a map method. One call maps all the objects into scalars, then sorts the scalars. An order method is less efficient because it must be called repeatedly (it can compare only two objects at a time).
In a type hierarchy, where definitions of specialized types are derived from definitions of more general types, only the root type--the most basic type, from which all other types are derived--can define an order method. If the root type does not define one, its subtypes cannot define one either.
If the root type specifies a map method, any of its subtypes can define a map method that overrides the map method of the root type. But if the root type does not specify a map method, no subtype can specify one either.
So if the root type does not specify either a map or an order method, none of the subtypes can specify either a map or order method.
Static methods are invoked on the object type, not its instances. You use a static method for operations that are global to the type and do not need to reference the data of a particular object instance. A static method has no SELF parameter.
You invoke a static method by using the "dot" notation to qualify the method call with the name of the object type: type_name.method().
Every object type has a constructor method implicitly defined for it by the system. A constructor method is a function that returns a new instance of the user-defined type and sets up the values of its attributes. You can also explicitly define your own constructors. The present section describes constructor methods in general and system-defined constructors in particular.
| See Also:
"User-Defined Constructors" for information on user-defined constructors and their advantages |
A constructor method is a function; it returns the new object as its value. The name of the constructor method is just the name of the object type. Its parameters have the names and types of the object type's attributes.
For example, suppose we have a type Customer_typ:
CREATE TYPE Customer_typ AS OBJECT ( id NUMBER, name VARCHAR2(20), phone VARCHAR2(30), );
The following example creates a new object instance of Customer_typ, specifies values for its attributes, and sets the object into a variable:
cust = Customer_typ(103, "Ravi", "1-800-555-1212")
The INSERT statement in the next example inserts a customer object that has an attribute of Address_typ object type. The constructor method Address_typ constructs an object of this type having the attribute values shown in the parentheses:
INSERT INTO Customer_objtab VALUES ( 1, 'Jean Nance', Address_typ('2 Avocet Drive', 'Redwood Shores', 'CA', '95054'), ... ) ;
Oracle supports two collection datatypes: varrays and nested tables.
RAW or BLOB).If you need to store only a fixed number of items, or to loop through the elements in order, or you will often want to retrieve and manipulate the entire collection as a value, then use a varray.
If you need to run efficient queries on a collection, handle arbitrary numbers of elements, or do mass insert/update/delete operations, then use a nested table.
An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.
The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called varrays. You must specify a maximum size when you declare the array type.
For example, the following statement declares an array type:
CREATE TYPE prices AS VARRAY(10) OF NUMBER(12,2);
The VARRAYs of type PRICES have no more than ten elements, each of datatype NUMBER(12,2).
Creating an array type does not allocate space. It defines a datatype, which you can use as:
A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB.
A varray cannot contain LOBs. This means that a varray also cannot contain elements of a user-defined type that has a LOB attribute.
A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.
For example, in the purchase order example, the following statement declares the table type used for the nested tables of line items:
CREATE TYPE lineitem_table AS TABLE OF lineitem;
A table type definition does not allocate space. It defines a type, which you can use as
When a column in a relational table is of nested table type, Oracle stores the nested table data for all rows of the relational table in the same storage table. Similarly, with an object table of a type that has a nested table attribute, Oracle stores nested table data for all object instances in a single storage table associated with the object table.
For example, the following statement defines an object table for the object type PURCHASE_ORDER:
CREATE TABLE purchase_order_table OF purchase_order NESTED TABLE lineitems STORE AS lineitems_table;
The second line specifies LINEITEMS_TABLE as the storage table for the LINEITEMS attributes of all of the PURCHASE_ORDER objects in PURCHASE_ORDER_TABLE.
A convenient way to access the elements of a nested table individually is to use a nested cursor.
| See Also:
See Oracle9i SQL Reference for information about nested cursors, and see"Nested Tables" for more information on using nested tables. |
Multilevel collection types are collection types whose elements are themselves directly or indirectly another collection type. Possible multilevel collection types are:
Like ordinary, single-level collection types, multilevel collection types can be used with columns in a relational table or with object attributes in an object table.
The following example creates a multilevel collection type that is a nested table of nested tables. The example models a system of stars in which each star has a nested table collection of the planets revolving around it, and each planet has a nested table collection of its satellites.
CREATE TYPE satellite_t AS OBJECT ( name VARCHAR2(20), diameter NUMBER); CREATE TYPE nt_sat_t AS TABLE OF satellite_t; CREATE TYPE planet_t AS OBJECT ( name VARCHAR2(20), mass NUMBER, satellites nt_sat_t); CREATE TYPE nt_pl_t AS TABLE OF planet_t;
A nested table type column or object table attribute requires a storage table where rows for all nested tables in the column are stored. Similarly with a multilevel nested table collection of nested tables: the inner set of nested tables requires a storage table just as the outer set does. You specify one by appending a second nested-table storage clause.
For example, the following code creates a table stars that contains a column planets whose type is a multilevel collection (a nested table of an object type that has a nested table attribute satellites). Separate nested table clauses are provided for the outer planets nested table and for the inner satellites one.
CREATE TABLE stars ( name VARCHAR2(20), age NUMBER, planets nt_pl_t) NESTED TABLE planets STORE AS planets_tab (NESTED TABLE satellites STORE AS satellites_tab);
The preceding example can refer to the inner satellite nested table by name because this nested table is a named attribute of an object. However, if the inner nested table is not an attribute, it has no name. The keyword COLUMN_VALUE is provided for this case: you use it in place of a name for an inner nested table. For example:
CREATE TYPE inner_table AS TABLE OF NUMBER; CREATE TYPE outer_table AS TABLE OF inner_table; CREATE TABLE tab1 ( col1 NUMBER, col2 outer_table) NESTED TABLE col2 STORE AS col2_ntab (NESTED TABLE COLUMN_VALUE STORE AS cv_ntab);
Physical attributes for the storage tables can be specified in the nested table clause. For example:
CREATE TABLE stars ( name VARCHAR2(20), age NUMBER, planets nt_pl_t) NESTED TABLE planets STORE AS planets_tab ( PRIMARY KEY (NESTED_TABLE_ID, name) ORGANIZATION INDEX COMPRESS NESTED TABLE satellites STORE AS satellites_tab );
Every nested table storage table contains a column, referenceable by NESTED_TABLE_ID, that keys rows in the storage table to the associated row in the parent table. A parent table that is itself a nested table has two system-supplied ID columns: one, referenceable by NESTED_TABLE_ID, that keys its rows back to rows in its own parent table, and one hidden column referenced by the NESTED_TABLE_ID column in its nested table children.
In the preceding example, nested table planets is made an IOT (index-organized table) by adding the ORGANIZATION INDEX clause and assigning the nested table a primary key in which the first column is NESTED_TABLE_ID. This column contains the ID of the row in the parent table with which a storage table row is associated. Specifying a primary key with NESTED_TABLE_ID as the first column and index-organizing the table cause Oracle to physically cluster all the nested table rows that belong to the same parent row, for more efficient access.
| See Also:
"Nested Table Storage" and "Object Tables with Embedded Objects" |
Each nested table needs its own table storage clause, so you must have as many nested table storage clauses as you have levels of nested tables in a collection.
Multilevel varrays are stored in one of two ways, depending on whether the varray is a varray of varrays or a varray of nested tables.
LOB storage is explicitly specified.LOB, with only the LOB locator stored in the row. There is no storage table associated with nested table elements of a varray. The entire nested table collection is stored inside the varray.You can explicitly specify LOB storage for varrays. The following example does this for the varray elements of a nested table. As the example also shows, you can use the COLUMN_VALUE keyword with varrays as well as nested tables.
CREATE TYPE va1 AS VARRAY(10) OF NUMBER; CREATE TYPE nt3 AS TABLE OF va1; CREATE TABLE tab2 (c1 NUMBER, c2 nt3) NESTED TABLE c2 STORE AS c2_tab2_nt ( VARRAY column_value STORE AS LOB tab2_lob );
The following example shows explicit LOB storage specified for a varray of varray type:
CREATE TYPE t2 AS OBJECT (a NUMBER, b va1); CREATE TYPE va2 AS VARRAY(2) OF t2; CREATE TABLE tab5 (c1 NUMBER, c2 va2) VARRAY c2 STORE AS tab5_lob;
As with single-level collections, both the source and the target must be of the same declared data type in assignments of multilevel collections.
Items whose data types are collection types, including multilevel collection types, cannot be compared.
You create an instance of a collection type in the same way that you create an instance of any other object type, namely, by calling the type's constructor method. The name of a type's constructor method is simply the name of the type. You specify the elements of the collection as a comma-delimited list of arguments to the method.
Calling a constructor method with an empty list creates an empty collection of that type. Note that an empty collection is an actual collection that happens to be empty; it is not the same as a null collection.
Like single-level collection types, multilevel collection types are created by calling the respective type's constructor method. Like the constructor methods for other user-defined types, a constructor for a multilevel collection type is a system-defined function that has the same name as the type and returns a new instance of it--in this case, a new multilevel collection. Constructor parameters have the names and types of the object type's attributes.
The following example calls the constructor for the multilevel collection type nt_pl_t. This type is a nested table of planets, each of which contains a nested table of satellites as an attribute. The constructor for the outer nested table calls the planet_t constructor for each planet to be created; each planet constructor calls the constructor for the satellites nested table type to create its nested table of satellites; and the satellites nested table type constructor calls the satellite_t constructor for each satellite instance to be created.
INSERT INTO stars VALUES('Sun',23, nt_pl_t( planet_t( 'Neptune', 10, nt_sat_t( satellite_t('Proteus',67), satellite_t('Triton',82) ) ), planet_t( 'Jupiter', 189, nt_sat_t( satellite_t('Callisto',97), satellite_t('Ganymede', 22) ) ) ) );
There are two general ways to query a table that contains a column or attribute of a collection type. One way returns the collections nested in the result rows that contain them. The other way distributes or unnests collections such that each collection element appears on a row by itself.
In the following query, column projects is a nested table collection of projects_list_nt type. The projects collection column appears in the SELECT list like an ordinary, scalar column. Querying a collection column in the SELECT list like this nests the elements of the collection in the result row with which the collection is associated.
For example, the following query gets the name of each employee and the collection of projects for that employee. The collection of projects is nested:
SELECT e.empname, e.projects FROM employees e; EMPNAME PROJECTS ------- -------- 'Bob' PROJECTS_LIST_NT(14, 23, 144) 'Daphne' PROJECTS_LIST_NT(14, 35)
If project values or instances are a user-defined type--for example, Proj_t, with two attributes, id and name--a result row looks something like this:
EMPNAME PROJECTS ------- -------- 'Bob' PROJECTS_LIST_NT(PROJ_T(14, 'White Horse'), PROJ_T(23, 'Excalibur'), ...)
Results are also nested if an object-type column in the SELECT list contains a collection attribute, even if that collection is not explicitly listed in the SELECT list itself. For example, the query SELECT * FROM employees would produce a nested result.
Not all tools or applications are able to deal with results in a nested format. To view Oracle collection data using tools that require a conventional format, you must unnest, or flatten, the collection attribute of a row into one or more relational rows. You can do this by using a TABLE expression with the collection. A TABLE expression enables you to query a collection in the FROM clause like a table. In effect, you join the nested table with the row that contains the nested table.
The TABLE expression can be used to query any collection value expression, including transient values such as variables and parameters.
|
Note: The |
Like the preceding example, the following query gets the name of each employee and the collection of projects for that employee, but the collection is unnested:
SELECT e.empname, p.* FROM employees e, TABLE(e.projects) p; EMPNAME PROJECTS ------- -------- 'Bob' 14 'Bob' 23 'Bob' 144 'Daphne' 14 'Daphne' 35
As the preceding example shows, a TABLE expression can have its own table alias. In the example, a table alias for the TABLE expression appears in the SELECT list to select columns returned by the TABLE expression.
The TABLE expression uses another table alias to specify the table that contains the collection column that the TABLE expression references. Thus the expression TABLE(e.projects) specifies the employees table as containing the projects collection column. A TABLE expression can use the table alias of any table appearing to the left of it in a FROM clause to reference a column of that table. This way of referencing collection columns is called left correlation.
In the following example, the employees table is listed in the FROM clause solely to provide a table alias for the TABLE expression to use. No columns from the employees table other than the column referenced by the TABLE expression appear in the result:
SELECT * FROM employees e, TABLE(e.projects); PROJECTS -------- 14 23 144 14 35
Or:
SELECT p.* FROM employees e, TABLE(e.projects) p WHERE e.empid = 100; PROJECTS -------- 14 23 144
The following example produces rows only for employees who have projects.
SELECT e.empname, p.* FROM employees e, TABLE(e.projects) p;
To get rows for employees with no projects, you can use outer-join syntax:
SELECT e.*, p.* FROM employees e, TABLE(e.projects)(+) p;
The (+) indicates that the dependent join between employees and e.projects should be NULL-augmented. That is, there will be rows of employees in the output for which e.projects is NULL or empty, with NULL values for columns corresponding to e.projects.
The preceding examples show a TABLE expression that contains the name of a collection. Alternatively, a TABLE expression can contain a subquery of a collection.
The following example returns the collection of projects for the employee whose id is 100.
SELECT * FROM TABLE(SELECT e.projects FROM employees e WHERE e.empid = 100); PROJECTS -------- 14 23 144
There are these restrictions on using a subquery in a TABLE expression:
SELECT list of the subquery must contain exactly one itemSELECT projects FROM employees succeeds in a TABLE expression only if table employees contains just a single row. If the table contains more than one row, the subquery produces an error.Here is an example showing a TABLE expression used in the FROM clause of a SELECT embedded in a CURSOR expression:
SELECT e.empid, CURSOR(SELECT * FROM TABLE(e.projects)) FROM employees e;
Unnesting queries can be used with multilevel collections, too, for both varrays and nested tables. The following example shows an unnesting query on a multilevel nested table collection of nested tables. From a table stars in which each star has a nested table of planets and each planet has a nested table of satellites, the query returns the names of all satellites from the inner set of nested tables.
SELECT t.name FROM stars s, TABLE(s.planets) p, TABLE(p.satellites) t;
Oracle supports the following DML operations on nested table columns:
Oracle does not support piecewise updates on VARRAY columns. However, VARRAY columns can be inserted into or updated as an atomic unit.
For piecewise updates of nested table columns, the DML statement identifies the nested table value to be operated on by using the TABLE expression.
The following DML statements demonstrate piecewise operations on nested table columns.
INSERT INTO TABLE(SELECT e.projects FROM employees e WHERE e.eno = 100) VALUES (1, 'Project Neptune'); UPDATE TABLE(SELECT e.projects FROM employees e WHERE e.eno = 100) p SET VALUE(p) = project_typ(1, 'Project Pluto') WHERE p.pno = 1; DELETE FROM TABLE(SELECT e.projects FROM employee e WHERE e.eno = 100) p WHERE p.pno = 1;
For multilevel nested table collections, DML can be done atomically, on the collection as a whole, or piecewise, on selected elements. For multilevel varray collections, DML operations can be done only atomically.
The section "Constructors for Multilevel Collections" shows an example of inserting an entire multilevel collection with an INSERT statement. Multilevel collections can also be updated atomically with an UPDATE statement. For example, suppose v_planets is a variable declared to be of the planets nested table type nt_pl_t. The following statement updates stars by setting the planets collection as a unit to the value of v_planets.
UPDATE stars s SET s.planets = :v_planets WHERE s.name = 'Aurora Borealis';
Piecewise DML is possible only on nested tables, not on varrays.
The following example shows a piecewise insert operation on the planets nested table of nested tables: the example inserts a new planet, complete with its own nested table of satellite_t:
INSERT INTO TABLE( SELECT planets FROM stars WHERE name = 'Sun') VALUES ('Saturn', 56, nt_sat_t( satellite_t('Rhea', 83) ) );
The next example performs a piecewise insert into an inner nested table to add a satellite for a planet. Like the preceding, this example uses a TABLE expression containing a subquery that selects the inner nested table to specify the target for the insert.
INSERT INTO TABLE( SELECT p.satellites FROM TABLE( SELECT s.planets FROM stars s WHERE s.name = 'Sun') p WHERE p.name = 'Uranus') VALUES ('Miranda', 31);
Object types enable you to model the real-world entities such as customers and purchase orders that your application works with. But this is just the first step in exploiting the capabilities of objects. With objects, you cannot only model an entity such as a customer, you can also define different specialized types of customers in a type hierarchy under the original type. You can then perform operations on a hierarchy and have each type implement and execute the operation in a special way.
A type hierarchy is a sort of family tree of object types. It consists of a parent base type, called a supertype, and one or more levels of child object types, called subtypes, derived from the parent.
Subtypes in a hierarchy are connected to their supertypes by inheritance. This means that subtypes automatically acquire the attributes and methods of their parent type. It also means that subtypes automatically acquire any changes made to these attributes or methods in the parent: any attributes or methods updated in a supertype are updated in subtypes as well.
A subtype becomes a specialized version of the parent type by adding new attributes and methods to the set inherited from the parent or by redefining methods it inherits. Redefining an inherited methods gives a subtype its own way of executing the method. Add to this that an object instance of a subtype can generally be substituted for an object instance of any of its supertypes in code, and you have polymorphism.
Polymorphism is the ability of a slot for a value in code to contain a value of either a certain declared type or any of a range of the declared type's subtypes. A method called on whatever value occupies the slot may execute differently depending on the value's type because the various types might implement the method differently.
A subtype can be derived from a supertype either directly, or indirectly through intervening levels of other subtypes.
A subtype can directly derive only from a single supertype: it cannot derive jointly from more than one. A supertype can have multiple sibling subtypes, but a subtype can have at most one direct parent supertype. In other words, Oracle supports only single inheritance, not multiple inheritance.
A subtype is derived from a supertype by defining a specialized variant of the supertype. For example, from a customer object type you might derive the specialized types govt_customer and corp_customer. Each of these subtypes is still at bottom a customer, but a special kind of customer. What makes a subtype special and distinguishes it from its parent supertype is some change made in the subtype to the attributes or methods that the subtype received from its parent.
An object type's attributes and methods make the type what it is: they are its essential, defining features. If a customer object type has the three attributes customer_id, name, and address and the method get_id(), then any object type that is derived from customer will have these same three attributes and a method get_id(). A subtype is a special case of its parent type, not a totally different kind of thing. As such, it shares with its parent type the features that make the general type what it is.
You can specialize the attributes or methods of a subtype in these ways:
For example, you might specialize corp_customer as a special kind of customer by adding to its definition an attribute for account_mgr_id. A subtype cannot drop or change the type of an attribute it inherited from its parent; it can only add new attributes.
For example, a shape object type might define a method calculate_area(). Two subtypes of shape, rectilinear_shape and circular_shape, might each implement this method in a different way.
Attributes and methods that a subtype gets from its parent type are said to be inherited. This means more than just that the attributes and methods are patterned on the parent's when the subtype is defined. With object types, the inheritance link remains live. Any changes made later on to the parent type's attributes or methods are also inherited so that the changes are reflected in the subtype as well. Unless a subtype reimplements an inherited method, it always contains the same core set of attributes and methods that are in the parent type, plus any attributes and methods that it adds.
Remember, a child type is not a different type from its parent: it's a particular kind of that type. If the general definition of customer ever changes, the definition of corp_customer changes too.
The live inheritance relationship that holds between a supertype and its subtypes is the source of both much of the power of objects and much of their complexity. It is a very powerful feature to be able to change a method in a supertype and have the change take effect in all the subtypes downstream just by recompiling. But this same capability means that you have to think about such things as whether you want to allow a type to be specialized or a method to be redefined. Similarly, it is a powerful feature for a table or column to be able to contain any type in a hierarchy, but then you must decide whether to allow this in a particular case, and you may need to constrain DML statements and queries so that they pick out from the type hierarchy just the range of types that you want. The following sections address these aspects of working with objects.
An object type's definition determines whether subtypes can be derived from that type. To permit subtypes, the object type must be defined as not final. This is done by including the NOT FINAL keyword in its type declaration. For example:
CREATE TYPE Person_typ AS OBJECT ( ssn NUMBER, name VARCHAR2(30), address VARCHAR2(100)) NOT FINAL;
The preceding statement declares Person_typ to be a not final type such that subtypes of Person_typ can be defined. By default, an object type is final--that is, subtypes cannot be derived from it.
You can change a final type to a not final type and vice versa with an ALTER TYPE statement. For example, the following statement changes Person_typ to a final type:
ALTER TYPE Person_typ FINAL;
You can alter a type from NOT FINAL to FINAL only if the target type has no subtypes.
Methods, too, can be declared to be final or not final. If a method is declared to be final, subtypes cannot override it by providing their own implementation. Unlike types, methods are not final by default and must be explicitly declared to be final.
The following statement creates a not final type containing a final member function:
CREATE TYPE T AS OBJECT (..., MEMBER PROCEDURE Print(), FINAL MEMBER FUNCTION foo(x NUMBER)... ) NOT FINAL;
You create a subtype using a CREATE TYPE statement that specifies the immediate parent of the subtype with an UNDER parameter:
CREATE TYPE Student_typ UNDER Person_typ ( deptid NUMBER, major VARCHAR2(30)) NOT FINAL;
The preceding statement creates Student_typ as a subtype of Person_typ. As a subtype of Person_typ, Student_typ inherits all the attributes declared in or inherited by Person_typ and any methods inherited by Person_typ or declared in Person_typ.
The statement that defines Student_typ specializes Person_typ by adding two new attributes. New attributes declared in a subtype must have names that are different from the names of any attributes or methods declared in any of its supertypes, higher up in its type hierarchy.
A type can have multiple child subtypes, and these can also have subtypes. The following statement creates another subtype Employee_typ under Person_typ.
CREATE TYPE Employee_typ UNDER Person_typ ( empid NUMBER, mgr VARCHAR2(30));
A subtype can be defined under another subtype. Again, the new subtype inherits all the attributes and methods that its parent type has, both declared and inherited. For example, the following statement defines a new subtype PartTimeStudent_typ under Student_typ. The new subtype inherits all the attributes and methods of Student_typ and adds another attribute.
CREATE TYPE PartTimeStudent_typ UNDER Student_typ ( numhours NUMBER);
A type can be declared to be NOT INSTANTIABLE. If a type is not instantiable, there is no constructor (default or user-defined) for it, and you cannot instantiate instances of that type (objects, in other words). You might use this option with types that you intend to use solely as supertypes of specialized subtypes that you do instantiate. For example:
CREATE TYPE Address_typ AS OBJECT(...) NOT INSTANTIABLE NOT FINAL; CREATE TYPE USAddress_typ UNDER Address_typ(...); CREATE TYPE IntlAddress_typ UNDER Address_typ(...);
A method can also be declared to be not instantiable. Use this option when you want to declare a method in a type without implementing the method there. A type that contains a non-instantiable method must itself be declared not instantiable. For example:
CREATE TYPE T AS OBJECT ( x NUMBER, NOT INSTANTIABLE MEMBER FUNCTION func1() RETURN NUMBER ) NOT INSTANTIABLE NOT FINAL;
A non-instantiable method serves as a placeholder. You might define a non-instantiable method when you expect every subtype to override the method in a different way. In such a case, there is no point in defining the method in the supertype.
If a subtype does not provide an implementation for every inherited non-instantiable method, the subtype itself, like the supertype, must be declared not instantiable.
A non-instantiable subtype can be defined under an instantiable supertype.
You can alter an instantiable type to a non-instantiable type and vice versa with an ALTER TYPE statement. For example, the following statement makes Example_typ instantiable:
ALTER TYPE Example_typ INSTANTIABLE;
You can alter an instantiable type to a non-instantiable type only if the type has no columns, views, tables, or instances that reference that type, either directly, or indirectly through another type or subtype.
You cannot declare a non-instantiable type to be FINAL (which would be pointless anyway).
A subtype automatically inherits all methods (both member and static methods) declared in or inherited by its supertype.
A subtype can redefine methods it inherits, and it can also add new methods. It can even add new methods that have the same names as methods it inherits, such that the subtype ends up containing more than one method with the same name.
Giving a type multiple methods with the same name is called method overloading. Redefining an inherited method to customize its behavior for a subtype is called method overriding.
Overloading is useful when you want to provide a variety of ways of doing something. For example, a shape object might overload a draw() method with another draw() method that adds a text label to the drawing and contains an argument for the label's text.
When a type has several methods with the same name, the compiler uses the methods' signatures to tell them apart. A method's signature is a sort of structural profile. It consists of the method's name and the number, types, and order of the method's formal parameters (including the implicit self parameter). Methods that have the same name but different signatures are called overloads (when they exist in the same type).
Subtype MySubType_typ in the following example creates an overload of foo():
CREATE TYPE MyType_typ AS OBJECT (..., MEMBER PROCEDURE foo(x NUMBER), ...) NOT FINAL; CREATE TYPE MySubType_typ UNDER MyType_typ (..., MEMBER PROCEDURE foo(x DATE), STATIC FUNCTION bar(...)... ...);
MySubType_typ contains two versions of foo( ): one inherited version, with a NUMBER parameter, and a new version with a DATE parameter.
Overriding redefines an inherited method to make it do something different in the subtype. For example, a subtype circular_shape derived from a shape supertype might override a method calculate_area() to customize it specifically for calculating the area of a circle.
When a subtype overrides a method, the new version is executed instead of the overridden one whenever an instance of the subtype invokes the method. If the subtype itself has subtypes, these inherit the override of the method instead of the original version.
It's possible that a supertype may contain overloads of a method that is overridden in a subtype. Overloads of a method all have the same name, so the compiler uses the signature of the subtype's overriding method to identify the version in the supertype to override. This means that, to override a method, you must preserve its signature.
In the type definition, precede a method declaration with the OVERRIDING keyword to signal that you are overriding the method. For example, in the following code, the subtype signals that it is overriding method Print():
CREATE TYPE MyType_typ AS OBJECT (..., MEMBER PROCEDURE Print(), FINAL MEMBER FUNCTION foo(x NUMBER)... ) NOT FINAL; CREATE TYPE MySubType_typ UNDER MyType_typ (..., OVERRIDING MEMBER PROCEDURE Print(), ...);
As with new methods, you supply the declaration for an overridng method in a CREATE TYPE BODY statement.
As a result of method overriding, a type hierarchy can define multiple implementations of the same method. For example, in a hierarchy of the types ellipse_typ, circle_typ, sphere_typ, each type might define a method calculate_area() differently.
When such a method is invoked, the type of the object instance that invokes it is used to determine which implementation of the method to use. The call is then dispatched to that implementation for execution. This process of selecting a method implementation is called "virtual" or "dynamic method dispatch" because it is done at run time, not at compile time.
A method call is dispatched to the nearest implementation, working back up the inheritance hierarchy from the current or specified type. If the call invokes a member method of an object instance, the type of that instance is the current type, and the implementation defined or inherited by that type is used. If the call invokes a static method of a type, the implementation defined or inherited by that specified type is used.
For example, if c1 is an object instance of circle_typ, c1.foo() looks first for an implementation of foo() defined in circle_typ. If none is found, it looks up the supertype chain for an implementation in ellipse_typ. The fact that sphere_typ also defines an implementation is irrelevant because the type hierarchy is searched only upwards, toward the top. Subtypes of the current type are not searched.
Similarly, a call to a static method circle_typ.bar() looks first in circle_typ and then, if necessary, in the supertype(s) of circle_typ. The subtype sphere_typ is not searched.
In a type hierarchy, the subtypes are variant kinds of the root, base type. For example, a Student_typ type and an Employee_typ are kinds of a Person_typ. The base type includes these other types.
When you work with types in a type hierarchy, sometimes you want to work at the most general level and, for example, select or update all persons. But sometimes you want to select or update only students, or only persons who are not students.
The (polymorphic) ability to select all persons and get back not only objects whose declared type is Person_typ but also objects whose declared (sub)type is Student_typ or Employee_typ is called substitutability. A supertype is substitutable if one of its subtypes can substitute or stand in for it in a slot (a variable, column, and so forth) whose declared type is the supertype.
In general, types are substitutable. This is what you would expect, given that a subtype is, after all, just a specialized kind of any of its supertypes. Formally, though, a subtype is a type in its own right: it is not the same type as its supertype. A column that holds all persons, including all persons who are students and all persons who are employees, actually holds data of multiple types.
Substitutability comes into play in attributes, columns, and rows (namely, of an object view or object table) declared to be an object type, a REF to an object type, or a collection type.
In principle, object attributes, collection elements and REFs are always substitutable: there is no syntax at the level of the type definition to constrain their substitutability to some subtype. You can, however, turn off or constrain substitutability at the storage level, for specific tables and columns.
Object attributes, collection elements and REFs are substitutable. Where MyType is an object type:
REF type attributes: An attribute defined as REF MyType can hold a REF to an instance of MyType or to an instance of any subtype of MyType.MyType can hold an instance of MyType or of any subtype of MyType.MyType can hold instances of MyType and instances of any subtype of MyType.For instance, the author attribute is substitutable in the Book_typ defined in the following example:
CREATE TYPE Book_typ AS OBJECT ( title VARCHAR2(30), author Person_typ /* substitutable */);
An instance of Book_typ can be created by specifying a title string and an author of Person_typ or of any subtype of Person_typ. The following example specifies an author of type Employee_typ:
Book_typ(`My Oracle Experience', Employee_typ(12345, `Joe', `SF', 1111, NULL))
Attributes in general can be accessed using the dot notation. Attributes of a subtype of a row or column's declared type can be accessed with the TREAT function. For example, in an object view Books_v of Book_typ, you can use TREAT to get the employee id of authors of Employee_typ. (The author column is of Person_typ.)
SELECT TREAT(author AS Employee_typ).empid FROM Books_v;