| Oracle® Database Application Developer's Guide - Object-Relational Features 10g Release 1 (10.1) Part Number B10799-01 |
|
|
View PDF |
| Oracle® Database Application Developer's Guide - Object-Relational Features 10g Release 1 (10.1) Part Number B10799-01 |
|
|
View PDF |
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:
This section describes SQL object types and references, including:
You can create a SQL object type with the CREATE TYPE statement. An example of creating an object type is shown in Example 1-1.
A table column, 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 which contains the person_typ object type defined in Example 1-1.
CREATE TABLE contacts ( contact person_typ, contact_date DATE );
The statement
INSERT INTO contacts VALUES ( person_typ (NULL, NULL, NULL), '24 Jun 2003' );
gives a different result from
INSERT INTO contacts VALUES ( NULL, '24 Jun 2003' );
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 contact column and sets each of the object's attributes to NULL. In the second case, Oracle sets the person_typ 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.
Lengths for character types CHAR and VARCHAR2 may be specified as a number of characters, instead of bytes, in object attributes and collections even if some of the characters consist of multiple bytes.
To specify character-denominated lengths for CHAR and VARCHAR2 attributes, you add a qualifier char to the length specification.
Like CHAR and VARCHAR2, NCHAR and NVARCHAR2 may also be used as attribute types in objects and collections. These types are always implicitly measured in terms of characters, so no char qualifier is used.
For example, the following statement creates an object with both a character-length VARCHAR2 attribute and an NCHAR attribute:
CREATE TYPE employee_typ AS OBJECT ( name VARCHAR2(30 char), language NCHAR(10), phone VARCHAR2(20) ); /
For CHAR and VARCHAR2 attributes whose length is specified without a char qualifier (for example, the phone attribute), the default unit of measure characters or bytes is determined by whether the NLS_LENGTH_SEMANTICS initialization parameter is set to CHAR or BYTE.
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 idno column of the object table person_extent:
CREATE TYPE location_typ (
building_no NUMBER,
city VARCHAR2(40) );
/
CREATE TYPE office_typ AS OBJECT (
office_id VARCHAR(10),
office_loc location_typ,
occupant person_typ );/
CREATE TABLE office_tab OF office_typ (
office_id PRIMARY KEY );
The department_mgrs table in the next example has a column whose type is the object type location_typ defined in the previous example. The example defines constraints on scalar attributes of the location_typ objects that appear in the dept_loc column of the table.
CREATE TABLE department_mgrs (
dept_no NUMBER PRIMARY KEY,
dept_name CHAR(20),
dept_mgr person_typ,
dept_loc location_typ,
CONSTRAINT dept_loc_cons1
UNIQUE (dept_loc.building_no, dept_loc.city),
CONSTRAINT dept_loc_cons2
CHECK (dept_loc.city IS NOT NULL) );
INSERT INTO department_mgrs VALUES
( 101, 'Physical Sciences',
person_typ(65,'Vrinda Mills', '1-800-555-4412'),
location_typ(300, 'Palo Alto'));
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_loc (
dept_no NUMBER PRIMARY KEY,
dept_name CHAR(20),
dept_addr location_typ );
CREATE INDEX i_dept_addr1
ON department_loc (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 office_tab table defined in "Constraints for Object Tables".
CREATE TABLE movement (
idno NUMBER,
old_office location_typ,
new_office location_typ );
CREATE TRIGGER trigger1
BEFORE UPDATE
OF office_loc
ON office_tab
FOR EACH ROW
WHEN (new.office_loc.city = 'Redwood Shores')
BEGIN
IF :new.office_loc.building_no = 600 THEN
INSERT INTO movement (idno, old_office, new_office)
VALUES (:old.occupant.idno, :old.office_loc, :new.office_loc);
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.
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 dept_addr is a column in the department_loc table and old_office is a column in the movement table, you can write:
SELECT * FROM department_loc WHERE EXISTS (SELECT * FROM movement WHERE dept_addr = old_office);
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. For example:
SELECT * FROM department_loc WHERE EXISTS (SELECT * FROM movement WHERE department_loc.dept_addr = movement.old_office); SELECT * FROM department_loc d WHERE EXISTS (SELECT * FROM movement m WHERE d.dept_addr = m.old_office);
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 two tables that contain the person_typ object type. person_obj_table is an object table for objects of type person_typ, and contacts is a relational table that contains a column of an object type.
The following queries show some correct and incorrect ways to reference attribute idno:
SELECT idno FROM person_obj_table; --Correct SELECT contact.idno FROM contacts; --IllegalSELECT contacts.contact.idno FROM contacts; --Illegal SELECT p.contact.idno FROM contacts p; --Correct
In the first SELECT statement, idno is the name of a column of person_obj_table. It references this top-level attribute directly, without using the dot notation, so no table alias is required.
In the second SELECT statement, idno is the name of an attribute of the person_typ object in the column named contact. This reference uses the dot notation and so requires a table alias, as shown in the fourth SELECT statement.
The third 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 objtest schema, department_loc table, dept_addr column, and city attribute of that column. But the expression is incorrect because department_loc is a table name, not an alias.
objtest.department_loc.dept_addr.city
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.
|
Note: Oracle recommends that you define table aliases in allUPDATE, DELETE, and SELECT statements and subqueries and use them to qualify column references whether or not the columns contain object types. |
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:
Connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table
You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Data Cartridge Developer's Guide.
Use database links within PL/SQL code to declare a local variable of a remote user-defined type
Convey a user-defined type argument or return value in a PL/SQL remote procedure call.
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();
In SQL, the parentheses are required for all method calls. Unlike with PL/SQL functions and procedures, SQL requires parentheses for method calls 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.
The topics described in this section are:
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_typ AS OBJECT (
num INTEGER,
den INTEGER,
MEMBER PROCEDURE normalize_proc);
/
CREATE TYPE BODY rational_typ AS
MEMBER PROCEDURE normalize_proc 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_proc;
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. The default behavior does not include the NOCOPY compiler hint.
You can invoke a member method using the dot notation object_variable.method(). This notation specifies the object on which to invoke the method, then the method to call. Any parameters must be placed inside the required parentheses.
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, such as a number or date. Example 1-1 contains a simple map method.
From the standpoint of writing one, a map method is simply a parameter-less 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, UNION, and ORDER BY clauses which require sorting by rows.
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 greater than (>) operator.
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.
|
See Also: "Equal and Not Equal Comparisons" for the use of map methods when comparing collections that contain object types |
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 locations by building number:
CREATE TYPE location_typ AS OBJECT (
building_no NUMBER,
city VARCHAR2(40),
ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER );/
CREATE TYPE BODY location_typ AS
ORDER MEMBER FUNCTION match (l location_typ) RETURN INTEGER IS
BEGIN
IF building_no < l.building_no THEN
RETURN -1; -- any negative number will do
ELSIF building_no > l.building_no 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, such as:
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.
The system implicitly defines a constructor function called the attribute value constructor for all object types that have attributes.
You can also define constructor functions of your own called user-defined constructors to create and initialize objects of such types. Attribute value constructors are convenient to use because they already exists, but user-defined constructors have some important advantages with respect to type evolution.
|
See Also: "Advantages of User-Defined Constructors" for information on user-defined constructors and their advantages |
The name of the constructor method is just the name of the object type, as shown in the following simple example:
person_typ (1, 'John Smith', '1-800-555-1212'),
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.
For example, consider the people_typ object type that is a table of person_typ that is defined in Example 1-1.
CREATE TYPE people_typ AS TABLE OF person_typ; /
|
See Also: "Constructor Methods for Collections" for information on user-defined constructors and their advantages |
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 person_typ object type you might derive the specialized types student_typ and employee_typ. Each of these subtypes is still at bottom a person_typ, but a special kind of person. 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 person_typ object type has the three attributes idno, name, and phone and the method get_idno(), then any object type that is derived from person_typ will have these same three attributes and a method get_idno(). 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:
Add new attributes that its parent supertype does not have.
For example, you might specialize student_typ as a special kind of person_typ by adding to its definition an attribute for major. A subtype cannot drop or change the type of an attribute it inherited from its parent; it can only add new attributes.
Add entirely new methods that the parent does not have.
Change the implementation of some of the methods a subtype inherits from its parent so that the subtype's version executes different code from the parent's.
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. See "Inheriting, Overloading, and Overriding Methods".
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 is a particular kind of that type. If the general definition of person_typ ever changes, the definition of student_typ changes also.
The 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.
The definition of an object type 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 ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)) 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 declared as final and 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 person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20) FINAL MAP MEMBER FUNCTION get_idno RETURN 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 (
dept_id 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 (
emp_id 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 part_time_student_typ under student_typ. The new subtype inherits all the attributes and methods of student_typ and adds another attribute.
CREATE TYPE part_time_student_typ UNDER student_typ ( number_hours 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 person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20), NOT INSTANTIABLE MEMBER FUNCTION get_idno() 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. In the following example, the ALTER TYPE statement makes person_typ instantiable:
CREATE TYPE person_typ AS OBJECT ( idno NUMBER, name VARCHAR2(30), phone VARCHAR2(20)) NOT INSTANTIABLE NOT FINAL;/ ALTER TYPE person_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 in a subtype is either overriding, in the case of member methods, or hiding, in the case of static methods.
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).
In the following example, Subtype MySubType_typ creates an overload of draw():
CREATE TYPE MyType_typ AS OBJECT (..., MEMBER PROCEDURE draw(x NUMBER), ...) NOT FINAL; CREATE TYPE MySubType_typ UNDER MyType_typ (..., MEMBER PROCEDURE draw(x VARCHAR2(20)), STATIC FUNCTION bar(...)... ...);
MySubType_typ contains two versions of draw(). One is an inherited version with a NUMBER parameter and the other has a VARCHAR2 parameter.
Overriding and hiding redefine 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 member method calculate_area() to customize it specifically for calculating the area of a circle.
Redefining a method is called overriding when the method that is redefined is a member method; redefining is called hiding when the redefined method is a static method. Overriding and hiding are similar in that, in either case, the version of the method redefined in the subtype eclipses an inherited version of the same name and signature such that the new version is executed instead of the inherited one whenever an instance of the subtype invokes the method. If the subtype itself has subtypes, these inherit the redefined method instead of the original version.
However, with overriding, the system relies on type information contained in the member method s implicit self argument to dynamically choose the correct version of the method to execute. With hiding, the correct version can be identified at compile time, and dynamic dispatch is not necessary. See "Dynamic Method Dispatch".
It is possible that a supertype may contain overloads of a method that is redefined in a subtype. Overloads of a method all have the same name, so the compiler uses the signature of the subtype s method to identify the particular version in the supertype that is superseded. This means that, to override or hide a method, you must preserve its signature.
A subtype that overrides a member method must signal the override with the OVERRIDING keyword in the type definition. No such special keyword is required when a subtype hides a static 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 function_mytype(x NUMBER)... ) NOT FINAL; CREATE TYPE MySubType_typ UNDER MyType_typ (..., OVERRIDING MEMBER PROCEDURE Print(), ...);
As with new methods, you supply the declaration for a method that hides or overrides in a CREATE TYPE BODY statement.
You can override only methods that are not declared to be final in the supertype.
Order methods may appear only in the root type of a type hierarchy: they may not be redefined (overridden) in subtypes.
A static method in a subtype may not redefine a member method in the supertype.
A member method in a subtype may not redefine a static method in the supertype.
If a method being overridden provides default values for any parameters, then the overriding method must provide the same default values for the same parameters.
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.proc() looks first for an implementation of proc() 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.
|
See Also: PL/SQL User's Guide and Reference for information on how subprograms calls are resolved and the dynamic dispatch feature |
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. Object attributes, collection elements and REFs are substitutable. An attribute defined as a REF, type, or collection of type person_typ can hold a REF to, an instance of, or instances of an instance of person_typ or an instance of any subtype of person_typ.
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 type columns are substitutable, and so are object-type rows in object tables and views. In other words, a column or row defined to be of type t can contain instances of t and any of its subtypes.
For example, consider the person_typ type hierarchy introduced in "Creating Subtypes".
An object table of person_typ can contain rows of all three types. You insert an instance of a given type using the constructor for that type in the VALUES clause of the INSERT statement as shown in Example 2-1.
Example 2-1 Inserting Values into Substitutable Columns
CREATE TABLE person_obj_table OF person_typ;
INSERT INTO person_obj_table
VALUES (person_typ(12, 'Bob Jones', '111-555-1212'));
INSERT INTO person_obj_table
VALUES (student_typ(51, 'Joe Lane', '1-800-555-1312', 12, 'HISTORY'));
INSERT INTO person_obj_table
VALUES (part_time_student_typ(52, 'Kim Patel', '1-800-555-1232', 14,
'PHYSICS', 20));
Similarly, in a relational table or view, a substitutable column of type person_typ can contain instances of all three types. The following example inserts a person, a student, and a part-time student in the person_typ column contact:
CREATE TABLE contacts (
contact person_typ,
contact_date DATE );
INSERT INTO contacts
VALUES (person_typ (12, 'Bob Jones', '111-555-1212'), '24 Jun 2003' );
INSERT INTO contacts
VALUES (student_typ(51, 'Joe Lane', '1-800-555-1312', 12, 'HISTORY'),
'24 Jun 2003' );
INSERT INTO contacts
VALUES (part_time_student_typ(52, 'Kim Patel', '1-800-555-1232', 14,
'PHYSICS', 20), '24 Jun 2003' );
A newly created subtype can be stored in any substitutable tables and columns of its supertype, including tables and columns that existed before the subtype was created.
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:
SELECT TREAT(contact AS student_typ).major FROM contacts;
The OBJECT_VALUE and OBJECT_ID pseudocolumns allow you to access and identify the value and OID of a substitutable row in an object table.
In Example 2-2, the categories_tab is a table of category_typ objects. The composite_category_typ and leaf_category_typ types are subtypes of category_typ. Both subtypes contain the category_name, category_description, and category_id attributes of the category_typ supertype. Additionally, leaf_category_typ contains the product_ref_list_typ nested table and composite_category_typ contains the subcategory_ref_list nested table.
Example 2-2 Using OBJECT_VALUE and OBJECT_ID
CREATE TABLE categories_tab OF category_typ
( category_id PRIMARY KEY)
NESTED TABLE TREAT
(OBJECT_VALUE AS leaf_category_typ).product_ref_list
STORE AS product_ref_list_nestedtab
NESTED TABLE TREAT
(OBJECT_VALUE AS composite_category_typ).subcategory_ref_list
STORE AS subcategory_ref_list_nestedtab;
SELECT c.category_id, o.*
FROM categories_tab c,
TABLE (TREAT(c.OBJECT_VALUE AS leaf_category_typ).product_ref_list) o
WHERE c.category_id = 31;
SELECT c.OBJECT_ID, c.category_id, o.COLUMN_VALUE.category_id
FROM categories_tab c,
TABLE (TREAT(c.OBJECT_VALUE AS composite_category_typ).subcategory_ref_list) o
WHERE category_id = 10;
A subtype can have an attribute that is a supertype. For example:
CREATE TYPE student_typ UNDER person_typ (
dept_id NUMBER,
major VARCHAR2(30),
advisor person_typ);
/
However, columns of such types are not substitutable. Similarly, a subtype ST can have a collection attribute whose element type is one of ST's supertypes, but, again, columns of such types are not substitutable. For example, if student_typ had a nested table or varray of person_typ, the student_typ column would not be substitutable.
You can, however, define substitutable columns of subtypes that have REF attributes that reference supertypes. For example, the composite_category_typ subtype shown in Example 2-2 contains the subcategory_ref_list nested table. This table contains subcategory_ref_list_typ which are REFs to category_typ. The subtype was created as follows:
CREATE TYPE subcategory_ref_list_typ
AS TABLE OF REF category_typ;
/
CREATE TYPE composite_category_typ
UNDER category_typ
(
subcategory_ref_list subcategory_ref_list_typ
...
If you create a subtype, any table that already has substitutable columns of the supertype is automatically enabled to store the new subtype as well. This means that your options for creating subtypes are affected by the existence of such tables. If such a table exists, you can only create subtypes that are substitutable, that is, subtypes that Oracle can enable that table to store.
The following example shows an attempt to create a subtype student_typ under person_typ.
CREATE TYPE person_typ AS OBJECT (
idno NUMBER,
name VARCHAR2(30),
phone VARCHAR2(20))
NOT FINAL;/
CREATE TYPE employee_typ UNDER person_typ (
emp_id NUMBER,
mgr VARCHAR2(30))
NOT FINAL;/
CREATE TABLE person_obj_table (p person_typ);
The following statement fails because student_typ has a supertype attribute, and table person_obj_table has a substitutable column p of the supertype.
CREATE TYPE student_typ UNDER person_typ (
advisor person_typ);
/
The following attempt succeeds. This version of the student_typ subtype is substitutable. Oracle automatically enables table person_obj_table to store instances of this new type.
CREATE TYPE student_typ UNDER person_typ (
dept_id NUMBER,
major VARCHAR2(30));/
INSERT INTO person_obj_table
VALUES (student_typ(51, 'Joe Lane', '1-800-555-1312', 12, 'HISTORY'));
You can drop a subtype with the VALIDATE option only if no instances of the subtype are stored in any substitutable column of the supertype.
For example, the following statement fails because an instance of student_typ is stored in substitutable column p of table person_obj_table:
DROP TYPE student_typ VALIDATE;
To drop the type, first delete any of its instances in substitutable columns of the supertype:
DELETE FROM person_obj_table WHERE p IS OF (student_typ); DROP TYPE student_typ VALIDATE;
When creating a table, you can turn off all substitutability on a column or attribute, including embedded attributes and collections nested to any level, with the clause NOT SUBSTITUTABLE AT ALL LEVELS.
In the following example, the clause confines column office of a relational table to storing only office_typ instances and disallows any subtype instances:
CREATE TYPE office_typ AS OBJECT ( office_id VARCHAR(10), location location_typ, occupant person_typ ) NOT FINAL;/ CREATE TABLE dept_office ( dept_no NUMBER, office office_typ) COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS;
With object tables, the clause can be applied to the table as a whole, like this:
CREATE TABLE office_tab OF office_typ NOT SUBSTITUTABLE AT ALL LEVELS;
Alternatively, the clause can also be applied to turn off substitutability in a particular column that is, for a particular attribute of the object type of the table:
CREATE TABLE office_tab OF office_typ COLUMN occupant NOT SUBSTITUTABLE AT ALL LEVELS;
You can specify that the element type of a collection is not substitutable using syntax like the following:
CREATE TABLE people_tab (
people_column people_typ )
NESTED TABLE people_column
NOT SUBSTITUTABLE AT ALL LEVELS STORE AS people_column_nt;
There is no mechanism to turn off substitutability for REF columns.
You can impose a constraint that limits the range of subtypes permitted in an object column or attribute to a particular subtype in the declared type's hierarchy. You do this using an IS OF type constraint.
For example, the following statement creates a table of office_typ in which occupants are constrained to just those persons who are employees:
CREATE TABLE office_tab OF office_typ COLUMN occupant IS OF (ONLY employee_typ);
Although the type office_typ allows authors to be of type person_typ, the column declaration imposes a constraint to store only instances of employee_typ.
You can only use the IS OF type operator to constrain row and column objects to a single subtype (not several), and you must use the ONLY keyword, as in the preceding example.
You can use either IS OF type or NOT SUBSTITUTABLE AT ALL LEVELS to constrain an object column, but you cannot use both.
In an existing table, you can change an object column from SUBSTITUTABLE to NOT SUBSTITUTABLE (or from NOT SUBSTITUTABLE to SUBSTITUTABLE) by using an ALTER TABLE statement. To do so, you specify the clause [NOT] SUBSTITUTABLE AT ALL LEVELS for the particular column.
You can modify substitutability only for a specific column; you cannot modify substitutability for an object table as a whole.
The following statement makes column office substitutable:
ALTER TABLE dept_office MODIFY COLUMN office SUBSTITUTABLE AT ALL LEVELS;
The following statement makes the column not substitutable. Notice that it also uses the FORCE keyword. This keyword causes any hidden columns containing typeid information or data for subtype attributes to be dropped:
ALTER TABLE dept_office MODIFY COLUMN office NOT SUBSTITUTABLE AT ALL LEVELS FORCE;
If the FORCE keyword is not used when a column is made not substitutable, the column and all attributes of the type must be FINAL or the ALTER TABLE statement will fail.
A VARRAY column can be modified from SUBSTITUTABLE to NOT SUBSTITUTABLE only if the element type of the varray is final itself and has no embedded types (in its attributes or in their attributes, and so on) that are not final.
See "Hidden Columns for Substitutable Columns and Tables" for more information about hidden columns for typeids and subtype attributes.
You can change the substitutability of only one column at a time with an ALTER TABLE statement. To change substitutability for multiple columns, you must issue multiple statements.
In an object table, you can modify substitutability for a column only if substitutability was not explicitly set at the table level, for the entire table, when the table was created.
For example, the following attempt to modify substitutability for column address succeeds because substitutability has not been explicitly turned on or off at the table level in the CREATE TABLE statement:
CREATE TABLE office_tab OF office_typ; ALTER TABLE office_tab MODIFY COLUMN occupant NOT SUBSTITUTABLE AT ALL LEVELS FORCE;
However, in the following example, substitutability is explicitly set at the table level, so the attempt to modify the setting for column address fails:
CREATE TABLE office_tab OF office_typ NOT SUBSTITUTABLE AT ALL LEVELS; /* Following SQL statement generates an error: */ ALTER TABLE office_tab MODIFY COLUMN occupant SUBSTITUTABLE AT ALL LEVELS FORCE;
A column whose substitutability is already constrained by an IS OF type operator cannot have its substitutability modified with a [NOT] SUBSTITUTABLE AT ALL LEVELS clause. See "Constraining Substitutability" for information about IS OF type.
The assignment rules described in this section apply to INSERT/UPDATE statements, the RETURNING clause, function parameters, and PL/SQL variables.
Substitutability is the ability of a subtype to stand in for one of its supertypes. An attempt to perform a substitution in the other direction, to substitute a supertype for a subtype, raises an error at compile time.
An assignment of a source of type source_typ to a target of type target_typ must be of one of the following two patterns:
Case 1: source_typ and target_typ are the same type
Case 2: source_typ is a subtype of target_typ (widening)
Case 2 illustrates widening. Widening is an assignment in which the declared type of the source is more specific than the declared type of the target. For example, assigning an employee instance to a variable of person type.
Intuitively, the idea here is that you are regarding an employee as a person. An employee is a more narrowly defined, specialized kind of person, so you can put an employee in a slot meant for a person if you do not mind ignoring whatever extra specialization makes that person an employee. All employees are persons, so a widening assignment always works.
To illustrate widening, suppose that you have the following table:
TABLE T(pers_col person_typ, emp_col employee_typ, stu_col student_typ)
The following assignments show widening. The assignments are valid unless perscol has been defined to be not substitutable.
UPDATE T set pers_col = emp_col;
The following is a PL/SQL example:
declare var1 person_typ; var2 employee_typ; begin var1 := var2; end;
Besides widening, there is also narrowing. Narrowing is the reverse of widening. It involves regarding a more general, less specialized type of thing, such as a person, as a more narrowly defined type of thing, such as an employee. Not all persons are employees, so a particular assignment like this works only if the person in question actually happens to be an employee.
To do a narrowing assignment, you must use the TREAT function to test that the source instance of the more general declared type is in fact an instance of the more specialized target type and can therefore be operated on as such. The TREAT function does a runtime check to confirm this and returns NULL if the source value the person in question is not of the target type or one of its subtypes.
For example, the following UPDATE statement sets values of person_typ in column perscol into column empcol of employee_typ. For each value in perscol, the assignment succeeds only if that person is also an employee. If person George is not an employee, TREAT returns NULL, and the assignment returns NULL.
UPDATE T set emp_col = TREAT(pers_col AS employee_typ);
The following statement attempts to do a narrowing assignment without explicitly changing the declared type of the source value. The statement will return an error:
UPDATE T set emp_col = pers_col;
In assignments of expressions of a collection type, the source and target must be of the same declared type. Neither widening nor narrowing is permitted. However, a subtype value can be assigned to a supertype collection. For example, suppose we have the following collection types:
CREATE TYPE person_set AS TABLE OF person_typ; / CREATE TYPE student_set AS TABLE OF student_typ; /
Expressions of these different collection types cannot be assigned to each other, but a collection element of student_typ can be assigned to a collection of PersonSet type:
DECLARE var1 person_set; var2 student_set; elem1 person_typ; elem2 student_typ; begin var1 := var2; /* ILLEGAL - collections not of same type */ var1 := person_set (elem1, elem2); /* LEGAL : Element is of subtype */
This section discusses the comparison operators used in SQL conditions.
Two object instances can be compared if, and only if, they are both of the same declared type, or one is a subtype of the other.
Map methods and order methods provide the mechanism for comparing objects. You optionally define one or the other of these in an object type to specify the basis on which you want objects of that type to be compared. If a method of either sort is defined, it is called automatically whenever objects of that type or one of its subtypes need to be compared.
If a type does not define either a map method or an order method, object variables of that type can be compared 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.
Several functions and operators are particularly useful for working with objects and references to objects:
Examples are given throughout this book.
In PL/SQL the VALUE, REF and DEREF functions can appear only in a SQL statement.
CAST converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value.
For example:
CREATE TYPE person_list_typ AS TABLE OF person_typ;/ SELECT CAST(COLLECT(contact) AS person_list_typ) FROM contacts;
A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function.
The DEREF function in a SQL statement returns the object instance corresponding to a REF. The object instance returned by DEREF may be of the declared type of the REF or any of its subtypes.
For example, the following statement returns person_typ objects from the table contact_ref.
SELECT DEREF(c.contact_ref), c.contact_date FROM contacts_ref c;
See "Dereferencing REFs".
The IS OF type predicate tests object instances for the level of specialization of their type.
For example, the following query retrieves all student instances (including any subtypes of students) stored in the person_obj_table table.
SELECT VALUE(p) FROM person_obj_table p WHERE VALUE(p) IS OF (student_typ);
For any object that is not of a specified subtype, or a subtype of a specified subtype, IS OF returns FALSE. Subtypes of a specified subtype are just more specialized versions of the specified subtype. If you want to exclude such subtypes, you can use the ONLY keyword. This keyword causes IS OF to return FALSE for all types except the specified types.
In the following example, the statement tests objects in object table person_obj_table, which contains persons, employees, and students, and returns REFs just to objects of the two specified person subtypes employee_typ, student_typ, and their subtypes, if any:
SELECT REF(p) FROM person_obj_table p WHERE VALUE(p) IS OF (employee_typ, student_typ);
Here is a similar example in PL/SQL. The code does something if the person is an employee or student:
DECLARE var person_t; BEGIN if var is of (employee_t, student_t) then ... END;
The following statement returns only students whose most specific or specialized type is student_typ. If the table or view contains any objects of a subtype of student_typ, such as part_time_student_typ, these are excluded. The example uses the TREAT function to convert objects that are students to student_typ from the declared type of the view, person_typ:
SELECT TREAT(VALUE(p) AS student_typ) FROM person_obj_table p WHERE VALUE(p) IS OF(ONLY student_typ);
To test the type of the object that a REF points to, you can use the DEREF function to dereference the REF before testing with the IS OF type predicate.
For example, if contact_ref is declared to be REF person_typ, you can get just the rows for students as follows:
SELECT * FROM contacts_ref WHERE DEREF(contact_ref) IS OF (student_typ);
The REF function in a SQL statement takes as an argument a correlation name for an object table or view and returns a reference (a REF) to an object instance from that table or view. The REF function may return references to objects of the declared type of the table, view, or any of its subtypes. For example, the following statement returns the references to all persons, including references to students and employees, whose idno attribute is 12:
SELECT REF(p) FROM person_obj_table p WHERE p.idno = 12;
The SYS_TYPEID function can be used in a query to return the typeid of the most specific type of the object instance passed to the function as an argument.
The most specific type of an object instance is the type to which the instance belongs that is farthest removed from the root type. For example, if Tim is a part-time student, he is also a student and a person, but his most specific type is part-time student.
The function returns the typeids from the hidden type discriminant column that is associated with every substitutable column. The function returns a null typeid for a final, root type.
The syntax of the function is:
SYS_TYPEID( object_type_value )
Function SYS_TYPEID may be used only with arguments of an object type. Its primary purpose is to make it possible to build an index on a hidden type discriminant column.
All types that do belong to a type hierarchy are assigned a non-null typeid that is unique within the type hierarchy. Types that do not belong to a type hierarchy have a null typeid.
Every type except a final root type belongs to a type hierarchy. A final root type has no types related to it by inheritance:
It cannot have subtypes derived from it because it is final
It is not itself derived from some other type because it is a root type, so it does not have any supertypes.
|
See Also: "Hidden Columns for Substitutable Columns and Tables" for more information about type discriminant columns |
For an example of SYS_TYPEID, consider the substitutable object table person_obj_table, of person_typ. person_typ is the root type of a hierarchy that has student_typ as a subtype and part_time_student_typ as a subtype of student_typ. See Example 2-1.
The following query uses SYS_TYPEID. It gets the name attribute and typeid of the object instances in the person_obj_table table. Each of the instances is of a different type:
SELECT name, SYS_TYPEID(VALUE(p)) typeid FROM person_obj_table p;
|
See Also: "Hidden Columns for Substitutable Columns and Tables" for information about the type discriminant and other hidden columns |
Table functions are functions that produce a collection of rows, a nested table or a varray, that can be queried like a physical database table or assigned to a PL/SQL collection variable. You can use a table function like the name of a database table, in the FROM clause of a query, or like a column name in the SELECT list of a query.
A table function can take a collection of rows as input. An input collection parameter can be either a collection type, such as a VARRAY or a PL/SQL table, or a REF CURSOR.
Use PIPELINED to instruct Oracle to return the results of a table function iteratively. A table function returns a nested table or varray collection type. You query table functions by using the TABLE keyword before the function name in the FROM clause of the query.
The TREAT function does a runtime check to confirm that an expression can be operated on as if it were of a different specified type in the hierarchy normally, a subtype of the expression s declared type. In other words, the function attempts to treat a supertype instance as a subtype instance to treat a person as a student, for example. Whether this can be done in a given case depends on whether the person in question actually is a student (or student subtype, such as a part-time student). If the person is a student, then the person is returned as a student, with the additional attributes and methods that a student may have. If the person happens not to be a student, TREAT returns NULL in SQL.
The two main uses of TREAT are:
In narrowing assignments, to modify the type of an expression so that the expression can be assigned to a variable of a more specialized type in the hierarchy: in other words, to set a supertype value into a subtype.
To access attributes or methods of a subtype of the declared type of a row or column
A substitutable object table or column of type T has a hidden column for every attribute of every subtype of T. These hidden columns are not listed by a DESCRIBE statement, but they contain subtype attribute data. TREAT enables you to access these columns.
The following example shows TREAT used in an assignment where a column of person type is set into a column of employee type. For each row in perscol, TREAT returns an employee type or NULL, depending on whether the given person happens to be an employee.
UPDATE T set empcol = TREAT(perscol AS employee_typ);
In the next example, TREAT returns all (and only) student_typ instances from person_obj_table of type person_typ, a supertype of student_typ. The statement uses TREAT to modify the type of p from person_typ to student_typ.
SELECT TREAT(VALUE(p) AS student_typ) FROM person_obj_table p;
For each p, the TREAT modification succeeds only if the most specific or specialized type of the value of p is student_typ or one of its subtypes. If p is a person who is not a student, or if p is NULL, TREAT returns NULL in SQL or, in PL/SQL, raises an exception.
You can also use TREAT to modify the declared type of a REF expression. For example:
SELECT TREAT(REF(p) AS REF student_typ) FROM person_obj_table p;
The previous example returns REFs to all student_typ instances. In SQL it returns NULL REFs for all person instances that are not students, and in PL/SQL it raises an exception.
Perhaps the most important use of TREAT is to access attributes or methods of a subtype of a row or column's declared type. The following query retrieves the major attribute of all persons, students and part-time students, who have this attribute. NULL is returned for persons who are not students:
SELECT name, TREAT(VALUE(p) AS student_typ).major major FROM person_obj_table p;
The following query will not work because major is an attribute of student_typ but not of person_typ, the declared type of table persons:
SELECT name, VALUE(p).major major FROM person_obj_table p;
The following is a PL/SQL example:
DECLARE var Person_t; BEGIN TREAT(var as Employee_t).raise_salary; END;
In a SQL statement, the VALUE function takes as its argument a correlation variable (table alias) for an object table or object view and returns object instances corresponding to rows of the table or view. The VALUE function may return instances of the declared type of the row or any of its subtypes. For example, the following query returns all persons, including students and employees, from table person_obj_table of person_typ:
SELECT VALUE(p) FROM person__obj_table p;
To retrieve only part time students, that is, instances whose most specific type is part_time_student_typ, use the ONLY keyword to confine the selection:
SELECT VALUE(p) FROM person_obj_table p WHERE VALUE(p) IS OF (ONLY part_time_student_typ);
The following example shows VALUE used to return object instance rows for updating:
UPDATE TABLE(SELECT d.dept_emps FROM department_persons d
WHERE d.dept_no = 101) p
SET VALUE(p) = person_typ(2, 'Diane Smith', '1-800-555-1243')
WHERE p.idno = 2;
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|