博客

Null Objects and Attributes

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. In

Example 2-1

, consider the

contacts

table which contains the

person_typ

object type.

CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) );
/

CREATE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' - '  || name || ' - '  || phone);
  END;
END;
/
CREATE TABLE contacts (
  contact        person_typ,
  contact_date   DATE );

INSERT INTO contacts VALUES (
  person_typ (NULL, NULL, NULL), '24 Jun 2003' );

INSERT INTO contacts VALUES (
  NULL, '24 Jun 2003' );

The two INSERT statements give two different results. 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.

Character Length Semantics

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, the default unit of measure characters or bytes is determined by whether the



NLS_LENGTH_SEMANTICS

initialization parameter is set to

CHAR

or

BYTE

.

Constraints for Object Tables

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

REF

s that are not scoped.

Example 2-3

and

Example 2-4

illustrate the possibilities.

Example 2-3

places a

PRIMARY


KEY

constraint on the

idno

column of the object table

person_extent

.

CREATE TYPE location_typ AS OBJECT (
  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

Example 2-4

has a column whose type is the object type

location_typ

defined in

Example 2-3

. 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'));

Indexes for Object Tables

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. For an example of an index on a nested table, see

Example 3-4

.

You can define indexes on leaf-level scalar attributes of




column objects, as shown in

Example 2-5

. 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);

INSERT INTO department_loc VALUES
          ( 101, 'Physical Sciences',
           location_typ(300, 'Palo Alto'));
INSERT INTO department_loc VALUES 
          ( 104, 'Life Sciences', 
           location_typ(400, 'Menlo Park'));
INSERT INTO department_loc VALUES 
          ( 103, 'Biological Sciences', 
           location_typ(500, 'Redwood Shores'));

Wherever Oracle expects a column name in an index definition, you can also specify a scalar attribute of an object column.

Triggers for Object Tables

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.

Example 2-6

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;/

INSERT INTO movement VALUES 
   ( 101, location_typ(300, 'Palo Alto'),
     location_typ(400, 'Menlo Park'));

Rules for REF Columns and Attributes

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.

Name Resolution

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 use the following:

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.

Restriction on Using User-Defined Types with a Remote Database

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 Database 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.

Member Methods

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. In

Example 2-8

the code shows a method declaration that takes advantage of

SELF

to omit qualification of the attributes

hgt

,

len

, and

wth

.

CREATE TYPE solid_typ AS OBJECT (
  len    INTEGER,
  wth    INTEGER,
  hgt    INTEGER,
  MEMBER FUNCTION surface RETURN INTEGER,
  MEMBER FUNCTION volume RETURN INTEGER,
  MEMBER PROCEDURE display (SELF IN OUT NOCOPY solid_typ) );
/

CREATE TYPE BODY solid_typ AS
  MEMBER FUNCTION volume RETURN INTEGER IS
  BEGIN
    RETURN len * wth * hgt;
 -- RETURN SELF.len * SELF.wth * SELF.hgt; -- equivalent to previous line 
  END;
  MEMBER FUNCTION surface RETURN INTEGER IS
  BEGIN -- not necessary to include SELF prefix in following line
    RETURN 2 * (len * wth + len * hgt + wth * hgt);
  END;
  MEMBER PROCEDURE display (SELF IN OUT NOCOPY solid_typ) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Length: ' || len || ' - '  || 'Width: ' || wth 
                          || ' - '  || 'Height: ' || hgt);
    DBMS_OUTPUT.PUT_LINE('Volume: ' || volume || ' - ' || 'Surface area: ' 
                          || surface);
  END;
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. See also

“Using SELF IN OUT NOCOPY with Member Procedures”

.

Methods for Comparing Objects

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

person_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.

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;
/

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;/

Static Methods

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()


See

“Static Methods”

for information on design considerations.

Constructor Methods

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.

Consider the

person_typ

object type that is defined in

Example 2-1

. The name of the constructor method is simply the name of the object type, as shown in the following:


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:

CREATE TABLE people_tab OF person_typ;

INSERT INTO people_tab VALUES (
       person_typ(101, 'John Smith', '1-800-555-1212') );

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 exist, but user-defined constructors have some important advantages with respect to type evolution. See

