Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 7
User-Defined Datatypes - An Extended Example

This chapter contains an extended example of how to use user-defined types. The chapter has the following major sections:

- Introduction
- A Purchase Order Example

## Introduction

User-defined types are schema objects in which users formalize the data structures and operations that appear in their applications.

See Also: Oracle8 Concepts for a discussion of user-defined types and how to use them.

The example in this chapter illustrates the most important aspects of defining and using user-defined types. The definitions of object type methods use the PL/SQL language. The remainder of the example uses Oracle SQL.

See Also: Oracle8 SQL Reference for a complete description of SQL syntax and usage.

PL/SQL provides additional capabilities beyond those illustrated here, especially in the area of accessing and manipulating the elements of collections.

See Also: PL/SQL User's Guide and Reference for a complete discussion of PL/SQL capabilities.

Client applications that use the Oracle call interface (OCI) can take advantage of its extensive facilities for accessing objects and collections and manipulating them on the client side.

See Also: Programmer's Guide to the Oracle Call Interface for a complete discussion of those facilities.

## A Purchase Order Example

This example is based on a simple business activity: managing the data in customer orders. The example is presented in three parts. The first two are in this chapter. The third is in Chapter 8, "Object Views-An Extended Example".

Each part implements a schema to support the basic activity. The first part implements the schema using only Oracle's built-in datatypes. This is called the relational approach. Using this approach, you create tables to hold the application's data and use well-known techniques to implement the application's entity relationships.

The second and third parts use user-defined types (UDTs) to translate the entities and relationships directly into schema objects that can be manipulated by a DBMS. This is called the object-relational approach. The second and third parts UDTs. They differ only in the way they implement the underlying data storage:

- The second part of the example creates object tables to hold the underlying data instead of the relational tables created in the first part.
- The third part uses the relational tables created in the first part. Rather than building object tables, it uses object views to represent virtual object tables.

### Entities and Relationships

The basic entities in this example are:

- Customers
- The stock of products for sale
- Purchase orders

Customers have a one-to-many relationship with purchase orders because a customer can place many orders, but a given purchase order is placed by a single customer.

Purchase orders have many-to-many relationship with stock items because purchase order can contain many stock items, and a stock item can appear on many purchase orders.

The usual way to manage the many-to-many relationship between purchase orders and stock is to introduce another entity called a line item list. A purchase order can have an arbitrary number of line items, but each line item belongs to a single purchase order. A stock item can appear on many line items, but each line item refers to a single stock item.

Table 7-1 lists the required information about each of these entities for an application that manages customer orders needs.

##### Table 7-1 Information Required about Entities in the Purchase Order Example

Entity | Required Information
--- | ---
`Customer` | Contact information
`Stock` | Item identification, cost, and taxability code
`Purchase Order` | Customer, order and ship dates, shipping address
`Line Item List` | Stock item, quantity, price, discount for each line item

The problem is that the real-world attributes entities are complex, and so they each require a complex set of attributes to map their data structure. An address contains attributes such as street, city, state, and zipcode. A customer may have several phone numbers. The line item list is an entity in its own right and also an attribute of a purchase order. Standard built-in types cannot represent them directly. The object-relational approach makes it possible to handle this rich structure in different ways.

### Part 1: Relational Approach

The relational approach normalizes entities and their attributes, and structures the customer, purchase order, and stock entities into tables. It breaks addresses into their standard components. It sets an arbitrary limit on the number of telephone numbers a customer can have and assigns a column to each.

The relational approach separates line items from their purchase orders and puts them into a table of their own. The table has columns for foreign keys to the stock and purchase order tables.

#### Tables

The relational approach results in the following tables:

    CREATE TABLE customer_info (
      custno       NUMBER,
      custname     VARCHAR2(200),
      street       VARCHAR2(200),
      city         VARCHAR2(200),
      state        CHAR(2),
      zip          VARCHAR2(20),
      phone1       VARCHAR2(20),
      phone2       VARCHAR2(20),
      phone3       VARCHAR2(20),
      PRIMARY KEY (custno)
      ) ;

    CREATE TABLE purchase_order (
      pono         NUMBER,
      custno       NUMBER REFERENCES customer_info,
      orderdate    DATE,
      shiptodate   DATE,
      shiptostreet VARCHAR2(200),
      shiptocity   VARCHAR2(200),
      shiptostate  CHAR(2),
      shiptozip    VARCHAR2(20),
      PRIMARY KEY (pono)
      ) ;

    CREATE TABLE stock_info (
      stockno      NUMBER PRIMARY KEY,
      cost         NUMBER,
      tax_code     NUMBER
      ) ;

    CREATE TABLE line_items (
      lineitemno   NUMBER,
      pono         NUMBER REFERENCES purchase_order,
      stockno      NUMBER REFERENCES stock_info,
      quantity     NUMBER,
      discount     NUMBER,
      PRIMARY KEY (pono, lineitemno)
      ) ;

