PL/SQL User's Guide and Reference Release 8.0 A58236-01 |
Library | Product | Contents | Index

# 9
Object Types

... It next will be right
To describe each particular batch:
Distinguishing those that have feathers, and bite,
From those that have whiskers, and scratch.

Lewis Carroll

Object-oriented programming is rapidly gaining acceptance because it can reduce the cost and time required to build complex applications. In PL/SQL, object-oriented programming is based on object types. They provide abstract templates for real-world objects, and so are an ideal modeling tool. They also provide black-box encapsulation like an integrated component that can be plugged into various electronic devices. To plug an object type into your programs, you need to know only what it does, not how it works.

##### Major Topics

The Role of Abstraction
What Is an Object Type?
Why Use Object Types?
Structure of an Object Type
Components of an Object Type
Defining Object Types
Declaring and Initializing Objects
Accessing Attributes
Calling Constructors and Methods
Sharing Objects
Manipulating Objects

## The Role of Abstraction

An abstraction is a high-level description or model of a real-world entity. Abstractions keep our daily lives manageable. They help us reason about an object, event, or relationship by suppressing irrelevant detail. For example, to drive a car, you need not know how its engine works. A simple interface consisting of a gearshift, steering wheel, accelerator, and brake, lets you use the car effectively. The details of what happens under the hood are not important for day-to-day driving.

Abstractions are central to the discipline of programming. For example, you use procedural abstraction when you suppress the details of a complex algorithm by writing a procedure and passing it parameters. A single procedure call hides the details of your implementation. To try a different implementation, you simply replace the procedure with another having the same name and parameters. Thanks to abstraction, programs that call the procedure need not be modified.

You use data abstraction when you specify the datatype of a variable. The datatype stipulates a set of values and a set of operations appropriate for those values. For instance, a variable of type `POSITIVE` can hold only positive integers, and can only be added, subtracted, multiplied, and so on. To use the variable, you need not know how PL/SQL stores integers or implements arithmetic operations; you simply accept the programming interface.

Object types are a generalization of the built-in datatypes found in most programming languages. PL/SQL provides a variety of built-in scalar and composite datatypes, each of which is associated with a set of predefined operations. A scalar type (such as `CHAR`) has no internal components. A composite type (such as `RECORD`) has internal components that can be manipulated individually. Like the `RECORD` type, an object type is a composite type. However, its operations are user-defined, not predefined.

Currently, you cannot define object types within PL/SQL. They must be `CREATE`d and stored in an Oracle database, where they can be shared by many programs. A program that uses object types is called a client program. It can declare and manipulate an object without knowing how the object type represents data or implements operations. This allows you to write the program and object type separately, and to change the implementation of the object type without affecting the program. Thus, object types support both procedural and data abstraction.

Note: To use object types, you must have the Oracle8 Enterprise Edition and the Objects Option. For more information, see Getting to Know Oracle8 and the Oracle8 Enterprise Edition

.

## What Is an Object Type?

An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods.

We usually think of an object (such as a person, car, or bank account) as having attributes and behaviors. For example, a baby has the attributes gender, age, and weight, and the behaviors eat, drink, and sleep. Object types let you maintain this perspective when you sit down to write an application.

When you define an object type using the `CREATE` `TYPE` statement, you create an abstract template for some real-world object. The template specifies only those attributes and behaviors the object will need in the application environment. For example, an employee has many attributes, but usually only a few are needed to fill the requirements of an application (see Figure 9-1).

#### Figure 9-1 Form Follows Function

Suppose you must write a program to allocate employee bonuses. Not all employee attributes are needed to solve this problem. So, you design an abstract employee who has the following problem-specific attributes: name, id_number, department, job title, salary, and rank. Then, you identify the operations needed to handle an abstract employee. For example, you need an operation that lets Management change the rank of an employee.

Next, you define a set of variables (attributes) to represent the data, and a set of subprograms (methods) to perform the operations. Finally, you encapsulate the attributes and methods in an object type.

The data structure formed by the set of attributes is public (visible to client programs). However, well-behaved programs do not manipulate it directly. Instead, they use the set of methods provided. That way, the employee data is kept in a proper state. (Future releases of Oracle will let you define private data structures, which can be manipulated only by the methods you provide.)

At run time, when the data structure is filled with values, you have created an instance of an abstract employee. You can create as many instances (usually called objects) as you need. Each object has the name, number, job title, and so on of an actual employee (see Figure 9-2). This data is accessed or changed only by the methods associated with it. Thus, object types let you create objects with well-defined attributes and behavior.

#### Figure 9-2 Object Type and Objects (Instances) of That Type

## Why Use Object Types?

Object types reduce complexity by breaking down a large system into logical entities. This allows you to create software components that are modular, maintainable, and reusable. It also allows different teams of programmers to develop software components concurrently.

By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Object types minimize side effects by allowing access to data only through approved operations. Also, object types hide implementation details, so that you can change the details without affecting client programs.

Object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. Moreover, object types map directly into classes defined in object-oriented languages such as C++. Now your programs can better reflect the world they are trying to simulate.

## Structure of an Object Type

Like a package, an object type has two parts: a specification and a body (refer to Figure 9-3). The specification is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body fully defines the methods, and so implements the specification.

#### Figure 9-3 Object Type Structure

All the information a client program needs to use the methods is in the specification. Think of the specification as an operational interface and of the body as a black box. You can debug, enhance, or replace the body without changing the specification-and without affecting client programs.

In an object type specification, all attributes must be declared before any methods. Only subprograms have an underlying implementation. So, if an object type specification declares only attributes, the object type body is unnecessary. You cannot declare attributes in the body.

All declarations in the object type specification are public (visible outside the object type). However, the object type body can contain private declarations, which define methods necessary for the internal workings of the object type. The scope of private declarations is local to the object type body.

To understand the structure better, study the example below, in which an object type for complex numbers is defined. For now, it is enough to know that a complex number has two parts, a real part and an imaginary part, and that several arithmetic operations are defined for complex numbers.

    CREATE TYPE Complex AS OBJECT (
       rpart REAL,
       ipart REAL,
       MEMBER FUNCTION plus (x Complex) RETURN Complex,
       MEMBER FUNCTION less (x Complex) RETURN Complex,
       MEMBER FUNCTION times (x Complex) RETURN Complex,
       MEMBER FUNCTION divby (x Complex) RETURN Complex
    );

    CREATE TYPE BODY Complex AS
       MEMBER FUNCTION plus (x Complex) RETURN Complex IS
       BEGIN
             RETURN Complex(rpart + x.rpart, ipart + x.ipart);
       END plus;
       MEMBER FUNCTION less (x Complex) RETURN Complex IS
       BEGIN
          RETURN Complex(rpart - x.rpart, ipart - x.ipart);
       END less;
       MEMBER FUNCTION times (x Complex) RETURN Complex IS
       BEGIN
          RETURN Complex(rpart * x.rpart - ipart * x.ipart,
                         rpart * x.ipart + ipart * x.rpart);
       END times;
       MEMBER FUNCTION divby (x Complex) RETURN Complex IS
          z REAL := x.rpart**2 + x.ipart**2;
       BEGIN
          RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z,
                         (ipart * x.rpart - rpart * x.ipart) / z);
       END divby;
    END;