“Advantages of User-Defined Constructors”

for information on user-defined constructors and their advantages. See

“Constructor Methods for Collections”

for information on user-defined constructors for collections.

External Implemented Methods

You can use PL/SQL to invoke external subprograms that have been written in other languages. This provides access to the strengths and capabilities of those languages.

Types and Subtypes

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.

FINAL and NOT FINAL Types and Methods

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, as shown in

Example 2-11

.

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 can also 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.

Example 2-12

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;
/

See

“Redefining Methods”

.

Creating Subtypes With Overriding Methods

You can create a


subtype using a

CREATE


TYPE

statement that specifies the immediate parent of the subtype with an



UNDER

clause.

Example 2-13

shows the creation of the parent or supertype

person_typ

object;

Example 2-14

,

Example 2-15

, and

Example 2-16

show the definition of the subtypes.

Note the methods that are created in the supertype body of

Example 2-13

. In

Example 2-14

,

Example 2-15

, and

Example 2-16

, the

show()

function of the parent type is








overridden to specifications for each subtype. At the same time, the static

show_super()

function is designed so that it can be called as it is in the supertype from every subtype under the

person_typ

parent object.

CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  STATIC FUNCTION show_super (person_obj in person_typ) RETURN VARCHAR2,
  MEMBER FUNCTION show RETURN VARCHAR2)
  NOT FINAL;
/

CREATE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;

-- static function that can be called by subtypes
 STATIC FUNCTION show_super (person_obj in person_typ) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Id: ' || TO_CHAR(person_obj.idno) || ', Name: ' || person_obj.name;
  END;

-- function that can be overriden by subtypes 
  MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN person_typ.show_super ( SELF );
  END; 

END;
/

Example 2-14

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. In addition, the

show()

function is overridden to display the new attribute

major

. 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.

CREATE TYPE student_typ UNDER person_typ (
    dept_id NUMBER,
    major VARCHAR2(30),
    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2)
    NOT FINAL;
/

CREATE TYPE BODY student_typ AS
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN person_typ.show_super ( SELF ) || ' -- Major: ' || major ;
  END;
  
END;
/

A type can have multiple child subtypes, and these can also have subtypes.

Example 2-15

creates another subtype

employee_typ

under

person_typ

.

CREATE TYPE employee_typ UNDER person_typ (
    emp_id NUMBER, 
    mgr VARCHAR2(30),
    OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);
/

CREATE TYPE BODY employee_typ AS
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN person_typ.show_super ( SELF ) || ' -- Employee Id: ' 
           || TO_CHAR(emp_id) || ', Manager: ' || mgr ;
  END;
  
END;
/

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.

Example 2-16

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,
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2);
/

CREATE TYPE BODY part_time_student_typ AS
  OVERRIDING MEMBER FUNCTION show RETURN VARCHAR2 IS
  BEGIN
    RETURN person_typ.show_super ( SELF ) || ' -- Major: ' || major ||
           ', Hours: ' || TO_CHAR(number_hours);
  END;
  
END;
/

You can create a table that contains the supertype and subtypes and populate the table as shown with the

person_obj_table

in

Example 2-17

.

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 (employee_typ(55, 'Jane Smith', '1-800-555-7765', 
                       100, 'Jennifer Nelson'));

INSERT INTO person_obj_table  
  VALUES (part_time_student_typ(52, 'Kim Patel', '1-800-555-1232', 14,
         'PHYSICS', 20));

You can call the

show()

function for the supertype and subtypes in the table with the following:

SELECT p.show() FROM person_obj_table p;

With the table populated as illustrated in

Example 2-17

, the output is similar to:

Id: 12, Name: Bob Jones

Id: 51, Name: Joe Lane — Major: HISTORY

Id: 55, Name: Jane Smith — Employee Id: 100, Manager: Jennifer Nelson

Id: 52, Name: Kim Patel — Major: PHYSICS, Hours: 20

Note the overridden

show()

function displays an output specific to the supertype or subtype while the results of the static

show_super()

are constant.

NOT INSTANTIABLE Types and Methods

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. The following pseudocode provides an 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, as shown in

Example 2-18

.

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.

Inheriting, Overloading, and Overriding Methods

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.

See the examples in

