0143-0145
Page 143
Additional INSTEAD OF triggers would be provided to handle INSERTs and DELETEs. The advantage of using Object views and INSTEAD OF triggers is their flexibility. Depending on their design and the design of the objects they expose, they can be used to present a more "flat" interface to underlying object tables or an object interface to underlying relational structures. In either case, Object views and INSTEAD OF triggers provide a layer of abstraction between applications and the physical structure of the database.
| CAUTION |
A potential disadvantage of using INSTEAD OF triggers (as opposed to using object methods ) for transaction support is that they do not provide the same degree of encapsulation. It requires greater knowledge of Oracle8 object-relational SQL syntax to apply DML transactions to object views, and the triggers are maintained independently. An object that provides insert(), update(), and delete() methods can encapsulate all DML transactions in the type body, which provides a more object-oriented interface and can simplify maintenance. |
Nested Tables
Nested tables are an Oracle8 extension of PL/SQL tables supported in previous versions. A nested table can be created only from a named type. Earlier sections in this chapter presented nested objects and VARRAYs in the context of built-in and object types. This section focuses on the built-in TABLE type and issues related to nested tables. To illustrate the use of nested tables, the following named types are used in this section:
CREATE TYPE invoice_detail ( item_number NUMBER(10), item_desc VARCHAR2(40), quantity NUMBER(10), price NUMBER(10, 2); CREATE TYPE invoice_details AS TABLE OF invoice_detail;
Note that a TABLE type can be defined based on an object type, a VARRAY, or standard types. Based on these type declarations, an invoice_details table can be nested within another table, as shown here:
CREATE TABLE invoice ( invoice_num NUMBER(10) PRIMARY KEY, invoice_date DATE, customer_id NUMBER(10), total_amt NUMBER(10, 2), status_code NUMBER(1), details invoice_details);
Page 144
| NOTE |
The data for nested tables is not stored inline with the outer table. Instead, it is stored in a system-generated table that inherits the storage parameters of the outer table. This is an important consideration in designing storage parameters for tables containing nested tables. |
This invoice table definition is almost identical to the invoice table containing a nested VARRAY defined previously. These tables differ in that a nested TABLE can contain other aggregate types. Another advantage of using nested table types is that they contain a number of built-in methods for operating on members of nested tables. For example, COUNT identifies the number of items in the nested table, EXISTS(i) tests for the existence of the i th element, FIRST and LAST can be used to access the first and last elements, and so on.
| NOTE |
Currently, the EXISTS, FIRST, and LAST functions are only available within PL/SQL code blocks. Host language interfaces are not supported. |
Oracle8 defines a new operator, THE, for direct access to nested tables for SQL and DML operations. For example, the following INSERT adds a detail item to invoice 123:
INSERT INTO THE(SELECT details FROM invoice WHERE invoice_num = 123) VALUES (999, `ACME WIDGETS', 10, 49.99);
The following SELECT statement retrieves the same row from the nested table:
SELECT item_number, item_desc, quantity, price FROM THE(SELECT details FROM invoice WHERE invoice_num = 123) AS p WHERE p.item_number = 999;
An invoice_details TABLE type is returned by the following SELECT statement:
SELECT details FROM invoice WHERE invoice_num = 123;
Unlike other nested types, indexes can be created on nested tables. For example, you can create an index on item_number with this code:
CREATE INDEX ix_inv_item_num ON invoice.details(item_number);
A storage specification can be provided for indexes on nested tables.
Nested tables are ideal for inlining one-to-many relationships. Although the nested table data is not physically stored with other column data, the logical representation is clearer. Nested tables also can eliminate redundancy, particularly when multiple columns would be needed to
Page 145
maintain the relationship in standard relational tables. This lack of redundancy can improve performance when the internal Oracle set identifier is smaller than the key that would be required to join the two relational tables.
The primary disadvantage of using nested tables is the lack of flexibility in defining the storage tables for nested table data. Unless the size and transaction volume of the outer table are nearly identical to the size and transaction volume for the inner table, a compromise must be made in the storage specification. It is likely that this limitation will be resolved in minor versions of Oracle8, however.
New PL/SQL Extensions and Packages
Aside from the PL/SQL extensions required to support the new object-relational features, a number of features and packages are introduced in Oracle8. These features include a new and simplified interface for accessing external procedures, new packages for accessing the new advanced queuing capabilities of Oracle8, and a package for manipulating LOB data.
Better Interfaces for Accessing External Procedures
In previous versions of Oracle, external procedures could be accessed only by using the DBMS_PIPE package. Oracle8 greatly simplifies this process by providing a means of declaring an external procedure in any PL/SQL block and eliminating the dependency on DBMS_PIPE.
Currently, external procedures can be written only in C (or a language that supports the C calling convention). A second requirement is that external procedures must be implemented in a shared library specific to the operating system (a Windows DLL, for example). Before you can call an external procedure, you must create the library and register the external procedure with Oracle. The following statement defines an external library to Oracle:
CREATE LIBRARY calc AS `/mylib/calc.so' ;
You can register and call external procedures from anonymous PL/SQL blocks, stand-alone functions, object member functions, and so on. An external procedure can be called only from the program unit that registered it, however. The simple function shown in Listing 7.6 registers and wraps an external function.
Listing 7.6. Registering and wrapping an external function.
CREATE FUNCTION calc_score ( val1 IN BINARY_INTEGER, val2 IN BINARY_INTEGER, val3 IN BINARY_INTEGER) RETURN REAL AS EXTERNAL LIBRARY calc NAME "calc_score" LANGUAGE C;
EAN: 2147483647
Pages: 391