## Components of an Object Type

An object type encapsulates data and operations. So, you can declare attributes and methods in an object type specification, but not constants, exceptions, cursors, or types. At least one attribute is required (the maximum is 1000); methods are optional.

### Attributes

Like a variable, an attribute is declared with a name and datatype. The name must be unique within the object type (but can be reused in other object types). The datatype can be any Oracle type except

- `LONG` and `LONG` `RAW`
- `NCHAR`, `NCLOB`, and `NVARCHAR`2
- `MLSLABEL` and `ROWID`
- the PL/SQL-specific types `BINARY_INTEGER` (and its subtypes), `BOOLEAN`, `PLS_INTEGER`, `RECORD`, `REF` `CURSOR`, `%TYPE`, and `%ROWTYPE`
- types defined inside a PL/SQL package

For example, the `REAL` variables `rpart` and `ipart` are attributes of object type `Complex` (defined in the previous section).

You cannot initialize an attribute in its declaration using the assignment operator or `DEFAULT` clause. Also, you cannot impose the `NOT` `NULL` constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.

The kind of data structure formed by a set of attributes depends on the real-world object being modeled. For example, to represent a rational number, which has a numerator and a denominator, you need only two `INTEGER` variables. On the other hand, to represent a college student, you need several `VARCHAR2` variables to hold a name, address, phone number, status, and so on, plus a `VARRAY` variable to hold courses and grades.

The data structure can be very complex. For example, the datatype of an attribute can be another object type (called a nested object type). That lets you build a complex object type from simpler object types. Some object types such as queues, lists, and trees are dynamic, meaning that they can grow as they are used. Recursive object types, which contain direct or indirect references to themselves, allow for highly sophisticated data models.

### Methods

In general, a method is a subprogram declared in an object type specification using the keyword `MEMBER`. The method cannot have the same name as the object type or any of its attributes.

Like packaged subprograms, most methods have two parts: a specification and a body. The specification consists of a method name, an optional parameter list, and, for functions, a return type. The body is the code that executes to perform a specific operation. For example, the functions `plus`, `less`, `times`, and `divby` are methods of object type`Complex`. These methods are always available to `Complex` objects.

For each method specification in an object type specification, there must be a corresponding method body in the object type body. To match method specifications and bodies, the PL/SQL compiler does a token-by-token comparison of their headers. So, the headers must match word for word.

In an object type, methods can reference attributes and other methods without a qualifier, as the example below shows.

    CREATE TYPE Stack AS OBJECT (
       top INTEGER,
       MEMBER FUNCTION full RETURN BOOLEAN,
       MEMBER PROCEDURE push (n IN INTEGER),
       ...
    );

    CREATE TYPE BODY Stack AS
       ...
       MEMBER PROCEDURE push (n IN INTEGER) IS
       BEGIN
          IF NOT full THEN
             top := top + 1;
             ...
       END push;
    END;

#### Parameter SELF

All methods in an object type accept an instance of that type as their first parameter. The name of this built-in parameter is `SELF`. Whether declared implicitly or explicitly, `SELF` is always the first parameter passed to a method. For example, method `transform` declares `SELF` as an `IN` `OUT` parameter:

    CREATE TYPE Complex AS OBJECT (
       MEMBER FUNCTION transform (SELF IN OUT Complex) ...

In member functions, if `SELF` is not declared, its parameter mode defaults to `IN`. However, in member procedures, if `SELF` is not declared, its parameter mode defaults to `IN` `OUT`. You cannot specify a different datatype for `SELF`.

In a method body, `SELF` denotes the object whose method was called. As the following example shows, methods can reference the attributes of `SELF` without a qualifier:

    CREATE FUNCTION gcd (x INTEGER, y INTEGER) RETURN INTEGER AS
    -- find greatest common divisor of x and y
       ans INTEGER;
    BEGIN
       IF (y <= x) AND (x MOD y = 0) THEN ans := y;
       ELSIF x < y THEN ans := gcd(y, x);
       ELSE ans := gcd(y, x MOD y);
       END IF;
       RETURN ans;
    END;

    CREATE TYPE Rational AS OBJECT (
       num INTEGER,
       den INTEGER,
       MEMBER PROCEDURE normalize,
       ...
    );

    CREATE TYPE BODY Rational AS
       MEMBER PROCEDURE normalize IS
          g INTEGER;
       BEGIN
          --  the next two statements are equivalent
          g := gcd(SELF.num, SELF.den);
          g := gcd(num, den);
          num := num / g;
          den := den / g;
       END normalize;
       ...
    END;

#### Overloading

Like packaged subprograms, methods of the same kind (functions or procedures) can be overloaded. That is, you can use the same name for different methods if their formal parameters differ in number, order, or datatype family. When you call one of the methods, PL/SQL finds it by comparing the list of actual parameters with each list of formal parameters.

You cannot overload two methods if their formal parameters differ only in parameter mode. Also, you cannot overload two member functions that differ only in return type. For more information, see "Overloading".

#### Map and Order Methods

The values of a scalar datatype such as `CHAR` or `REAL` have a predefined order, which allows them to be compared. But, instances of an object type have no predefined order. To put them in order, PL/SQL calls a map method supplied by you. In the following example, the keyword `MAP` indicates that method`convert` orders `Rational` objects by mapping them to `REAL` values:

    CREATE TYPE Rational AS OBJECT (
       num INTEGER,
       den INTEGER,
       MAP MEMBER FUNCTION convert RETURN REAL,
       ...
    );

    CREATE TYPE BODY Rational AS
       MAP MEMBER FUNCTION convert RETURN REAL IS
       -- convert rational number to real number
       BEGIN
          RETURN num / den;
       END convert;
       ...
    END;

PL/SQL uses the ordering to evaluate Boolean expressions such as `x > y`, and to do comparisons implied by the `DISTINCT`, `GROUP` `BY`, and `ORDER` `BY` clauses. Map method `convert` returns the relative position of an object in the ordering of all `Rational` objects.

An object type can contain only one map method, which must be a parameterless function with one of the following scalar return types: `DATE`, `NUMBER`, `VARCHAR2`, an ANSI SQL type such as `CHARACTER` or `REAL`.

Alternatively, you can supply PL/SQL with an order method. In the example below, the keyword `ORDER` indicates that method`match` compares two objects. Every order method takes just two parameters: the built-in parameter `SELF` and another object of the same type.

If `c1` and `c2` are `Customer` objects, a comparison such as `c1 > c2` calls method `match` automatically. The method returns a negative number, zero, or a positive number signifying that `SELF` is respectively less than, equal to, or greater than the other parameter.

    CREATE TYPE Customer AS OBJECT (
       id   NUMBER,
       name VARCHAR2(20),
       addr VARCHAR2(30),
       ORDER MEMBER FUNCTION match (c Customer) RETURN INTEGER
    );

    CREATE TYPE BODY Customer AS
       ORDER MEMBER FUNCTION match (c Customer) RETURN INTEGER IS
       BEGIN
          IF id < c.id THEN
             RETURN -1;  -- any negative number will do
          ELSIF id > c.id THEN
             RETURN 1;   -- any positive number will do
          ELSE
             RETURN 0;
          END IF;
       END;
    END;

An object type can contain only one order method, which must be a function that returns a numeric result.

##### Guidelines

A map method, acting like a hash function, maps object values into scalar values (which are easier to compare), then compares the scalar values. An order method simply compares one object value to another.

You can declare a map method or an order method but not both. If you declare either method, 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 are 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). You must use a map method for hash joins because PL/SQL hashes on the object value.

