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

# 8
Object Views-An Extended Example

This chapter contains an extended example of how to use object views. The chapter has the following major sections:

- Introduction
- Purchase Order Example

## Introduction

Object views are virtual object tables, materialized out of data from tables or views.

See Also: For a discussion of object views and how to use them, see Oracle8 Concepts.

The example in this chapter illustrates the most important aspects of defining and using object views. The definitions of triggers 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 the objects and collections defined by object views and manipulating them on the client side.

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

## Purchase Order Example

Chapter 7, "User-Defined Datatypes - An Extended Example" develops a purchase order example by following these steps:

1. Establish the entities and relationships.
2. Implement the entity-relationship structure by creating and populating relational tables.
3. Define an object-relational schema of user-defined types to model the entity-relationship structure.
4. Implement the entity-relationship structure using the object-relational schema to create and populate object tables.

The approach in this chapter uses the same initial steps but a different final step. Rather than creating and populating object tables, this approach uses object views to materialize virtual object tables out of data in the relational tables.

### Defining Object Views

The example developed in Chapter 7 contains three object tables: `CUSTOMER_TAB`,`STOCK_TAB`, and `PURCHASE_TAB`. this chapter contains three corresponding object views: `CUSTOMER_VIEW`, `STOCK_VIEW`, and `PURCHASE_VIEW`.

The statement that creates an object view has four parts:

- The name of the view.
- The name of the object type it is based on.
- The source of the primary-key-based object identifier.
- A selection that populates the virtual object table corresponding to the object type.

#### The customer_view View

The definition of the `CUSTOMER_INFO_T` object type appears on page 10. This object view is based on that object type.

    CREATE OR REPLACE VIEW
    customer_view OF customer_info_t WITH OBJECT OID(custno) AS
      SELECT  C.custno, C.custname,
              address_t(C.street, C.city, C.state, C.zip),
              phone_list_t (C.phone1, C.phone2, C.phone3)
       FROM   customer_info C ;

This object view selects its data from the `CUSTOMER_INFO` table. The definition of this table appears on page 4.

The `CUSTOMER_INFO_T` object type has the following attributes:

    custno     NUMBER
    custname   VARCHAR2(200)
    address    address_t
    phone_list phone_list_t

The object view definition takes the `CUSTNO` and `CUSTNAME` attributes from correspondingly named columns of the `CUSTOMER_INFO` table. It uses the `STREET`, `CITY`, `STATE`, and `ZIP` columns of the `CUSTOMER_INFO` table as arguments to the constructor function for the `ADDRESS_T` object type, which is defined on page 10.

#### The stock_view View

The definition of the `STOCK_INFO_T` object type appears on page 13. This object view is based on that object type.

    CREATE OR REPLACE VIEW
    stock_view OF stock_info_t WITH OBJECT OID(stockno) AS
      SELECT  *
       FROM   stock_info ;

This object view selects its data from the `STOCK_INFO` table. The definition of this table appears on page 5.

The selection used to materialize the object view is extremely simple, because the object type definition and the table definition correspond exactly.

#### The purchase_view View

The definition of the `PURCHASE_ORDER_T` object type appears on page 12. This object view is based on that object type.

    CREATE OR REPLACE VIEW
    purchase_view OF purchase_order_t WITH OBJECT OID (pono) AS
      SELECT  P.pono,
              MAKE_REF (customer_view, P.custno),
              P.orderdate, P.shiptodate,
              CAST (
                MULTISET (
                  SELECT  line_item_t (
                            L.lineitemno,
                            MAKE_REF(stock_view, L.stockno),
                            L.quantity, L.discount
                            )
                   FROM   line_items L
                   WHERE  L.pono= P.pono
                  )
                AS line_item_list_t
                ),
               address_t (P.shiptostreet, P.shiptocity,
                          P.shiptostate,  P.shiptozip)
       FROM   purchase_order P ;

