Release 8.0
A54642_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8
Server Application Developer's Guide Release 8.0 A54642_01 |
|
This chapter contains an extended example of how to use object views. The chapter has the following major sections:
Object views are virtual object tables, materialized out of data from tables or views. For a discussion of object views and how to use them, see Oracle8 Server 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 Oracle8 Server 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 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 Programmer's Guide to the Oracle Call Interface for a complete discussion of those facilities.
Chapter 7, "User-Defined Datatypes -An Extended Example" develops a purchase order example by following these steps:
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.
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 definition of the customer_info_t object type appears on page 7-9. 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 7-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 7-8.
The definition of the stock_info_t object type appears on page 7-11. 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 7-4.
The selection used to materialize the object view is extremely simple, because the object type definition and the table definition correspond exactly.
The definition of the purchase_order_t object type appears on page 7-10. 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 7-4, the purchase_order table, which is defined on page 7-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.
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.
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.
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.
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.
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 cutomer 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.
The three queries in "Selecting" on page 7-19 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.
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|