#### Constructor Methods

Every object type has a constructor method (constructor for short), which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type.

Oracle generates a default constructor for every object type. The formal parameters of the constructor match the attributes of the object type. That is, the parameters and attributes are declared in the same order and have the same names and datatypes.

PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed. For more information, see "Calling Constructors and Methods".

### Pragma RESTRICT_REFERENCES

To execute a SQL statement that calls a member function, Oracle must know the purity level of the function, that is, the extent to which the function is free of side effects. (In this context, side effects are references to database tables or packaged variables.)

Side effects can prevent the parallelization of a query, yield order-dependent (and therefore indeterminate) results, or require that package state be maintained across user sessions (which is not allowed). So, the following rules apply to a member function called from SQL statements:

- It cannot insert into, update, or delete from database tables.
- It cannot be executed remotely or in parallel if it reads or writes the values of packaged variables.
- It cannot write the values of packaged variables unless it is called from a `SELECT`, `VALUES`, or `SET` clause.
- It cannot call another method or subprogram that breaks one of the foregoing rules. Also, it cannot reference a view that breaks one of the rules. (Oracle replaces references to a view with a stored `SELECT` operation, which can include function calls.)

You use the pragma (compiler directive) `RESTRICT_REFERENCES` to enforce these rules. The pragma tells the PL/SQL compiler to deny the member function read/write access to database tables, packaged variables, or both.

In the object type specification, you code the pragma somewhere after the method to which it applies. The syntax follows:

    PRAGMA RESTRICT_REFERENCES ({DEFAULT | method_name},
       {RNDS | WNDS | RNPS | WNPS}[, {RNDS | WNDS | RNPS | WNPS}]...);

For example, the following pragma constrains map method `convert` to read no database state (`RNDS`), write no database state (`WNDS`), read no package state (`RNPS`), and write no package state (`WNPS`):

    CREATE TYPE Rational AS OBJECT (
       num INTEGER,
       den INTEGER,
       MAP MEMBER FUNCTION convert RETURN REAL,
       ...
       PRAGMA RESTRICT_REFERENCES (convert, RNDS,WNDS,RNPS,WNPS)
    );

You can specify up to four constraints in any order. To call the method from parallel queries, you must specify all four constraints. No constraint implies another. For example, `WNPS` does not imply `RNPS`.

If you specify the keyword `DEFAULT` instead of a method name, the pragma applies to all member functions including the system-defined constructor. For example, the following pragma constrains all member functions to write no database or package state:

    PRAGMA RESTRICT_REFERENCES (DEFAULT, WNDS, WNPS)

You can declare the pragma for any member function. Such pragmas override the default pragma. However, a non-default pragma can apply to only one method. So, among overloaded methods, the pragma always applies to the nearest preceding method.

For more information about pragma `RESTRICT_REFERENCES`, see Oracle8 Application Developer's Guide.

## Defining Object Types

An object type can represent any real-world entity. For example, an object type can represent a student, bank account, computer screen, rational number, or data structure such as a queue, stack, or list. This section gives several complete examples, which teach you a lot about the design of object types and prepare you to start writing your own.

Currently, you cannot define object types in a PL/SQL block, subprogram, or package. However, you can define them interactively in SQL*Plus or Enterprise Manager using the following syntax:

    CREATE TYPE type_name {IS | AS} OBJECT (
       attribute_name datatype[, attribute_name datatype]...
       [{MAP | ORDER} MEMBER function_specification,]
       [  MEMBER {procedure_specification | function_specification}
        | restrict_references_pragma
       [, MEMBER {procedure_specification | function_specification}
        | restrict_references_pragma]]...);

    [CREATE TYPE BODY type_name {IS | AS}
      { {MAP | ORDER} MEMBER function_body;
       | MEMBER {procedure_body | function_body};}
      [MEMBER {procedure_body | function_body};]... END;]

### Object Type Stack

A stack holds an ordered collection of data items. As the name implies, stacks have a top and a bottom. But, items can be added or removed only at the top. So, the last item added to a stack is the first item removed. (Think of the stack of clean serving trays in a cafeteria.) The operations push and pop update the stack while preserving last in, first out (LIFO) behavior.

Stacks have many applications. For example, they are used in systems programming to prioritize interrupts and to manage recursion. The simplest implementation of a stack uses an integer array. Integers are stored in array elements, with one end of the array representing the top of the stack.

PL/SQL provides the datatype `VARRAY`, which allows you to declare variable-size arrays (varrays for short). To declare a varray attribute, we must first define its type. However, we cannot define types in an object type specification. So, we define a stand-alone varray type, specifying its maximum size, as follows:

    CREATE TYPE IntArray AS VARRAY(25) OF INTEGER;

Now, we can write our object type specification, as follows:

    CREATE TYPE Stack AS OBJECT (
       max_size INTEGER,
       top      INTEGER,
       position IntArray,
       MEMBER PROCEDURE initialize,
       MEMBER FUNCTION full RETURN BOOLEAN,
       MEMBER FUNCTION empty RETURN BOOLEAN,
       MEMBER PROCEDURE push (n IN INTEGER),
       MEMBER PROCEDURE pop (n OUT INTEGER)
    );

