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

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:

Establish the entities and relationships.
Implement the entity-relationship structure by creating and populating relational tables.
Define an object-relational schema of user-defined types to model the entity-relationship structure.
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.

8Object Views-An Extended Example