|Oracle8 Application Developer's Guide
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.
||Item identification, cost, and taxability code|
||Customer, order and ship dates, shipping address|
||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
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.
LINE_ITEMS table contains foreign keys
PONO to the
PURCHASE_ORDER table and
STOCKNO to the
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:
In an application based on the tables defined earlier, statements such as the following delete stored data:
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:
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.
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
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
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
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.
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
LINE_ITEM_T objects would be, for the following reasons:
VARRAYs because it involves casting the
VARRAYto a nested table first.
VARRAYrequires 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
Objects of type
PURCHASE_ORDER_T have two methods:
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
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.
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
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.
RET_VALUE method is simple: you use it to return the number of its associated
TOTAL_VALUE method uses a number of O-R means to return the sum of the values of the line items of its associated
TOTAL_VALUEmethod is to return the sum of the values of the line items of its associated
PURCHASE_ORDER_Tobject. The keyword
SELF, which is implicitly created as a parameter to every function, lets you refer to that object.
COUNTgives 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
COUNTcounts the number of elements in the nested table that match the
LINE_ITEM_LISTattribute of the
PURCHASE_ORDER_Tobject, here represented by
DEREFoperator takes a reference value as an argument, and returns a row object. In this case,
STOCKREF) takes the
STOCKREFattribute as an argument, and returns
STOCK_INFO_Tobject. Looking back to our data definition, you will see that
STOCKREFis an attribute of the
LINE_ITEM_Tobject 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 P
URCHASE_ORDER_Tobject represented by
SELF. This may seem rather complicated until you take it up again from a real-world perspective in which a purchase order (P
URCHASE_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_Tis a template for all purchase order objects. How then are to we retrieve the values of actual stock objects? The SQL
SELECTstatement with the explicit
DEREFcall is required, because Oracle does not support implicit dereferencing of
REFs within PL/SQL programs. The PL/SQL variable
STOCKis of type
STOCK_INFO_T. The select statement sets it to the object represented by
STOCKREF). And this object is the actual stock item referred to in the i-th line item
COSTattribute of the
STOCKobject. But to compute the cost of the item we also need to know the quantity of items ordered. In our application, the term
QUANTITYattribute of each
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
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:
This means that the table
CUSTOMER_TAB has columns of
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
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
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
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:
Oracle assigns a column for each attribute, and the CREATE TABLE statement places a primary key constraint on the
The next statement defines an object table for
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
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
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
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,
SHIPDATE, and the attributes of the
ADDRESS_T object in
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.
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:
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
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
The next statement further alters the
PO_LINE_TAB storage table to specify its index storage.
The next statement creates an index on the
PO_LINE_TAB storage table:
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
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:
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 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.
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.