This object view is based on the `LINE_ITEMS` table, which is defined on page 5, the `PURCHASE_ORDER` table, which is defined on page 4, and the `CUSTOMER_VIEW` and `STOCK_VIEW` object views defined in the two previous sections.

The `PURCHASE_ORDER_T` object type has the following attributes:

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

The object view definition takes its `PONO` column from the `PONO` column of the `PURCHASE_ORDER` table. It uses the expression `MAKE_REF` (`CUSTOMER_VIEW`, `CUSTNO`) to create a `REF` to the row object in the customer_view object view identified by `CUSTNO`. That `REF` becomes the `CUSTREF` column.

The object view definition takes its `ORDERDATE` and `SHIPDATE` columns from the `ORDERDATE` and `SHIPTODATE` columns of the `PURCHASE_ORDER` table.

The object view definition uses the term

              CAST (
                MULTISET (
                  SELECT  line_item_t (
                            L.lineitemno,
                            MAKE_REF(stock_view, L.stockno),
                            L.quantity, L.discount
                            )
                   FROM   line_items L
                   WHERE  L.pono= P.pono
                  )
                AS line_item_list_t
                ),

to materialize the `LINE_ITEM_LIST` column of the object view. At the innermost level of this expression, the operator `MAKE_REF`(`STOCK_VIEW`, `STOCKNO`) builds a `REF` to the row object in the `STOCK_VIEW` object view identified by `STOCKNO`. That `REF` becomes one of the input arguments to the constructor function for the `LINE_ITEM_T` object type. The other arguments come from the `LINEITEMNO`, `QUANTITY`, and `DISCOUNT` columns of the `LINE_ITEMS` table.

The selection results in a set of `LINE_ITEM_T` objects, one for each row of the `LINE_ITEMS` table whose `PONO` column matches the `PONO` column of the row of the `PURCHASE_ORDER` table that is currently being examined in the outer selection. The `MULTISET` operator tells Oracle to regard the set of `LINE_ITEM_T` objects as a multiset, making it an appropriate argument for the `CAST` operator, which turns it into a nested table of type `LINE_ITEM_LIST_T`, as specified by the AS clause.

The resulting nested table becomes the `LINE_ITEM_LIST` column of the object view.

Finally, the definition uses the `SHIPTOSTREET`, `SHIPTOCITY`, `SHIPTOSTATE`, and `SHIPTOZIP` columns of the `PURCHASE_ORDER` table as arguments to the constructor function for the `ADDRESS_T` object type to materialize the `SHIPTOADDR` column of the object view.

### Updating the Object Views

Oracle provides `INSTEAD` `OF` triggers as a way to update complex object views. This section presents the `INSTEAD` `OF` triggers necessary to update the object views just defined.

Oracle invokes an object view's `INSTEAD` `OF` trigger whenever a command directs it to change the value of any attribute of a row object in the view. Oracle makes both the current value and the requested new value of the row object available to the trigger program. It recognizes the keywords :`OLD` and :`NEW` as representing the current and new values.

#### INSTEAD OF Trigger for purchase_view

    CREATE OR REPLACE TRIGGER
    poview_insert_tr INSTEAD OF INSERT ON purchase_view

    DECLARE
     line_itms     line_item_list_t ;
     i             INTEGER ;
     custvar       customer_info_t ;
     stockvar      stock_info_t ;
     stockvartemp  REF stock_info_t ;

    BEGIN
     line_itms := :NEW.line_item_list ;

     SELECT DEREF(:NEW.custref) INTO custvar FROM DUAL ;

     INSERT INTO purchase_order VALUES (
       :NEW.pono, custvar.custno, :NEW.orderdate, :NEW.shipdate,
       :NEW.shiptoaddr.street,    :NEW.shiptoaddr.city,
       :NEW.shiptoaddr.state,     :NEW.shiptoaddr.zip ) ;

     FOR i IN 1..line_itms.COUNT LOOP
       stockvartemp := line_itms(i).stockref ;
       SELECT DEREF(stockvartemp) INTO stockvar FROM DUAL ;

       INSERT INTO line_items VALUES (
         line_itms(i).lineitemno, :NEW.pono, stockvar.stockno,
         line_itms(i).quantity,    line_itms(i).discount ) ;
     END LOOP ;

    END ;