The first table, `CUSTOMER_INFO`, stores information about customers. It does not refer to the other tables, but the `PURCHASE_ORDER` table contains a `CUSTNO` column, which contains a foreign key to the `CUSTOMER_INFO` table.

The foreign key implements the many-to-one relationship of purchase orders to customers. Many purchase orders might come from a single customer, but only one customer issues a given purchase order.

The `LINE_ITEMS` table contains foreign keys `PONO` to the `PURCHASE_ORDER` table and `STOCKNO` to the `STOCK_INFO` table.

Inserting Values

In an application based on the tables defined in the previous section, statements such as the following insert data into the tables:

    INSERT INTO customer_info
      VALUES (1, 'Jean Nance', '2 Avocet Drive',
             'Redwood Shores', 'CA', '95054',
             `415-555-1212', NULL, NULL) ;

    INSERT INTO customer_info
      VALUES (2, 'John Nike', '323 College Drive',
             'Edison', 'NJ', '08820',
             `609-555-1212', `201-555-1212', NULL) ;

    INSERT INTO purchase_order
      VALUES (1001, 1, SYSDATE, '10-MAY-1997',
              NULL, NULL, NULL, NULL) ;

    INSERT INTO purchase_order
      VALUES (2001, 2, SYSDATE, '20-MAY-1997',
             '55 Madison Ave', 'Madison', 'WI', `53715') ;

    INSERT INTO stock_info VALUES(1004, 6750.00, 2) ;
    INSERT INTO stock_info VALUES(1011, 4500.23, 2) ;
    INSERT INTO stock_info VALUES(1534, 2234.00, 2) ;
    INSERT INTO stock_info VALUES(1535, 3456.23, 2) ;

    INSERT INTO line_items VALUES(01, 1001, 1534, 12,  0) ;
    INSERT INTO line_items VALUES(02, 1001, 1535, 10, 10) ;
    INSERT INTO line_items VALUES(10, 2001, 1004,  1,  0) ;
    INSERT INTO line_items VALUES(11, 2001, 1011,  2,  1) ;

#### Selecting

Assuming that values have been inserted into these tables in the usual way, your application would execute queries of the following kind to retrieve the necessary information from the stored data.

##### Customer and Line Item Data for Purchase Order 1001

    SELECT   C.custno, C.custname, C.street, C.city, C.state,
             C.zip, C.phone1, C.phone2, C.phone3,

             P.pono, P.orderdate,

             L.stockno, L.lineitemno, L.quantity, L.discount

     FROM    customer_info  C,
             purchase_order P,
             line_items     L

     WHERE   C.custno = P.custno
      AND    P.pono = L.pono
      AND    P.pono = 1001;

##### Total Value of Each Purchase Order

    SELECT   P.pono, SUM(S.cost * L.quantity)

     FROM    purchase_order P,
             line_items     L,
             stock_info     S

     WHERE   P.pono = L.pono
      AND    L.stockno = S.stockno

     GROUP BY P.pono;

##### Purchase Order and Line Item Data Involving Stock Item 1004

    SELECT  P.pono, P.custno,
            L.stockno, L.lineitemno, L.quantity, L.discount

     FROM   purchase_order P,
            line_items     L

     WHERE    P.pono = L.pono
       AND    L.stockno = 1004;

#### Updating

Given the schema objects described above, you would execute statements such as the following to update the stored data:

##### Update the Quantity for Purchase Order 01 and Stock Item 1001

    UPDATE  line_items

     SET    quantity = 20

     WHERE  pono     = 1
      AND   stockno  = 1001 ;

#### Deleting

In an application based on the tables defined earlier, statements such as the following delete stored data:

##### Delete Purchase Order 1001

    DELETE
     FROM   line_items
     WHERE  pono = 1001 ;

    DELETE
     FROM   purchase_order
     WHERE  pono = 1001 ;

### Part 2: Object-Relational Approach with Object Tables

#### Why a Different Approach May Be Needed

Applications written in third generation languages (3GL) such as C++, are able to implement highly complex user-defined types that encapsulate data with methods. By contrast, SQL provides only basic, scalar types and no way of encapsulating these with relevant operations.

So why not create applications using a 3GL? First, DBMSs provide a functionality that would take millions of person-hours to replicate. Second, one of the problems of information management using 3GLs is that they are not persistent - or, if they are persistent, that they sacrifice security to obtain the necessary performance by way of locating the application logic and the data logic in the same address space. Neither trade-off is acceptable to users of DBMSs for whom both persistence and security are basic requirements.

This leaves the application developer with the problem of simulating complex types by some form of mapping into SQL. Apart from the many person-hours required, this involves serious problems of implementation. You must

- translate from application logic into data logic on `write', and then
- perform the reverse process on `read' (and vica versa).

Obviously, there is heavy traffic back and forth between the client address space and that of the server, with the accompanying decrement in performance. And if client and server are on different machines, the toll may on performance from network roundtrips may be considerable.

O-R technology resolves these problems. In the course of this and the following chapter we will consider examples that implement this new functionality.

#### The Object-Relational (O-R) Way

The O-R approach to the example we have been considering begins with the same entity relationships outlined in "Entities and Relationships" on page 7-3. But user-defined types make it possible to carry more of that structure into the database schema.

Rather than breaking up addresses or the customer's contact phones into unrelated columns in relational tables, the O-R approach defines types to represent them; rather than breaking line items out into a separate table, the O-R approach allows them to stay with their respective purchase orders as nested tables.

In the O-R approach, the main entities - customers, stock, and purchase orders - become objects. Object references express the n: 1 relationships between them. Collection types model their multi-valued attributes.

Given an O-R strategy, there are two approaches to implementation:

- create and populate object tables
- use object views to represent virtual object tables from existing relational data.

The remainder of this chapter develops the O-R schema and shows how to implement it with object tables. Chapter 8, "Object Views-An Extended Example" implements the same schema with object views.

#### Defining Types

The following statements set the stage:

    CREATE TYPE line_item_t ;
    CREATE TYPE purchase_order_t ;
    CREATE TYPE stock_info_t ;

The preceding three statements define incomplete object types. The incomplete definitions notify Oracle that full definitions are coming later. Oracle allows types that refer to these types to compile successfully. Incomplete type declarations are like forward declarations in C and other programming languages.

The next statement defines an array type.

    CREATE TYPE phone_list_t AS VARRAY(10) OF VARCHAR2(20) ;

The preceding statement defines the type `PHONE_LIST_T`. Any data unit of type `PHONE_LIST_T` is a `VARRAY` of up to 10 telephone numbers, each represented by a data item of type `VARCHAR2`.

A list of phone numbers could occupy a `VARRAY` or a nested table. In this case, the list is the set of contact phone numbers for a single customer. A `VARRAY` is a better choice than a nested table for the following reasons:

- The order of the numbers might be important. `VARRAY`s are ordered. Nested tables are unordered.
- The number of phone numbers for a specific customer is small. `VARRAY`s force you to specify a maximum number of elements (10 in this case) in advance. They use storage more efficiently than nested tables which have no special size limitations.
- There is no reason to query the phone number list, so the nested table format offers no benefit.

In general, if ordering and bounds are not important design considerations, designers can use the following rule of thumb for deciding between `VARRAY`s and nested tables: If you need to query the collection, use nested tables; if you intend to retrieve the collection as a whole, use `VARRAY`s.

    CREATE TYPE address_t AS OBJECT (
      street  VARCHAR2(200),
      city    VARCHAR2(200),
      state   CHAR(2),
      zip     VARCHAR2(20)
      ) ;

The preceding statement defines the object type `ADDRESS_T`. Data units of this type represent addresses. All of their attributes are character strings, representing the usual parts of a slightly simplified mailing address.

The next statement defines an object type that uses other user-defined types as building blocks. The object type also has a comparison method.

    CREATE TYPE customer_info_t AS OBJECT (
      custno     NUMBER,
      custname   VARCHAR2(200),
      address    address_t,
      phone_list phone_list_t,

      ORDER MEMBER FUNCTION
        cust_order(x IN customer_info_t) RETURN INTEGER,

      PRAGMA RESTRICT_REFERENCES (
        cust_order,  WNDS, WNPS, RNPS, RNDS)
      ) ;

The preceding statement defines the object type `CUSTOMER_INFO_T`. Data units of this type are objects that represent blocks of information about specific customers. The attributes of a `CUSTOMER_INFO_T` object are a number, a character string, an `ADDRESS_T` object, and a `VARRAY` of type `PHONE_LIST_T`.

Every `CUSTOMER_INFO_T` object also has an associated order method, one of the two types of comparison methods. Whenever Oracle needs to compare two `CUSTOMER_INFO_T` objects, it invokes the CUST_ORDER method to do so.

The two types of comparison methods are map methods and order methods. This application uses one of each for purposes of illustration.

Note: An `ORDER` method must be called for every two objects being compared, whereas a `MAP` method is called once per object. In general, when sorting a set of objects, the number of times an `ORDER` method would be called is more than the number of times a `MAP` method would be called. Given that the system can perform scalar value comparisons very efficiently, coupled with the fact that calling a user-defined function is slower compared to calling a kernel implemented function, sorting objects using the `ORDER` method is relatively slow compared to sorting the mapped scalar values (returned by the `MAP` function).

See Also: Oracle8 Concepts for a discussion of `ORDER` and `MAP` methods. PL/SQL User's Guide and Reference for details of how to use pragma declarations.

The statement does not include the actual PL/SQL program implementing the method `CUST_ORDER`. That appears in a later section.

The next statement completes the definition of the incomplete object type `LINE_ITEM_T` declared at the beginning of this section.

    CREATE TYPE line_item_t AS OBJECT (
      lineitemno NUMBER,
      STOCKREF   REF stock_info_t,
      quantity   NUMBER,
      discount   NUMBER
      ) ;

Data units of type `LINE_ITEM_T` are objects that represent line items. They have three numeric attributes and one `REF` attribute. The `LINE_ITEM_T` models the line item entity and includes an object reference to the corresponding stock object.

    CREATE TYPE line_item_list_t AS TABLE OF line_item_t ;

The preceding statement defines the table type `LINE_ITEM_LIST_T`. A data unit of this type is a nested table, each row of which contains a `LINE_ITEM_T` object. A nested table of line items is better choice to represent the multivalued line item list of a purchase order than a `VARRAY` of `LINE_ITEM_T` objects would be, for the following reasons:

- Querying the contents of line items is likely to be a requirement for most applications. This is an inefficient operation for `VARRAY`s because it involves casting the `VARRAY` to a nested table first.
- Indexing on line item data may be a requirement in some applications. Nested tables allow this, but it is not possible with `VARRAY`s.
- The order of line items is usually unimportant, and the line item number can be used to specify an order when necessary.
- There is no practical upper bound on the number of line items on a purchase order. Using a `VARRAY` requires specifying an upper bound on the number of elements.

The following statement completes the definition of the incomplete object type `PURCHASE_ORDER_T` declared at the beginning of this section.

    CREATE TYPE purchase_order_t AS OBJECT (
      pono           NUMBER,
      custref        REF customer_info_t,
      orderdate      DATE,
      shipdate       DATE,
      line_item_list line_item_list_t,
      shiptoaddr     address_t,

      MAP MEMBER FUNCTION
        ret_value RETURN NUMBER,
        PRAGMA RESTRICT_REFERENCES (
          ret_value, WNDS, WNPS, RNPS, RNDS),

      MEMBER FUNCTION
        total_value RETURN NUMBER,
        PRAGMA RESTRICT_REFERENCES (total_value, WNDS, WNPS)
      ) ;

The preceding statement defines the object type `PURCHASE_ORDER_T`. Data units of this type are objects representing purchase orders. They have six attributes, including a `REF`, a nested table of type `LINE_ITEM_LIST_T`, and an `ADDRESS_T` object.

Objects of type `PURCHASE_ORDER_T` have two methods: `RET_VALUE` and `TOTAL_VALUE`. One is a `MAP` method, one of the two kinds of comparison methods. A MAP method returns the relative position of a given record within the order of records within the object. So, whenever Oracle needs to compare two `PURCHASE_ORDER_T` objects, it implicitly calls the `RET_VALUE` method to do so.

The two pragma declarations provide information to PL/SQL about what sort of access the two methods need to the database.

See Also: PL/SQL User's Guide and Reference for complete details of how to use pragma declarations.

.

The statement does not include the actual PL/SQL programs implementing the methods `RET_VALUE` and `TOTAL_VALUE`. That appears in a later section.

The next statement completes the definition of `STOCK_INFO_T`, the last of the three incomplete object types declared at the beginning of this section.

    CREATE TYPE stock_info_t AS OBJECT (
      stockno    NUMBER,
      cost       NUMBER,
      tax_code   NUMBER
      ) ;

Data units of type `STOCK_INFO_T` are objects representing the stock items that customers order. They have three numeric attributes.

##### Method definitions

This section shows how to specify the methods of the `CUSTOMER_INFO_T` and `PURCHASE_ORDER_T` object types.

    CREATE OR REPLACE TYPE BODY purchase_order_t AS
      MEMBER FUNCTION total_value RETURN NUMBER IS
        i          INTEGER;
        stock      stock_info_t;
        line_item  line_item_t;
        total      NUMBER := 0;
        cost       NUMBER;

      BEGIN
        FOR i IN 1..SELF.line_item_list.COUNT  LOOP

          line_item := SELF.line_item_list(i);
          SELECT DEREF(line_item.stockref) INTO stock FROM DUAL ;

          total := total + line_item.quantity * stock.cost ;

          END LOOP;
        RETURN total;
      END;

      MAP MEMBER FUNCTION ret_value RETURN NUMBER IS
      BEGIN
        RETURN pono;
      END;
    END;

The preceding statement defines the body of the `PURCHASE_ORDER_T` object type, that is, the PL/SQL programs that implement its methods.

##### The RET_VALUE Method

The `RET_VALUE` method is simple: you use it to return the number of its associated `PURCHASE_ORDER_T` object.

##### The TOTAL_VALUE Method

The `TOTAL_VALUE` method uses a number of O-R means to return the sum of the values of the line items of its associated `PURCHASE_ORDER_T` object:

- As already noted, the basic function of the `TOTAL_VALUE` method is to return the sum of the values of the line items of its associated `PURCHASE_ORDER_T` object. The keyword `SELF`, which is implicitly created as a parameter to every function, lets you refer to that object.
- The keyword `COUNT` gives the count of the number of elements in a PL/SQL table or array. Here, in combination with LOOP, the application iterates through all the elements in the collection - in this case, the items of the purchase order. In this way `SELF`.`LINE_ITEM_LIST`.`COUNT` counts the number of elements in the nested table that match the `LINE_ITEM_LIST` attribute of the `PURCHASE_ORDER_T` object, here represented by `SELF`.
- The `DEREF` operator takes a reference value as an argument, and returns a row object. In this case, `DEREF` (`LINE_ITEM`.`STOCKREF`) takes the `STOCKREF` attribute as an argument, and returns `STOCK_INFO_T` object. Looking back to our data definition, you will see that `STOCKREF` is an attribute of the `LINE_ITEM_T` object which is itself an element of the `LINE_ITEM_LIST`. This list object, which we have structured as a nested table, is in turn an attribute of the P`URCHASE_ORDER_T` object represented by `SELF`. This may seem rather complicated until you take it up again from a real-world perspective in which a purchase order (P`URCHASE_ORDER_T`) contains a list (`LINE_ITEM_LIST`) of items (`LINE_ITEM_T`), each of which contains a reference (`STOCKREF`) to information about the item (`STOCK_INFO_T`). The operation which we have been considering simply fetches the required data by O-R means.
- All these entities are abstract datatypes, and as such can be viewed as templates for objects - as P`URCHASE_ORDER_T` is a template for all purchase order objects. How then are to we retrieve the values of actual stock objects? The SQL `SELECT` statement with the explicit `DEREF` call is required, because Oracle does not support implicit dereferencing of `REF`s within PL/SQL programs. The PL/SQL variable `STOCK` is of type `STOCK_INFO_T`. The select statement sets it to the object represented by `DEREF` (`LINE_ITEM`.`STOCKREF`). And this object is the actual stock item referred to in the i-th line item
- Having retrieved the stock item in question, the next step is to compute its cost. The program refers to the stock item's cost as `STOCK`.`COST`, the `COST` attribute of the `STOCK` object. But to compute the cost of the item we also need to know the quantity of items ordered. In our application, the term `LINE_ITEM`.`QUANTITY` represents the `QUANTITY` attribute of each `LINE_ITEM_T` object.

The remainder of the method program is straightforward. The loop sums the extended values of the line items, and the method returns the total as its value.

##### The CUST_ORDER Method

The following statement defines the `CUST_ORDER` method of the `CUSTOMER_INFO_T` object type.

    CREATE OR REPLACE TYPE BODY customer_info_t AS
      ORDER MEMBER FUNCTION
      cust_order (x IN customer_info_t) RETURN INTEGER IS
      BEGIN
        RETURN custno - x.custno;
      END;
    END;

As mentioned earlier, the function of the `CUST_ORDER` operation is to compare information about two customer orders. The mechanics of the operation are quite simple. The order method `CUST_ORDER` takes another `CUSTOMER_INFO_T` object as an input argument and returns the difference of the two `CUSTNO` numbers. Since it subtracts the `CUSTNO` of the other `CUSTOMER_INFO_T` object from its own object's `CUSTNO`, the method returns (a) a negative number if its own object has a smaller value of `CUSTNO`, or (b) a positive number if its own has a larger value of `CUSTNO`, or (c) zero if the two objects have the same value of `CUSTNO` - in which case it is referring to itself! If `CUSTNO` has some meaning in the real world (e.g., lower numbers are created earlier in time than higher numbers), the actual value returned by this function could be useful.

This completes the definition of the user-defined types used in the purchase order application. Note that none of the declarations create tables or reserve data storage space.

#### Creating Object Tables

To this point the example is the same, whether you plan to create and populate object tables or implement the application with object views on top of the relational tables that appear in the first part of the example. The remainder of this chapter continues the example using object tables. Chapter 8, "Object Views-An Extended Example" picks up from this point and continues the example with object views.

Generally, you can think of the relationship between the "objects" and "tables" in the following way:

- classes, which represent entities, map to tables,
- attributes map to columns, and
- objects map to rows.

Viewed in way, each table is an implicit type whose objects (specific rows) each have the same attributes (the column values). The creation of explicit abstract datatypes and of object tables introduce a new level of functionality.

##### The Object Table CUSTOMER_TAB

##### Creating object tables: the basic syntax.

The following statement defines an object table `CUSTOMER_TAB` to hold objects of type `CUSTOMER_INFO_T`.

    CREATE TABLE customer_tab OF customer_info_t
     (custno PRIMARY KEY);

As you can see, there is a syntactic difference in the definition of object tables, namely the use of the term "OF". You may recall that we earlier defined the attributes of `CUSTOMER_INFO_T` objects as:

    custno     NUMBER
    custname   VARCHAR2(200)
    address    address_t
    phone_list phone_list_t
    po_list    po_reflist_t

This means that the table `CUSTOMER_TAB` has columns of `CUSTNO`, `CUSTNAME`, `ADDRESS`, `PHONE_LIST` and `PO_LIST`, and that each row is an object of type `CUSTOMER_INFO_T`. And, as you will see, this notion of row object offers a significant advance in functionality.

##### Abstract datatypes as a template for object tables.

Note first that the fact that there is a type `CUSTOMER_INFO_T` means that you could create numerous tables of type `CUSTOMER_INFO_T`. For instance, you could create a table `CUSTOMER_TAB2` also of type `CUSTOMER_INFO_T`. By contrast, without this ability, you would have to define each table individually.

Being able to create tables of the same type does not mean that you cannot introduce variations. Note that the statement by which we created `CUSTOMER_TAB` defined a primary key constraint on the `CUSTNO` column. This constraint applies only to this table. Another object table of `CUSTOMER_INFO_T` objects (e.g., `CUSTOMER_TAB2`) need not satisfy this constraint. This illustrates an important point: constraints apply to tables, not to type definitions.

##### Object tables with embedded objects.

Examining the definition of `CUSTOMER_TAB`, you will see that the `ADDRESS` column contains `ADDRESS_T` objects. Put another way: an abstract datatype may have attributes that are themselves abstract datatypes. When these types are instantiated as objects, the included objects are instantiated at the same time (unless they allow for `NULL` values, in which case place-holders for their values are created). `ADDRESS_T` objects have attributes of built-in types which means that they are leaf-level scalar attributes of `CUSTOMER_INFO_T`. Oracle creates columns for `ADDRESS_T` objects and their attributes in the object table `CUSTOMER_TAB`. You can refer to these columns using the dot notation. For example, if you wish to build an index on the `ZIP` column, you can refer to it as `ADDRESS`.`ZIP`.

The `PHONE_LIST` column contains `VARRAY`s of type `PHONE_LIST_T`. You may recall that we defined each object of type `PHONE_LIST_T` as a `VARRAY` of up to 10 telephone numbers, each represented by a data item of type `VARCHAR2`.

    CREATE TYPE phone_list_t AS VARRAY(10) OF VARCHAR2(20) ;

Since each `VARRAY`s of type `PHONE_LIST_T` can contain no more than 200 characters (10 x 20), plus a small amount of overhead. Oracle stores the `VARRAY` as a single data unit in the `PHONE_LIST` column. Oracle stores `VARRAY`s that exceed 4000 bytes in `BLOB`s which means that they are stored outside the table. This raises an interesting question to which we will return: How does the DBMS reference these external objects?

##### The Object Table STOCK_TAB

The next statement creates an object table for `STOCK_INFO_T` objects:

    CREATE TABLE stock_tab OF stock_info_t
     (stockno PRIMARY KEY) ;

This does not introduce anything new. The statement creates the `STOCK_TAB` object table. Since Each row of the table is a `STOCK_INFO_T` object having three numeric attributes:

      stockno    NUMBER,
      cost       NUMBER,
      tax_code   NUMBER

Oracle assigns a column for each attribute, and the CREATE TABLE statement places a primary key constraint on the `STOCKNO` column.

##### The Object Table PURCHASE_TAB

The next statement defines an object table for `PURCHASE_ORDER_T` objects:

    CREATE TABLE purchase_tab OF purchase_order_t (
      PRIMARY KEY (pono),
      SCOPE FOR (custref) IS customer_tab
      )
      NESTED TABLE line_item_list STORE AS po_line_tab ;

The preceding statement creates the `PURCHASE_TAB` object table. Each row of the table is a `PURCHASE_ORDER_T` object. Attributes of `PURCHASE_ORDER_T` objects are:

      pono           NUMBER
      custref        REF customer_info_t
      orderdate      DATE
      shipdate       DATE
      line_item_list line_item_list_t
      shiptoaddr     address_t

##### The REF operator.

The first new element introduced here has to do with the way the statement places a scope on the `REF`s in the `CUSTREF` column. When there is no restriction on scope (the default case), the `REF` operator allows you to reference any row object. However, these `CUSTREF` `REF`s can refer only to row objects in the `CUSTOMER_TAB` object table. The scope limitation applies only to `CUSTREF` columns of the `CUSTOMER_TAB` object table. It does not apply to the `CUSTREF` attributes of `PURCHASE_ORDER_T` objects that might be stored in any other object table.

##### Nested tables.

A second new element has to do with the fact that each row has a nested table column `LINE_ITEM_LIST`. The last line of the statement creates the table `PO_LINE_TAB` to hold the `LINE_ITEM_LIST` columns of all of the rows of the `PURCHASE_TAB` table. Nested tables are particularly well-suited to coding master-detail relationships, such as we are considering in this purchase order example. As we will discuss, nested tables can also do much to remove the complexity of relational joins from applications.

All the rows of a nested table are stored in a separate storage table. A hidden column in the storage table, called the `NESTED_TABLE_ID` matches the rows with their corresponding parent row. All the elements in the nested table belonging to a particular parent have the same `NESTED_TABLE_ID` value.

For example, all the elements of the nested table of a given row of `PURCHASE_TAB` have the same value of `NESTED_TABLE_ID`. The nested table elements that belong to a different row of `PURCHASE_TAB` have a different value of `NESTED_TABLE_ID`.

The top level attributes of the nested table type map to columns in the storage table. A nested table whose elements are not of an object type has a single unnamed column. Oracle recognizes the keyword `COLUMN_VALUE` as representing the name of that column. For example, to place a scope on the REF column in a nested table of REFs, we can use the COLUMN_VALUE column name to refer to it.

Oracle creates columns in `CUSTOMER_TAB` for the remaining leaf level scalar attributes of `PURCHASE_ORDER_T` objects, namely, `ORDERDATE`, `SHIPDATE`, and the attributes of the `ADDRESS_T` object in `SHIPTOADDR`.

At this point all of the tables for the purchase order application are in place. The next section shows how to add additional specifications to these tables.

#### Altering the Tables

The next statement alters the `PO_LINE_TAB` storage table, which holds the `LINE_ITEM_LIST` nested table columns of the object table `PURCHASE_TAB`, to place a scope on the `REF`s it contains.

    ALTER TABLE po_line_tab
      ADD (SCOPE FOR (stockref) IS stock_tab) ;

The `PO_LINE_TAB` storage table holds nested table columns of type `LINE_ITEM_LIST_T`. The definition of that type (from earlier in the chapter) is:

    CREATE TYPE line_item_list_t AS TABLE OF line_item_t ;

An attribute of a `LINE_ITEM_T` object, and hence one column of the `PO_LINE_TAB` storage table, is `STOCKREF`, which is of type `REF` `STOCK_INFO_T`. The object table `STOCK_TAB` holds row objects of type `STOCK_INFO_T`. The alter statement restricts the scope of the `REF`s in the `STOCKREF` column to the object table `STOCK_TAB`.

The next statement further alters the `PO_LINE_TAB` storage table to specify its index storage.

    ALTER TABLE po_line_tab
      STORAGE (NEXT 5K PCTINCREASE 5 MINEXTENTS 1 MAXEXTENTS 20) ;

The next statement creates an index on the `PO_LINE_TAB` storage table:

    CREATE INDEX po_nested_in
      ON         po_line_tab (NESTED_TABLE_ID) ;

A storage table for a nested table column of an object table has a hidden column called `NESTED_TABLE_ID`. The preceding statement creates an index on that column, making access to the contents of `LINE_ITEM_LIST` columns of the `PURCHASE_TAB` object table more efficient.

The next statement shows how to use `NESTED_TABLE_ID` to enforce uniqueness of a column of a nested table within each row of the enclosing table. It creates a unique index on the `PO_LINE_TAB` storage table. That table holds the `LINE_ITEM_LIST` columns of all of the rows of the `PURCHASE_TAB` table.

    CREATE UNIQUE INDEX po_nested
      ON                po_line_tab (NESTED_TABLE_ID, lineitemno) ;

By including the `LINEITEMNO` column in the index key and specifying a unique index, the statement ensures that the `LINEITEMNO` column contains distinct values within each purchase order.

#### Inserting Values

The statements in this section show how to insert the same data into the object tables just created as the statements on page 7-5 insert into the relational tables of the first part of the example.

##### stock_tab

    INSERT INTO stock_tab VALUES(1004, 6750.00, 2);
    INSERT INTO stock_tab VALUES(1011, 4500.23, 2);
    INSERT INTO stock_tab VALUES(1534, 2234.00, 2);
    INSERT INTO stock_tab VALUES(1535, 3456.23, 2);

##### customer_tab

    INSERT INTO customer_tab
      VALUES (
        1, `Jean Nance',
        address_t(`2 Avocet Drive', `Redwood Shores', `CA', `95054'),
        phone_list_t(`415-555-1212')
        ) ;

    INSERT INTO customer_tab
      VALUES (
        2, `John Nike',
        address_t(`323 College Drive', `Edison', `NJ', `08820'),
        phone_list_t(`609-555-1212',`201-555-1212')
        ) ;

##### purchase_tab

    INSERT INTO purchase_tab
      SELECT  1001, REF(C),
              SYSDATE,'10-MAY-1997',
              line_item_list_t(),
              NULL
       FROM   customer_tab C
       WHERE  C.custno = 1 ;

The preceding statement constructs a `PURCHASE_ORDER_T` object with the following attributes:

      pono             1001
      custref          REF to customer number 1
      orderdate        SYSDATE
      shipdate         10-MAY-1997
      line_item_list   an empty line_item_list_t
      shiptoaddr       NULL

The statement uses a query to construct a `REF` to the row object in the `CUSTOMER_TAB` object table that has a `CUSTNO` value of 1.

The next statement uses a flattened subquery, signaled by the keyword `THE`, to identify the target of the insertion, namely the nested table in the `LINE_ITEM_LIST` column of the row object in the `PURCHASE_TAB` object table that has a `PONO` value of 1001.

    INSERT INTO THE (
      SELECT  P.line_item_list
       FROM   purchase_tab P
       WHERE  P.pono = 1001
      )
      SELECT  01, REF(S), 12, 0
       FROM   stock_tab S
       WHERE  S.stockno = 1534;

The preceding statement inserts a line item into the nested table identified by the flattened subquery. The line item that it inserts contains a `REF` to the row object in the object table `STOCK_TAB` that has a `STOCKNO` value of 1534.

The following statements are similar to the preceding two.

    INSERT INTO purchase_tab
      SELECT  2001, REF(C),
              SYSDATE,'20-MAY-1997',
              line_item_list_t(),
              address_t(`55 Madison Ave','Madison','WI','53715')
       FROM   customer_tab C
       WHERE  C.custno = 2;

    INSERT INTO THE (
      SELECT  P.line_item_list
       FROM   purchase_tab P
       WHERE  P.pono = 1001
      )
      SELECT  02, REF(S), 10, 10
       FROM   stock_tab S
       WHERE  S.stockno = 1535;

    INSERT INTO THE (
      SELECT  P.line_item_list
       FROM   purchase_tab P
       WHERE  P.pono = 2001
      )
      SELECT  10, REF(S), 1, 0
       FROM   stock_tab S
       WHERE  S.stockno = 1004;

    INSERT INTO THE (
      SELECT  P.line_item_list
       FROM   purchase_tab P
       WHERE  P.pono = 2001
      )
      VALUES( line_item_t(11, NULL, 2, 1) ) ;

The next statement uses a table alias to refer to the result of the flattened subquery

    UPDATE THE (
      SELECT  P.line_item_list
       FROM   purchase_tab P
       WHERE  P.pono = 2001
      ) plist

      SET plist.stockref =
       (SELECT REF(S)
         FROM  stock_tab S
         WHERE S.stockno = 1011
         )

      WHERE plist.lineitemno = 11 ;

#### Selecting

The following query statement implicitly invokes a comparison method. It shows how Oracle uses the ordering of `PURCHASE_ORDER_T` object types that the comparison method defines:

    SELECT  p.pono
     FROM   purchase_tab p
     ORDER BY VALUE(p);

The preceding instruction causes Oracle to invoke the map method `RET_VALUE` for each `PURCHASE_ORDER_T` object in the selection. Since that method simply returns the value of the object's `PONO` attribute, the result of the selection is a list of purchase order numbers in ascending numerical order.

The following queries correspond to the queries in "Selecting" on page 7-6.

##### Customer and Line Item Data for Purchase Order 1001

    SELECT  DEREF(p.custref), p.shiptoaddr, p.pono,
            p.orderdate, line_item_list

     FROM   purchase_tab p

     WHERE  p.pono = 1001 ;

##### Total Value of Each Purchase Order

    SELECT   p.pono, p.total_value()

     FROM    purchase_tab p ;

##### Purchase Order and Line Item Data Involving Stock Item 1004

    SELECT   po.pono, po.custref.custno,

             CURSOR (
               SELECT  *
                FROM   TABLE (po.line_item_list) L
                WHERE  L.stockref.stockno = 1004
               )

     FROM    purchase_tab po ;

#### Deleting

The following example has the same effect as the two deletions needed in the relational case (see "Deleting" on page 7-7). In this case Oracle automatically deletes all line items belonging to the deleted purchase order. The relational case requires a separate step.

##### Delete Purchase Order 1001

    DELETE
     FROM   purchase_order
     WHERE  pono = 1001 ;

This concludes the object table version of the purchase order example. The next chapter develops an alternative version of the example using relational tables and object views.

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