Release 8.0
A58241-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Application Developer's Guide Release 8.0 A58241-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.
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.
PL/SQL provides additional capabilities beyond those illustrated here, especially in the area of accessing and manipulating the elements of collections.
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.
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 basic entities in this example are:
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.
| 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.
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.
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 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) ;
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.
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;
Given the schema objects described above, you would execute statements such as the following to 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 such as the following delete stored data:
DELETE FROM line_items WHERE pono = 1001 ; DELETE FROM purchase_order WHERE pono = 1001 ;
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
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 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:
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.
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:
VARRAYs are ordered. Nested tables are unordered.
VARRAYs 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.
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.
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.
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:
VARRAYs because it involves casting the VARRAY to a nested table first.
VARRAYs.
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.
.
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.
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 is simple: you use it to return the number of its associated PURCHASE_ORDER_T object.
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:
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.
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.
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 PURCHASE_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 (PURCHASE_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.
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 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). And this object is the actual stock item referred to in the i-th line item
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 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.
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:
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 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.
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.
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 VARRAYs 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 VARRAYs 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 VARRAYs that exceed 4000 bytes in BLOBs 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 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 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 first new element introduced here has to do with the way the statement places a scope on the REFs 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 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 might be stored in any other object table.
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.
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.
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.
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-6.
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 requires 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.
