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 user-defined types. The chapter has the following major sections:
User-defined types are schema objects in which users formalize the data structures and operations that appear in their applications. For a discussion of user-defined types and how to use them, see Oracle8 Server Concepts.
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 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 objects and collections and manipulating them on the client side. See Programmer's Guide to the Oracle Call Interface for a complete discussion of those facilities.
This example is based on a simple business activity: managing the data in customer orders. The example is 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 database to support the basic activity. The first part implements the database using only Oracle's built-in datatypes. This is called the relational approach. It creates tables to hold the application's data and uses well-known techniques to implement the application's entity relationships.
The second and third parts use user-defined types to translate the entities and relationships directly into database terms. This is called the object-relational approach. The second and third parts use identical user-defined types. 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. It uses these instead of the 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 materialize virtual object tables.
The basic entities in this example are
Purchase orders have an n : 1 relationship with customers, because a customer can place many orders, but a given purchase order is from a single customer.
Purchase orders have an m : n relationship with the stock. A purchase order can contain many stock items, and a stock item can appear on many purchase orders.
The usual way to manage the m : n 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 information about each of these entities that an application to manage customer orders needs.
At this point the attributes describing the entities are complex. Built-in types cannot represent them directly. An address contains attributes like 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. The relational and object-relational approaches map this rich structure in different ways.
The relational approach normalizes entities and their attributes. It puts 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.
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.
In an application based on the tables defined in the previous section, statements like 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) ;
In an application based on the tables defined earlier, queries like the following provide necessary information from the stored data.
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;
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;
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;
In an application based on the tables defined earlier, statements like the following update the stored data:
UPDATE line_items SET quantity = 20 WHERE pono = 1 AND stockno = 1001 ;
In an application based on the tables defined earlier, statements like the following delete stored data:
DELETE FROM line_items WHERE pono = 1001 ; DELETE FROM purchase_order WHERE pono = 1001 ;
The object-relational approach begins with the entity relationships outlined in "Entities and Relationships" on page 7-3. User-defined types make it possible to carry more of that structure into the schema.
Rather than breaking up addresses or the customer's contact phones into unrelated columns in relational tables, the object-relational approach defines types to represent them. Rather than breaking line items out into a separate table, the object-relational approach allows them to stay with their respective purchase orders as nested tables.
In the object-relational 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 object-relational schema, there are two approaches to implementing it: create and populate object tables or use object views to materialize virtual object tables out of existing relational data.
The remainder of this chapter develops the object-relational schema and shows how to implement it with object tables. Chapter 8, "Object Views-An Extended Example" implements the same schema with object views.
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:
In general, if ordering and bounds are not important design considerations, designers can use the following rule of thumb for deciding between VARRAYs and nested tables: If you need to query the collection, use nested tables; if you intend to retrieve the collection as a whole, use VARRAYs.
The next statement defines an object type.
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 that 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. See Oracle8 Server Concepts for a discussion of order and map methods and how to choose the right one for a given application. This application uses one of each to for purposes of illustration.
The pragma declaration provides information to PL/SQL about what sort of access the method needs to the database. See 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 ) ;
The preceding statement defines the object type line_item_t. Data units of that type are objects that represent line items. They have three numeric attributes and one REF attribute.
The next statement creates a table type.
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 that 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:
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. 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 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 ) ;
The preceding statement defines the object type stock_info_t. Data units of this type are objects representing the stock items that customers order. They have three numeric attributes.
This section shows how to specify the methods of the customer_info_t and purchase_order_t object types. The following statement defines the methods of the purchase_order_t object type.
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 total_value method illustrates a number of important concepts and needs further explanation. The ret_value method, on the other hand is simple. It merely returns the number of its associated purchase_order_t object. Since ret_value is a map method, Oracle uses the value it returns, namely the purchase order number, as the basis for comparing purchase_order_t objects.
The preceding statement defines the body of the purchase_order_t object type, that is, the PL/SQL programs that implement its methods.
The basic function of the total_value method is to return the sum of the extended values of the line items of its associated purchase_order_t object. The keyword SELF refers to that object.
The keyword COUNT is the name of a system-generated attribute of every collection type. It contains the number of elements in the collection. The term SELF.line_item_list.COUNT represents the number of elements in the nested table that is the line_item_list attribute of the purchase_order_t object represented by SELF.
The term DEREF (line_item.stockref) represents the stock_info_t object referred to by the stockref attribute of the line_item_t object that is the i-th element of the nested table that is the line_item_list attribute of the purchase_order_t object represented by SELF.
The SQL SELECT statement with the explicit DEREF call is required, because Oracle does not support implicit dereferencing of REFs 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). That object is the stock item referred to in the i-th line item, so the program refers to the stock item's cost as stock.cost, the cost attribute of the stock_info_t object that stock refers to.
The other term needed to compute the extended cost of the i-th line item is the quantity associated with that line: line_item.quantity. This term represents the quantity attribute of the line_item_t object that is the i-th element of the nested table that is the line_item_list attribute of the purchase_order_t object represented by SELF.
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 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;
The order method cust_order accepts another customer_info_t object as an input argument and returns the difference of the two custno attributes. Since it subtracts the custno of the other customer_info_t object from its own object's custno, the method returns a negative number if its own object has a smaller value of custno, a positive number if its own has a larger value of custno, and zero if the two objects have the same value of custno.
This completes the definition of the user-defined types used in the purchase order application.
None of the declarations create tables or reserve data storage space.
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.
The following statement defines an object table to hold row objects of type customer_info_t.
CREATE TABLE customer_tab OF customer_info_t (custno PRIMARY KEY);
The preceding statement creates the object table customer_tab. Each row of customer_tab is a customer_info_t object.
The attributes of customer_info_t objects are:
custno NUMBER custname VARCHAR2(200) address address_t phone_list phone_list_t po_list po_reflist_t
The statement defines a primary key constraint on the custno column. This constraint applies only to this table, not to all customer_info_t objects. Another object table of customer_info_t objects need not satisfy that constraint.
This illustrates an important point: Constraints apply to tables, not to type definitions.
The address column contains address_t objects. These have attributes of built-in types. They are leaf-level scalar attributes of customer_info_t, so Oracle creates columns for them 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 VARRAYs of type phone_list_t. VARRAYs of type phone_list_t contain no more than 200 characters of phone numbers, plus a small amount of overhead. As a result, Oracle stores the VARRAY as a single data unit in the phone_list column. Oracle stores VARRAYs that exceed 4000 bytes in BLOBs.
The next statement creates an object table for stock_info_t objects.
CREATE TABLE stock_tab OF stock_info_t (stockno PRIMARY KEY) ;
The preceding statement creates the stock_tab object table. Each row of the table is a stock_info_t object. Each such object has three numeric attributes. Oracle assigns a column to each. The statement places a primary key constraint on the stockno column.
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
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.
The statement places a primary key constraint on the pono column.
The statement places a scope on the REFs in the custref column. These REFs 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 are not stored in the customer_tab object table.
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.
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 REFs 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 REFs in the stockref column to the object table stock_tab.
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. The keyword COLUMN_VALUE makes it possible to place a scope on the elements of a nested table of REFs.
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.
All elements of the nested table in a column of a given row of purchase_tab have the same value of NESTED_TABLE_ID. Elements of the same column in a different row of purchase_tab have a different value of NESTED_TABLE_ID.
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.
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.
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);
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')
) ;
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 ;
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-5.
SELECT DEREF(p.custref), p.shiptoaddr, p.pono,
p.orderdate, line_item_list
FROM purchase_tab p
WHERE p.pono = 1001 ;
SELECT p.pono, p.total_value() FROM purchase_tab p ;
SELECT po.pono, po.custref.custno,
CURSOR (
SELECT *
FROM TABLE (po.line_item_list) L
WHERE L.stockref.stockno = 1004
)
FROM purchase_tab po ;
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 needs a separate step.
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.
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|