This trigger program inserts new values into the `PURCHASE_ORDER` table. Then, in a loop, it inserts new values into the `LINE_ITEMS` table for each `LINE_ITEM_T` object in the nested table in the new `LINE_ITEM_LIST` column.

The use of the `STOCKVARTEMP` variable is an alternative to implicitly dereferencing the `REF` represented by `LINE_ITMS`(i).`STOCKREF`.

#### INSTEAD OF Trigger for customer_view

    CREATE OR REPLACE TRIGGER
    custview_insert_tr INSTEAD OF INSERT ON customer_view

    DECLARE
      phones  phone_list_t;
      tphone1 customer_info.phone1%TYPE := NULL;
      tphone2 customer_info.phone2%TYPE := NULL;
      tphone3 customer_info.phone3%TYPE := NULL;
    BEGIN
      phones := :NEW.phone_list;

      IF phones.COUNT > 2 THEN
        tphone3 := phones(3);
      END IF;

      IF phones.COUNT > 1 THEN
        tphone2 := phones(2);
      END IF;

      IF phones.COUNT > 0 THEN
        tphone1 := phones(1);
      END IF;

      INSERT INTO customer_info VALUES (
       :NEW.custno,       :NEW.custname,      :NEW.address.street,
       :NEW.address.city, :NEW.address.state, :NEW.address.zip,
        tphone1,           tphone2,            tphone3);
    END ;

This trigger function updates the `CUSTOMER_INFO` table with the new information. Most of the program deals with updating the three phone number columns of the customer table from the :`NEW`.`PHONE_LIST` `VARRAY` of phone numbers. The `IF` statements assure that the program does not attempt to access :`NEW`.`PHONE_LIST` elements with indexes greater than :`NEW`.`PHONE_LIST`.`COUNT`.

There is a slight mismatch between these two representations, because the `VARRAY` is defined hold up to 10 numbers, while the customer table has only three phone number columns. The trigger program discards :`NEW`.`PHONE_LIST` elements with indexes greater than 3.

#### INSTEAD OF Trigger for stock_view

    CREATE OR REPLACE TRIGGER
    stockview_insert_tr INSTEAD OF INSERT ON stock_view

    BEGIN
      INSERT INTO stock_info VALUES (
        :NEW.stockno, :NEW.cost, :NEW.tax_code );
    END ;

This trigger function updates the `STOCK_INFO` table with the new information.

### Sample Updates

The following statement fires the `CUSTOMER_VIEW` trigger.

    INSERT INTO customer_view VALUES (
       13, `Ellan White',
       address_t(`25 I Street', `Memphis', `TN', `05456'),
                  phone_list_t(`615-555-1212') );

The preceding statement inserts a new customer into the database via the `CUSTOMER_VIEW` object view.

The following statement fires the `PURCHASE_VIEW` trigger.

    INSERT INTO purchase_view
      SELECT  3001, REF(c), SYSDATE, SYSDATE,
              CAST(
                MULTISET(
                   SELECT line_item_t(41, REF(S), 20, 1)
                    FROM  stock_view S
                    WHERE S.stockno = 1535
                    )
                AS line_item_list_t
                ),
              address_t(`22 Nothingame Ave','Cockstown','AZ','44045')

       FROM   customer_view c
       WHERE  c.custno = 1

The preceding statement inserts a new purchase order into the database via the `PURCHASE_VIEW` object view. Customer number 1 has ordered 20 of stock item 1535. The statement assigns number 3001 to the purchase order and number 41 to the line item.

### Selecting

The three queries in "Selecting" on page 7-24 work exactly as written, but with the object table name `PURCHASE_TAB` replaced by the object view name `PURCHASE_VIEW`. Queries involving other object tables work with the analogous name replacement.

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