“Creating Subtypes With Overriding Methods”

and

Example 7-7

.

Dynamic Method Dispatch

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.

Substituting Types in a Type Hierarchy

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 subtype 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



REF

s 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

REF

s 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. See

“Turning Off Substitutability in a New Table”

and

“Constraining Substitutability”

.

Column and Row Substitutability

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 With Overriding Methods”

. 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-17

.

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;

See

“TREAT”

.

CREATE TABLE person_obj_table OF person_typ;

INSERT INTO person_obj_table
  VALUES (person_typ(20, 'Bob Jones', '111-555-1212'));

SELECT p.object_id, p.object_value FROM person_obj_table p;

CREATE TYPE student_typ UNDER person_typ (
    dept_id   NUMBER,
    major     VARCHAR2(30),
    advisor   person_typ);
/

Creating Subtypes After Creating Substitutable Columns

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));
/

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 ( -- incorrect CREATE subtype
    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'));

Dropping Subtypes After Creating Substitutable Columns

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 -- incorrect DROP TYPE ; 

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;

Turning Off Substitutability in a New Table

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:

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.

Constraining Substitutability

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.

Modifying Substitutability

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.

Restrictions on Modifying Substitutability

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  -- incorrect ALTER;

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


.

Assignments Across Types

The




assignment rules described in this section apply to

INSERT/UPDATE

statements, the

RETURNING

clause, function parameters, and PL/SQL variables.

DECLARE
  var1 person_typ;
  var2 employee_typ;
BEGIN
  var2 := employee_typ(55, 'Jane Smith', '1-800-555-7765', 100, 'Jennifer Nelson');
  var1 := var2;
END;
/

CREATE TYPE person_set AS TABLE OF person_typ;
/

CREATE TYPE student_set AS TABLE OF student_typ;
/

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 */
END;
/

Comparisons of Objects, REF Variables, and Collections

This section discusses the comparison operators used in SQL conditions.

CAST




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;

For more information about the SQL

CAST

function, see


Oracle Database SQL Reference


.

CURSOR

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.

For more information about the SQL

CURSOR

expression, see


Oracle Database SQL Reference


.

DEREF

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”

. For more information about the SQL

DEREF

function, see


Oracle Database SQL Reference


.

IS OF

type

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

REF

s 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_typ; 
BEGIN 
  var := employee_typ(55, 'Jane Smith', '1-800-555-7765', 100, 'Jennifer Nelson');
  IF var IS OF (employee_typ, student_typ) THEN 
     DBMS_OUTPUT.PUT_LINE('Var is an employee_typ or student_typ object.');
  ELSE
     DBMS_OUTPUT.PUT_LINE('Var is not an employee_typ or student_typ object.');
  END IF;
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);

For more information about the SQL

IS


OF



type


condition, see


Oracle Database SQL Reference


.

REF

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;

For more information about the SQL

REF

function, see


Oracle Database SQL Reference


.

SYS_TYPEID

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

“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-17

.

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

“Hidden Columns for Substitutable Columns and Tables”

for information about the type discriminant and other hidden columns. For more information about the SQL

SYS


TYPEID

function, see


Oracle Database SQL Reference


.

TABLE()

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.

For information on

TABLE()

functions, see


Oracle Database Data Cartridge Developer’s Guide


and


Oracle Database PL/SQL User’s Guide and Reference


.

TREAT

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

REF

s to all

student_typ

instances. In SQL it returns

NULL


REF

s 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 -- incorrect;

The following is a PL/SQL example:

DECLARE 
  var person_typ; 
BEGIN 
  var := employee_typ(55, 'Jane Smith', '1-800-555-7765', 100, 'Jennifer Nelson');
  DBMS_OUTPUT.PUT_LINE(TREAT(var AS employee_typ).mgr);
END;
/

See

“Assignments Across Types”

for information on using

TREAT

in assignments. For more information about the SQL

TREAT

function, see


Oracle Database SQL Reference


.

VALUE

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. In

Example 2-34

the 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);

In the following example,

VALUE

is used to update a object instance in an object table:

UPDATE person_obj_table p
   SET VALUE(p) = person_typ(12, 'Bob Jones', '1-800-555-1243')
   WHERE p.idno = 12;