Finally, we write the object type body, as follows:

    CREATE TYPE BODY Stack AS
       MEMBER PROCEDURE initialize IS
       BEGIN
          top := 0;
          /* Call constructor for varray and set element 1 to NULL. */
          position := IntArray(NULL);
          max_size := position.LIMIT;  -- get varray size constraint (25)
          position.EXTEND(max_size - 1, 1);  -- copy element 1 into 2..25
       END initialize;

       MEMBER FUNCTION full RETURN BOOLEAN IS
       BEGIN
          RETURN (top = max_size);  -- return TRUE if stack is full
       END full;

       MEMBER FUNCTION empty RETURN BOOLEAN IS
       BEGIN
          RETURN (top = 0);  -- return TRUE if stack is empty
       END empty;

       MEMBER PROCEDURE push (n IN INTEGER) IS
       BEGIN
          IF NOT full THEN
             top := top + 1;  -- push integer onto stack
             position(top) := n;
          ELSE  -- stack is full
             RAISE_APPLICATION_ERROR(-20101, `stack overflow');
          END IF;
       END push;

       MEMBER PROCEDURE pop (n OUT INTEGER) IS
       BEGIN
          IF NOT empty THEN
             n := position(top);
             top := top - 1;  -- pop integer off stack
          ELSE  -- stack is empty
             RAISE_APPLICATION_ERROR(-20102, `stack underflow');
          END IF;
       END pop;
    END;

Notice that in member procedures `push` and `pop`, we use the built-in procedure `raise_application_error` to issue user-defined error messages. That way, we report errors to the client program and avoid returning unhandled exceptions to the host environment. The client program gets a PL/SQL exception, which it can process using the error-reporting functions `SQLCODE` and `SQLERRM` in an `OTHERS` exception handler, as follows:

    DECLARE
       err_num NUMBER;
       err_msg VARCHAR2(100);
    BEGIN
       ...
    EXCEPTION
       ...
       WHEN OTHERS THEN
          err_num := SQLCODE;
          err_msg := SUBSTR(SQLERRM, 1, 100);
          DBMS_OUTPUT.PUT_LINE(TO_CHAR(err_num) || ': ' || err_msg);

The string function `SUBSTR` ensures that a `VALUE_ERROR` exception (for truncation) is not raised when you assign the value of `SQLERRM` to `err_msg`.

Alternatively, the program can use pragma `EXCEPTION_INIT` to map the error numbers returned by `raise_application_error` to named exceptions, as the following example shows:

    DECLARE
       stack_overflow  EXCEPTION;
       stack_underflow EXCEPTION;
       PRAGMA EXCEPTION_INIT(stack_overflow, -20101);
       PRAGMA EXCEPTION_INIT(stack_underflow, -20102);
    BEGIN
       ...
    EXCEPTION
       WHEN stack_overflow THEN ...

### Object Type Ticket_Booth

Consider a chain of low-budget, triplex movie theatres. Each theatre has a ticket booth where tickets for three different movies are sold. All tickets are priced at $2.50. Periodically, ticket receipts are collected and the stock of tickets is replenished.

Before defining an object type that represents a ticket booth, we must consider the data and operations needed. For a simple ticket booth, the object type needs attributes for the ticket price, quantity of tickets on hand, and receipts. It also needs methods for the following operations: purchase ticket, take inventory, replenish stock, and collect receipts.

For receipts, we use a three-element varray. Elements 1, 2, and 3 record the ticket receipts for movies 1, 2, and 3, respectively. To declare a varray attribute, we must first define its type, as follows:

    CREATE TYPE RealArray AS VARRAY(3) OF REAL;

Now, we can write our object type specification, as follows:

    CREATE TYPE Ticket_Booth AS OBJECT (
       price       REAL,
       qty_on_hand INTEGER,
       receipts    RealArray,
       MEMBER PROCEDURE initialize,
       MEMBER PROCEDURE purchase (
          movie  INTEGER,
          amount REAL,
          change OUT REAL),
       MEMBER FUNCTION inventory RETURN INTEGER,
       MEMBER PROCEDURE replenish (quantity INTEGER),
       MEMBER PROCEDURE collect (movie INTEGER, amount OUT REAL)
    );

Finally, we write the object type body, as follows:

    CREATE TYPE BODY Ticket_Booth AS
       MEMBER PROCEDURE initialize IS
       BEGIN
          price := 2.50;
          qty_on_hand := 5000;  -- provide initial stock of tickets
          -- call constructor for varray and set elements 1..3 to zero
          receipts := RealArray(0,0,0);
       END initialize;

       MEMBER PROCEDURE purchase (
          movie  INTEGER,
          amount REAL,
          change OUT REAL) IS
       BEGIN
          IF qty_on_hand = 0 THEN
             RAISE_APPLICATION_ERROR(-20103, `out of stock');
          END IF;
          IF amount >= price THEN
             qty_on_hand := qty_on_hand - 1;
             receipts(movie) := receipts(movie) + price;
             change := amount - price;
          ELSE  -- amount is not enough
             change := amount;  -- so return full amount
          END IF;
       END purchase;

       MEMBER FUNCTION inventory RETURN INTEGER IS
       BEGIN
          RETURN qty_on_hand;
       END inventory;

       MEMBER PROCEDURE replenish (quantity INTEGER) IS
       BEGIN
          qty_on_hand := qty_on_hand + quantity;
       END replenish;

       MEMBER PROCEDURE collect (movie INTEGER, amount OUT REAL) IS
       BEGIN
          amount := receipts(movie);  -- get receipts for a given movie
          receipts(movie) := 0;  -- reset receipts for that movie to zero
       END collect;
    END;

### Object Type Bank_Account

Before defining an object type that represents a bank account, we must consider the data and operations needed. For a simple bank account, the object type needs attributes for an account number, balance, and status. It also needs methods for the following operations: open account, verify account number, close account, deposit money, withdraw money, and return balance.

First, we write the object type specification, as follows:

    CREATE TYPE Bank_Account AS OBJECT (
       acct_number INTEGER(5),
       balance     REAL,
       status      VARCHAR2(10),
       MEMBER PROCEDURE open (amount IN REAL),
       MEMBER PROCEDURE verify_acct (num IN INTEGER),
       MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL),
       MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL),
       MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL),
       MEMBER FUNCTION curr_bal (SELF IN OUT Bank_Account, num IN INTEGER)
          RETURN REAL
    );

Then, we write the object type body, as follows:

    CREATE TYPE BODY Bank_Account AS
       MEMBER PROCEDURE open (amount IN REAL) IS
       -- open account with initial deposit
       BEGIN
          IF NOT amount > 0 THEN
             RAISE_APPLICATION_ERROR(-20104, `bad amount');
          END IF;
          SELECT acct_sequence.NEXTVAL INTO acct_number FROM dual;
          status := `open';
          balance := amount;
       END open;

       MEMBER PROCEDURE verify_acct (num IN INTEGER) IS
       -- check for wrong account number or closed account
       BEGIN
          IF (num <> acct_number) THEN
             RAISE_APPLICATION_ERROR(-20105, `wrong number');
          ELSIF (status = `closed') THEN
             RAISE_APPLICATION_ERROR(-20106, `account closed');
          END IF;
       END verify_acct;

       MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL) IS
       -- close account and return balance
       BEGIN
          verify_acct(num);
          status := `closed';
          amount := balance;
       END close;

       MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL) IS
       BEGIN
          verify_acct(num);
          IF NOT amount > 0 THEN
             RAISE_APPLICATION_ERROR(-20104, `bad amount');
          END IF;
          balance := balance + amount;
       END deposit;

       MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL) IS
       -- if account has enough funds, withdraw
       -- given amount; else, raise an exception
       BEGIN
          verify_acct(num);
          IF amount <= balance THEN
             balance := balance - amount;
          ELSE
             RAISE_APPLICATION_ERROR(-20107, `insufficient funds');
          END IF;
       END withdraw;

       MEMBER FUNCTION curr_bal (SELF IN OUT Bank_Account, num IN INTEGER)
          RETURN REAL IS
       BEGIN
          verify_acct(num);
          RETURN balance;
       END curr_bal;
    END;

### Object Type Rational

A rational number is a number expressible as the quotient of two integers, a numerator and a denominator. Like most languages, PL/SQL does not have a rational number type or predefined operations on rational numbers. Let us remedy that omission by defining object type`Rational`. First, we write the object type specification, as follows:

    CREATE TYPE Rational AS OBJECT (
       num INTEGER,
       den INTEGER,
       MAP MEMBER FUNCTION convert RETURN REAL,
       MEMBER PROCEDURE normalize,
       MEMBER FUNCTION reciprocal RETURN Rational,
       MEMBER FUNCTION plus (x Rational) RETURN Rational,
       MEMBER FUNCTION less (x Rational) RETURN Rational,
       MEMBER FUNCTION times (x Rational) RETURN Rational,
       MEMBER FUNCTION divby (x Rational) RETURN Rational,
       PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS,WNDS,RNPS,WNPS)
    );

PL/SQL does not allow the overloading of operators. That is why we define methods named `plus`, `less` (the word `minus` is reserved), `times`, and `divby` instead of overloading the infix operators `+`, `-`, `*`, and `/`.

Next, we create the following stand-alone stored function, which will be called by method `normalize`.

    CREATE FUNCTION gcd (x INTEGER, y INTEGER) RETURN INTEGER AS
    -- find greatest common divisor of x and y
       ans INTEGER;
    BEGIN
       IF (y <= x) AND (x MOD y = 0) THEN
          ans := y;
       ELSIF x < y THEN
          ans := gcd(y, x);  -- recursive call
       ELSE
          ans := gcd(y, x MOD y);  -- recursive call
       END IF;
       RETURN ans;
    END;

Then, we write the object type body, as follows:

    CREATE TYPE BODY Rational AS
       MAP MEMBER FUNCTION convert RETURN REAL IS
       -- convert rational number to real number
       BEGIN
          RETURN num / den;
       END convert;

       MEMBER PROCEDURE normalize IS
       -- reduce fraction num / den to lowest terms
          g INTEGER;
       BEGIN
          g := gcd(num, den);
          num := num / g;
          den := den / g;
       END normalize;

       MEMBER FUNCTION reciprocal RETURN Rational IS
       -- return reciprocal of num / den
       BEGIN
          RETURN Rational(den, num);  -- call constructor
       END reciprocal;

       MEMBER FUNCTION plus (x Rational) RETURN Rational IS
       -- return sum of SELF + x
          r Rational;
       BEGIN
          r := Rational(num * x.den + x.num * den, den * x.den);
          r.normalize;
          RETURN r;
       END plus;

       MEMBER FUNCTION less (x Rational) RETURN Rational IS
       -- return difference of SELF - x
          r Rational;
       BEGIN
          r := Rational(num * x.den - x.num * den, den * x.den);
          r.normalize;
          RETURN r;
       END less;

       MEMBER FUNCTION times (x Rational) RETURN Rational IS
       -- return product of SELF * x
          r Rational;
       BEGIN
          r := Rational(num * x.num, den * x.den);
          r.normalize;
          RETURN r;
       END times;

       MEMBER FUNCTION divby (x Rational) RETURN Rational IS
       -- return quotient of SELF / x
          r Rational;
       BEGIN
          r := Rational(num * x.den, den * x.num);
          r.normalize;
          RETURN r;
       END divby;
    END;

## Declaring and Initializing Objects

Once an object type is defined and installed in the schema, you can use it to declare objects in any PL/SQL block, subprogram, or package. For example, you can use the object type to specify the datatype of an attribute, column, variable, bind variable, record field, table element, formal parameter, or function result. At run time, instances of the object type are created; that is, objects of that type are instantiated. Each object can hold different values.

Such objects follow the usual scope and instantiation rules. In a block or subprogram, local objects are instantiated when you enter the block or subprogram and cease to exist when you exit. In a package, objects are instantiated when you first reference the package and cease to exist when you end the database session.

### Declaring Objects

You can use object types wherever built-in types such as `CHAR` or `NUMBER` can be used. In the block below, you declare object `r` of type `Rational`. Then, you call the constructor for object type `Rational` to initialize the object. The call assigns the values 6 and 8 to attributes `num` and `den,` respectively.

    DECLARE
       r Rational;
    BEGIN
       r := Rational(6, 8);
       DBMS_OUTPUT.PUT_LINE(r.num); -- prints 6

You can declare objects as the formal parameters of functions and procedures. That way, you can pass objects to stored subprograms and from one subprogram to another. In the next example, you use object type `Account` to specify the datatype of a formal parameter:

    DECLARE
       ...
       PROCEDURE open_acct (new_acct IN OUT Account) IS ...

In the following example, you use object type `Account` to specify the return type of a function:

    DECLARE
       ...
       FUNCTION get_acct (acct_id IN INTEGER) RETURN Account IS ...

### Initializing Objects

Until you initialize an object by calling the constructor for its object type, the object is atomically null. That is, the object itself is null, not just its attributes. Consider the following example:

    DECLARE
       r Rational;  -- r becomes atomically null
    BEGIN
       r := Rational(2,3);  -- r becomes 2/3

A null object is never equal to another object. In fact, comparing a null object with any other object always yields `NULL`. Also, if you assign an atomically null object to another object, the other object becomes atomically null (and must be reinitialized). Likewise, if you assign the non-value `NULL` to an object, the object becomes atomically null, as the following example shows:

    DECLARE
       r Rational;
    BEGIN
       r Rational := Rational(1,2);  -- r becomes 1/2
       r := NULL;  -- r becomes atomically null
       IF r IS NULL THEN ...  -- condition yields TRUE

A good programming practice is to initialize an object in its declaration, as shown in the following example:

    DECLARE
       r Rational := Rational(2,3);  -- r becomes 2/3

### How PL/SQL Treats Uninitialized Objects

In an expression, attributes of an uninitialized object evaluate to `NULL`. Trying to assign values to attributes of an uninitialized object raises the predefined exception `ACCESS_INTO_NULL`. When applied to an uninitialized object or its attributes, the `IS` `NULL` comparison operator yields `TRUE`.

The following example illustrates the difference between null objects and objects with null attributes:

    DECLARE
       r Rational;  -- r is atomically null
    BEGIN
       IF r IS NULL THEN ...  -- yields TRUE
       IF r.num IS NULL THEN ...  -- yields TRUE
       r := Rational(NULL, NULL);  -- initializes r
       r.num := 4; -- succeeds because r is no longer atomically null
                   -- even though all its attributes are null
       r := NULL;  -- r becomes atomically null again
       r.num := 4;  -- raises ACCESS_INTO_NULL
    EXCEPTION
       WHEN ACCESS_INTO_NULL THEN
          ...
    END;

Calls to methods of an uninitialized object are allowed, in which case `SELF` is bound to `NULL`. When passed as arguments to `IN` parameters, attributes of an uninitialized object evaluate to `NULL`. When passed as arguments to `OUT` or `IN` `OUT` parameters, they raise an exception if you try to write to them.

## Accessing Attributes

You can refer to an attribute only by name (not by its position in the object type). To access or change the value of an attribute, you use dot notation. In the example below, you assign the value of attribute `den` to variable `denominator`. Then, you assign the value stored in variable `numerator` to attribute `num`.

    DECLARE
       r Rational := Rational(NULL, NULL);
       numerator   INTEGER;
       denominator INTEGER;
    BEGIN
       ...
       denominator := r.den;
       r.num := numerator;

Attribute names can be chained, which allows you to access the attributes of a nested object type. For example, suppose we define object types `Address` and `Student`, as follows:

    CREATE TYPE Address AS OBJECT (
       street   VARCHAR2(30),
       city     VARCHAR2(20),
       state    CHAR(2),
       zip_code VARCHAR2(5)
    );

    CREATE TYPE Student AS OBJECT (
       name         VARCHAR2(20),
       home_address Address,
       phone_number VARCHAR2(10),
       status       VARCAHR2(10),
       advisor_name VARCHAR2(20),
       ...
    );'

Notice that `zip_code` is an attribute of object type `Address` and that `Address` is the datatype of attribute `home_address` in object type `Student`. If `s` is a `Student` object, you access the value of its `zip_code` attribute as follows:

    s.home_address.zip_code

## Calling Constructors and Methods

Calls to a constructor are allowed wherever function calls are allowed. Like all functions, a constructor is called as part of an expression, as the following example shows:

    DECLARE
       r1 Rational := Rational(2, 3);
       FUNCTION average (x Rational, y Rational) RETURN Rational IS
       BEGIN
          ...
       END;
    BEGIN
       r1 := average(Rational(3, 4), Rational(7, 11));
       IF (Rational(5, 8) > r1) THEN
          ...
       END IF;
    END;

### Passing Parameters to a Constructor

When you pass parameters to a constructor, the call assigns initial values to the attributes of the object being instantiated. You must supply a parameter for every attribute because, unlike constants and variables, attributes cannot have `DEFAULT` clauses. As the following example shows, the nth parameter assigns a value to the nth attribute:

    DECLARE
       r Rational;
    BEGIN
       r := Rational(5, 6);  -- assign 5 to num, 6 to den
       -- now r is 5/6

You can call a constructor using named notation instead of positional notation, as the following example shows:

    BEGIN
       r := Rational(den => 6, num => 5);  -- assign 5 to num, 6 to den

### Calling Methods

Like packaged subprograms, methods are called using dot notation. In the example below, you call method `normalize,` which divides attributes `num` and `den` by their greatest common divisor.

    DECLARE
       r Rational;
    BEGIN
       r := Rational(6, 8);
       r.normalize;;
       DBMS_OUTPUT.PUT_LINE(r.num);  -- prints 3

As the example below shows, you can chain method calls. Execution proceeds from left to right. First, member function `reciprocal` is called, then member procedure `normalize` is called.

    DECLARE
       r Rational := Rational(6, 8);
    BEGIN
       r.reciprocal().normalize;
       DBMS_OUTPUT.PUT_LINE(r.num);  -- prints 4

In SQL statements, calls to a parameterless method require an empty parameter list. In procedural statements, an empty parameter list is optional unless you chain calls, in which case it is required for all but the last call.

You cannot chain additional method calls to the right of a procedure call because procedures are called as statements, not as part of an expression. For example, the following statement is illegal:

    r.normalize().reciprocal;  -- illegal

Also, if you chain two function calls, the first function must return an object that can be passed to the second function.

## Sharing Objects

Most real-world objects are considerably larger and more complex than objects of type `Rational`. Consider the following object types:

    CREATE TYPE Address AS OBJECT (
       street_address VARCHAR2(35),
       city           VARCHAR2(15),
       state          CHAR(2),
       zip_code       INTEGER
    );

    CREATE TYPE Person AS OBJECT (
       first_name   VARCHAR2(15),
       last_name    VARCHAR2(15),
       birthday     DATE,
       home_address Address,  -- nested object type
       phone_number VARCHAR2(15),
       ss_number    INTEGER,
       ...
    );

`Address` objects have twice as many attributes as `Rational` objects, and `Person` objects have still more attributes including one of type `Address`. When an object is large, it is inefficient to pass copies of it from subprogram to subprogram. It makes more sense to share the object. You can do that if the object has an object identifier. To share the object, you use references (refs for short). A ref is a pointer to an object.

Sharing has two important advantages. First, data is not replicated unnecessarily. Second, when a shared object is updated, the change occurs in only one place, and any ref can retrieve the updated values instantly.

In the following example, we gain the advantages of sharing by defining object type `Home` and then creating a table that stores instances of that object type:

    CREATE TYPE Home AS OBJECT (
       address    VARCHAR2(35),
       owner      VARCHAR2(25),
       age        INTEGER,
       style      VARCHAR(15),
       floor plan BLOB,
       price      REAL(9,2),
       ...
    );
    ...
    CREATE TABLE homes OF Home;

### Using Refs

By revising object type `Person`, we can model a community in which several people might share the same home. We use the type modifier `REF` to declare refs, which hold pointers to objects.

    CREATE TYPE Person AS OBJECT (
       first_name   VARCHAR2(10),
       last_name    VARCHAR2(15),
       birthday     DATE,
       home_address REF Home,  -- can be shared by family
       phone_number VARCHAR2(15),
       ss_number    INTEGER,
       mother       REF Person, -- family members refer to each other
       father       REF Person,
       ...
    );

Notice how references from persons to homes and between persons model real-world relationships.

You can declare refs as variables, parameters, fields, or attributes. And, you can use refs as input or output variables in SQL data manipulation statements. However, you cannot navigate through refs. Given an expression such as `x.attribute`, where `x` is a ref, PL/SQL cannot navigate to the table in which the referenced object is stored. For example, the following assignment is illegal:

    DECLARE
       p_ref    REF Person;
       phone_no VARCHAR2(15);
    BEGIN
       ...
       phone_no := p_ref.phone_number;  -- illegal

Instead, you must use the operator `DEREF` to access the object. For some examples, see "Using Operator DEREF".

### Forward Type Definitions

You can refer only to schema objects that already exist. In the following example, the first `CREATE` `TYPE` statement is illegal because it refers to object type `Department`, which does not yet exist.

    CREATE TYPE Employee AS OBJECT (
       name VARCHAR2(20),
       dept REF Department,  -- illegal
       ...
    );

    CREATE TYPE Department AS OBJECT (
       number  INTEGER,
       manager Employee,
       ...
    );

Switching the `CREATE` `TYPE` statements does not help because the object types are mutually dependent; that is, one depends on the other through a ref. To solve this problem, you use a special `CREATE` `TYPE` statement called a forward type definition, which lets you define mutually dependent object types.

To debug the last example, simply precede it with the following statement:

    CREATE TYPE Department;  -- forward type definition
       -- at this point, Department is an incomplete object type

The object type created by a forward type definition is called an incomplete object type because (until it is defined fully) it has no attributes or methods.

An impure incomplete object type has attributes but compiles with semantic (not syntactic) errors because it refers to an undefined type. For example, the following `CREATE` `TYPE` statement compiles with a semantic error because object type `Address` is undefined:

    CREATE TYPE Customer AS OBJECT (
       id    NUMBER,
       name  VARCHAR2(20),
       addr  Address,  -- not yet defined
       phone VARCHAR2(15)
    );

This allows you to defer the definition of object type `Address`. Moreover, the incomplete type `Customer` can be made available to other application developers for use in refs.

## Manipulating Objects

You can use an object type in the `CREATE` `TABLE` statement to specify the datatype of a column. Once the table is created, you can use SQL statements to insert an object, select its attributes, call its methods, and update its state.

In the SQL*Plus script below, the `INSERT` statement calls the constructor for object type `Rational`, then inserts the resulting object. The `SELECT` statement retrieves the value of attribute `num`. The `UPDATE` statement calls member method `reciprocal`, which returns a `Rational` value after swapping attributes `num` and `den`. Notice that a table alias is required when you reference an attribute or method. (For an explanation, see Appendix E.)

    CREATE TABLE numbers (rn Rational, ...)
    /
    INSERT INTO numbers (rn) VALUES (Rational(3, 62))  -- inserts 3/62
    /
    SELECT n.rn.num INTO my_num FROM numbers n WHERE ...  -- returns 3
    /
    UPDATE numbers n SET n.rn = n.rn.reciprocal WHERE ...  -- yields 62/3
    /

When you instantiate an object this way, it has no identity outside the database table. However, the object type exists independently of any table, and can be used to create objects in other ways.

In the next example, you create a table that stores objects of type `Rational` in its rows. Such tables, having rows of objects, are called object tables. Each column in a row corresponds to an attribute of the object type. Rows can have different column values.

    CREATE TABLE rational_nums OF Rational;

Each row in an object table has an object identifier, which uniquely identifies the object stored in that row and serves as a reference to the object.

### Selecting Objects

Assume that you have run the following SQL*Plus script, which creates object type `Person` and object table `persons`, and that you have populated the table:

    CREATE TYPE Person AS OBJECT (
       first_name   VARCHAR2(15),
       last_name    VARCHAR2(15),
       birthday     DATE,
       home_address Address,
       phone_number VARCHAR2(15))
    /
    CREATE TABLE persons OF Person
    /

The following subquery produces a result set of rows containing only the attributes of `Person` objects:

    BEGIN
       INSERT INTO employees  -- another object table of type Person
          SELECT * FROM persons p
              WHERE p.last_name LIKE `%Smith';

To return a result set of objects, you must use the operator `VALUE`, which is discussed in the next section.

#### Using Operator VALUE

As you might expect, the operator `VALUE` returns the value of an object. `VALUE` takes as its argument a correlation variable. (In this context, a correlation variable is a row variable or table alias associated with a row in an object table.) For example, to return a result set of `Person` objects, you use `VALUE`, as follows:

    BEGIN
       INSERT INTO employees
          SELECT VALUE(p) FROM persons p
              WHERE p.last_name LIKE `%Smith';

In the next example, you use `VALUE` to return a specific `Person` object:

    DECLARE
       p1 Person;
       p2 Person;
       ...
    BEGIN
       SELECT VALUE(p) INTO p1 FROM persons p
          WHERE p.last_name = `Kroll';
       p2 := p1;
       ...
    END;

At this point, `p1` holds a local `Person` object, which is a copy of the stored object whose last name is `'Kroll'`, and `p2` holds another local `Person` object, which is a copy of `p1`. As the following example shows, you can use these variables to access and update the objects they hold:

    BEGIN
       p1.last_name := p1.last_name || `Jr';

Now, the local `Person` object held by `p1` has the last name `'Kroll Jr'`.

#### Using Operator REF

You can retrieve refs using the operator `REF`, which, like `VALUE`, takes as its argument a correlation variable. In the following example, you retrieve one or more refs to `Person` objects, then insert the refs into table `person_refs`:

    BEGIN
       INSERT INTO person_refs
          SELECT REF(p) FROM persons p
              WHERE p.last_name LIKE '%Smith';

In the next example, you retrieve a ref and attribute at the same time:

    DECLARE
       p_ref       REF Person;
       taxpayer_id VARCHAR2(9);
    BEGIN
       SELECT REF(p), p.ss_number INTO p_ref, taxpayer_id
          FROM persons p
          WHERE p.last_name = 'Parker'; -- must return one row
       ...
    END;

In the final example, you update the attributes of a `Person` object:

    DECLARE
       p_ref        REF Person;
       my_last_name VARCHAR2(15);
       ...
    BEGIN
       ...
       SELECT REF(p) INTO p_ref FROM persons p
          WHERE p.last_name = my_last_name;
       UPDATE persons p
          SET p = Person('Jill', 'Anders', '11-NOV-67', ...)
          WHERE REF(p) = p_ref;
    END;

#### Testing for Dangling Refs

If the object to which a ref points is deleted, the ref is left dangling (pointing to a nonexistent object). To test for this condition, you can use the SQL predicate `IS` `DANGLING`. For example, suppose column `manager` in relational table `department` holds refs to `Employee` objects stored in an object table. You can use the following `UPDATE` statement to convert any dangling refs into nulls:

    BEGIN
       UPDATE department SET manager = NULL WHERE manager IS DANGLING;

#### Using Operator DEREF

You cannot navigate through refs within PL/SQL procedural statements. Instead, you must use the operator `DEREF` in a SQL statement. (`DEREF` is short for dereference. When you dereference a pointer, you get the value to which it points.) `DEREF` takes as its argument a reference to an object, then returns the value of that object. If the ref is dangling, `DEREF` returns a null object.

In the example below, you dereference a ref to a `Person` object. Notice that you select the ref from dummy table `dual`. You need not specify an object table and search criteria because each object stored in an object table has a unique, immutable object identifier, which is part of every ref to that object.

    DECLARE
       p1    Person;
       p_ref REF Person;
       name  VARCHAR2(15);
    BEGIN
       ...
       /* Assume that p_ref holds a valid reference
          to an object stored in an object table. */
       SELECT DEREF(p_ref) INTO p1 FROM dual;
       name := p1.last_name;

You can use `DEREF` in successive SQL statements to dereference refs, as the following example shows:

    CREATE TYPE PersonRef AS OBJECT (p_ref REF Person)
    /
    DECLARE
       name   VARCHAR2(15);
       pr_ref REF PersonRef;
       pr     PersonRef;
       p      Person;
    BEGIN
       ...
       /* Assume pr_ref holds a valid reference. */
       SELECT DEREF(pr_ref) INTO pr FROM dual;
       SELECT DEREF(pr.p_ref) INTO p FROM dual;
       name := p.last_name;
       ...
    END
    /

The next example shows that you cannot use operator `DEREF` within procedural statements:

    BEGIN
       ...
       p1 := DEREF(p_ref);  -- illegal

Within SQL statements, you can use dot notation to navigate through object columns to ref attributes and through one ref attribute to another. You can also navigate through ref columns to attributes if you use a table alias. For example, the following syntax is legal:

    table_alias.object_column.ref_attribute
    table_alias.object_column.ref_attribute.attribute
    table_alias.ref_column.attribute

Assume that you have run the following SQL*Plus script, which creates object types `Address` and `Person` and object table `persons`:

    CREATE TYPE Address AS OBJECT (
       street   VARCHAR2(35),
       city     VARCHAR2(15),
       state    CHAR(2),
       zip_code INTEGER)
    /
    CREATE TYPE Person AS OBJECT (
       first_name   VARCHAR2(15),
       last_name    VARCHAR2(15),
       birthday     DATE,
       home_address REF Address,  -- shared with other Person objects
       phone_number VARCHAR2(15))
    /
    CREATE TABLE persons OF Person
    /

Ref attribute `home_address` corresponds to a column in object table `persons` that holds refs to `Address` objects stored in some other table. After populating the tables, you can select a particular address by dereferencing its ref, as follows:

    DECLARE
       addr1 Address,
       addr2 Address,
       ...
    BEGIN
       SELECT DEREF(home_address) INTO addr1 FROM persons p
          WHERE p.last_name = `Derringer';

In the example below, you navigate through ref column `home_address` to attribute `street`. In this case, a table alias is required.

    DECLARE
       my_street VARCHAR2(25),
       ...
    BEGIN
       SELECT p.home_address.street INTO my_street FROM persons p
          WHERE p.last_name = `Lucas';

### Inserting Objects

You use the `INSERT` statement to add objects to an object table. In the following example, you insert a `Person` object into object table `persons`:

    BEGIN
       INSERT INTO persons
          VALUES ('Jenifer', 'Lapidus', ...);

Alternatively, you can use the constructor for object type `Person` to insert an object into object table `persons`:

    BEGIN
       INSERT INTO persons
          VALUES (Person('Albert', 'Brooker', ...));

In the next example, you use the `RETURNING` clause to store `Person` refs in local variables. Notice how the clause mimics a `SELECT` statement.You can also use the `RETURNING` clause in `UPDATE` and `DELETE` statements.

    DECLARE
       p1_ref REF Person;
       p2_ref REF Person;
       ...
    BEGIN
       INSERT INTO persons p
          VALUES (Person('Paul', 'Chang', ...))
          RETURNING REF(p) INTO p1_ref;
       INSERT INTO persons p
          VALUES (Person('Ana', 'Thorne', ...))
          RETURNING REF(p) INTO p2_ref;

To insert objects into an object table, you can use a subquery that returns objects of the same type. An example follows:

    BEGIN
       INSERT INTO persons2
          SELECT VALUE(p) FROM persons p
             WHERE p.last_name LIKE '%Jones';

The rows copied to object table `persons2` are given new object identifiers. No object identifiers are copied from object table `persons`.

The script below creates a relational table named `department`, which has a column of type `Person`, then inserts a row into the table. Notice how constructor `Person()` provides a value for column `manager`.

    CREATE TABLE department (
       dept_name VARCHAR2(20),
       manager   Person,
       location  VARCHAR2(20))
    /
    INSERT INTO department
       VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles')
    /

The new `Person` object stored in column `manager` is not referenceable because it is stored in a column (not a row) and therefore has no object identifier.

### Updating Objects

To modify the attributes of objects in an object table, you use the `UPDATE` statement, as the following example shows:

    BEGIN
       UPDATE persons p SET p.home_address = '341 Oakdene Ave'
          WHERE p.last_name = 'Brody';
       ...
       UPDATE persons p SET p = Person('Beth', 'Steinberg', ...)
          WHERE p.last_name = 'Steinway';
       ...
    END;

### Deleting Objects

You use the `DELETE` statement to remove objects (rows) from an object table. To remove objects selectively, you use the `WHERE` clause, as the following example shows:

    BEGIN
       DELETE FROM persons p
          WHERE p.home_address = '108 Palm Dr';
       ...
    END;

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index