|Oracle8i Application Developer's Guide - Object-Relational Features
Release 2 (8.1.6)
Part Number A76976-01
If you have programmed in C++, Java, Perl, or other such modern languages, then you have probably encountered the idea of object-oriented programming. Oracle provides a number of object-oriented features that let you transfer your design and problem-solving skills from those languages to database application development.
If you are a long-time database programmer, you may have been frustrated by the lack of complex types, the need to "flatten" hierarchical data structures into tables with numerous primary and foreign keys, and the amount of application logic that must be copied and adapted to handle each new situation. Oracle's object-oriented features are intended to help solve each of these problems.
Before reading this book, you should be familiar with database application development. You should be able to use one or more programming languages together with DDL and DML to do all the usual database operations.
If you are just beginning to use Oracle's object-relational features, and you still have questions after reading this chapter, refer to Chapter 7, "Frequently Asked Questions about Programming with Oracle Objects".
Here is a brief overview of the concepts and terminology you need to work with Oracle's object-relational features features.
The object-relational model is an evolutionary way to introduce object-oriented features to the database without giving up the existing relational features that are used in existing applications. As you read further, you will see how object-oriented features are integrated into Oracle 8i without compromising the good features of the past.
Why not create object-oriented applications using a third-generation language (3GL), without the database at all?
First, an RDBMS provides functionality that you can build upon, instead of reinventing.
Second, one of the problems of information management using 3GLs is that they are not persistent; or, if they are persistent, then they sacrifice security to get the necessary performance by locating the application logic and the data logic in the same address space. Neither trade-off is acceptable to users of an RDBMS, for whom both persistence and security are basic requirements.
This leaves the application developer working under the relational model with the problem of simulating complex types by some form of mapping into SQL. Apart from the many person-hours required, this approach involves serious problems of implementation. You must:
This involves heavy traffic between the client and server address spaces, leading to slower performance. If client and server are on different machines, network roundtrips may add considerable overhead.
Object-relational (O-R) technology solves these problems, as you will see throughout this book.
Object types are abstractions of the real-world entities--for example, purchase orders--that application programs deal with. They are analogous to Java and C++ classes.
You can think of an object type as a template, and an object as a structure that matches the template. Object types can represent many different data structures; a few examples are line items, images, and spatial data.
Object types are schema objects, subject to the same kinds of administrative control as other schema objects (see Chapter 2, "Managing Oracle Objects").
You can use object types to map an object model directly to a database schema, instead of flattening the model to relational tables and columns. They let you bring related pieces of data together in a single unit, and to store the behavior of data along with the data itself. Application code can retrieve and manipulate the data as objects.
An object type is a schema object with three kinds of components:
When you create a variable of an object type, the result is an object. The object has attributes and methods based on its type. Because the object is a concrete thing, you can assign values to its attributes and call its methods.
Methods of an object type are functions or procedures that are called by the application to model the behavior of the objects. Methods that are written in PL/SQL or Java are stored in the database, which is preferable for data-intensive procedures and short procedures that are called frequently. Procedures in other languages, such as C, are stored externally, which is preferable for computationally intensive procedures that are called less frequently.
The methods of an object type broadly fall into three categories: Member, Static, and Comparison.
A member method is a function or a procedure that always has an implicit SELF parameter, and thus can work with the attributes of a specific object. You invoke it using the "dot" notation OBJECT_VARIABLE.METHOD( ). Member methods are useful for writing observer or mutator methods, where the operation affects a specific object and you do not need to pass in any parameters.
A static method is a function or a procedure that does not have an implicit SELF parameter. Such methods may be invoked by qualifying the method with the type name, as in TYPE_NAME.METHOD( ). Static methods are useful for procedures that work with global data rather than the object state, or functions that return the same value regardless of the object.
Comparison methods compare instances of objects, to allow sorting and IF/THEN logic.
In the example, PURCHASE_ORDER has a method named GET_VALUE. Each purchase order object has its own GET_VALUE method. For example, if X_OBJ and Y_OBJ are PL/SQL variables that hold purchase order objects and W_NUM and Z_NUM are variables that hold numbers, the following two statements can retrieve values from the objects:
Both objects, being of the same type, have the GET_VALUE method. The method call does not need any parameters, because it operates on its own set of data: the attributes of X_OBJ and Y_OBJ. In this selfish style of method invocation, the method uses the appropriate set of data depending upon the object for which it is called.
Every object type has a system-defined constructor method, that is, a method that makes a new object and sets up the values of its attributes. The name of the constructor method is the name of the object type. Its parameters have the names and types of the object type's attributes. The constructor method is a function. It returns the new object as its value.
For example, the expression
represents a purchase order object with the following attributes:
person ("John Smith", "1-800-555-1212") is an invocation of the constructor function for the object type PERSON. The object that it returns becomes the contact attribute of the purchase order.
See "Null Objects and Attributes" for a discussion of null objects and null attributes.
Oracle has facilities for comparing two data items of a given built-in type and determining whether one is greater than, equal to, or less than the other. To compare two items of a user-defined type, the creator of the type must define an order relationship for the type using map methods or order methods.
Map methods produce a single value of a built-in type that can be used for comparisons and sorting. For example, if you define an object type called RECTANGLE, the map method AREA can multiply its HEIGHT and WIDTH attributes and return the answer. Oracle can then compare two rectangles by comparing their areas.
Order methods are more general. They use their own internal logic to compare two objects of a given object type and return a value that encodes the order relationship: -1 if the first is smaller, 0 if they are equal, and 1 if the first is larger.
For example, an order method can allow you to sort a set of addresses based on their distance from a fixed point, or some other operation more complicated than comparing individual values.
In defining an object type, you can specify either a map method or an order method for it, but not both. If an object type has no comparison method, Oracle cannot determine a greater-than or less-than relationship between two objects of that type. It can, however, attempt to determine whether two objects of the type are equal.
Oracle compares two objects of a type that lacks a comparison method by comparing corresponding attributes:
Because the system can perform scalar value comparisons very efficiently, coupled with the fact that calling a user-defined function is slower than calling a kernel implemented function, sorting objects using the
ORDER method is relatively slow compared to sorting the mapped scalar values returned by the
An object table is a special kind of table in which each row represents an object.
For example, the following statement defines an object table for objects of the PERSON type:
Oracle allows you to view this table in two ways:
For example, you can execute the following instructions:
INSERT INTO person_table VALUES ( "John Smith", "1-800-555-1212" ); SELECT VALUE(p) FROM person_table p WHERE p.name = "John Smith";
The first instruction inserts a PERSON object into PERSON_TABLE as a multi-column table. The second selects from PERSON_TABLE as a single column table.
Objects that occupy complete rows in object tables are called row objects. Objects that occupy table columns in a larger row, or are attributes of other objects, are called column objects.
An object view (see Chapter 4, "Applying an Object Model to Relational Data") is a way to access relational data using object-relational features. It lets you develop object-oriented applications without changing the underlying relational schema.
REF is a logical "pointer" to a row object. It is an Oracle built-in datatype.
REFs and collections of
REFs model associations among objects--particularly many-to-one relationships--thus reducing the need for foreign keys.
REFs provide an easy mechanism for navigating between objects. You can use the dot notation to follow the pointers. Oracle does joins for you when needed, and in some cases can avoid doing joins.
You can use a REF to examine or update the object it refers to. You can also use a REF to obtain a copy of the object it refers to. You can change a REF so that it points to a different object of the same object type, or assign it a null value.
In declaring a column type, collection element, or object type attribute to be a REF, you can constrain it to contain only references to a specified object table. Such a REF is called a scoped REF. Scoped REFs require less storage space and allow more efficient access than unscoped REFs.
It is possible for the object identified by a REF to become unavailable--through either deletion of the object or a change in privileges. Such a REF is called dangling. Oracle SQL provides a predicate (called IS DANGLING) to allow testing REFs for this condition.
Accessing the object referred to by a REF is called dereferencing the REF. Oracle provides the DEREF operator to do this.
Dereferencing a dangling REF returns a null object.
Oracle also provides implicit dereferencing of REFs. For example, consider the following:
If X represents an object of type PERSON, then the SQL expression:
follows the pointer from the person X to another person, X's manager, and retrieves the manager's name. (Following the REF like this is allowed in SQL, but not in PL/SQL.)
You can obtain a REF to a row object by selecting the object from its object table and applying the REF operator. For example, you can obtain a REF to the purchase order with identification number 1000376 as follows:
DECLARE OrderRef REF to purchase_order; SELECT REF(po) INTO OrderRef FROM purchase_order_table po WHERE po.id = 1000376;
The query must return exactly one row.
For more on storage of objects and REFs, see "Using Collections".
For modelling one-to-many relationships, Oracle supports two collection datatypes: varrays and nested tables. For example, a purchase order has an arbitrary number of line items, so you may want to put the line items into a collection.
If you need to loop through the elements in order, store only a fixed number of items, or retrieve and manipulate the entire collection as a value, then use varrays.
If you need to run efficient queries on collections, handle arbitrary numbers of elements, or do mass insert/update/delete operations, then use nested tables. If the collections are very large and you want to retrieve only subsets, you can model the collection as a nested table and retrieve a locator for the result set.
For example, a purchase order object may have a nested table of line items, while a rectangle object may contain a varray with 4 coordinates.
You can make an object of a collection type by calling its constructor method. The name of the constructor method is the name of the type, and its argument is a comma-separated list of the new collection's elements.
Calling the constructor method with an empty list creates an empty collection of that type. An empty collection is different from a null collection.
An array is an ordered set of data elements. All elements of a given array are of the same datatype. Each element has an index, which is a number corresponding to the element's position in the array.
The number of elements in an array is the size of the array. Oracle allows arrays to be of variable size, which is why they are called VARRAYs. You must specify a maximum size when you declare the array type.
For example, the following statement declares an array type:
The VARRAYs of type PRICES have no more than ten elements, each of datatype NUMBER(12,2).
Creating an array type does not allocate space. It defines a datatype, which you can use as
A VARRAY is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB (see "Import/Export/Load of Object Types").
A nested table is an unordered set of data elements, all of the same datatype. It has a single column, and the type of that column is a built-in type or an object type. If the column in a nested table is an object type, the table can also be viewed as a multi-column table, with a column for each attribute of the object type.
For example, in the purchase order example, the following statement declares the table type used for the nested tables of line items:
A table type definition does not allocate space. It defines a type, which you can use as
When a table type appears as the type of a column in a relational table or as an attribute of the underlying object type of an object table, Oracle stores all of the nested table data in a single table, which it associates with the enclosing relational or object table. For example, the following statement defines an object table for the object type PURCHASE_ORDER:
CREATE TABLE purchase_order_table OF purchase_order NESTED TABLE lineitems STORE AS lineitems_table;
The second line specifies LINEITEMS_TABLE as the storage table for the LINEITEMS attributes of all of the PURCHASE_ORDER objects in PURCHASE_ORDER_TABLE.
A convenient way to access the elements of a nested table individually is to use a nested cursor.
See Oracle8i SQL Reference for information about nested cursors, and see"Nested Tables" for more information on using nested tables.
Inheritance is a technique used in object-oriented development to create objects that contain generalized attributes and behavior for groups of related objects. The more general object types are referred to as a super-types. The specialized object types that "inherit" from the super-types are called subtypes.
A common case of inheritance is that of
Employee. The set of people includes employees and non-employees. The more general case,
Person, is the super-type and the special case,
Employee, the sub-type. Another example could involve a
Vehicle as super-type and
Truck as its subtypes.
Here is an example of how you might define a set of object types.
The object types are PERSON, LINEITEM, LINEITEM_TABLE, and PURCHASE_ORDER.
NAME, PHONE, ITEM_NAME, and so on are attributes of their respective object types. The attribute CONTACT is an object, and the attribute LINEITEMS is a nested table.
LINEITEM_TABLE is a table in which each row is an object of type LINEITEM.
CREATE TYPE person AS OBJECT ( name VARCHAR2(30), phone VARCHAR2(20) ); CREATE TYPE lineitem AS OBJECT ( item_name VARCHAR2(30), quantity NUMBER, unit_price NUMBER(12,2) ); CREATE TYPE lineitem_table AS TABLE OF lineitem; CREATE TYPE purchase_order AS OBJECT ( id NUMBER, contact person, lineitems lineitem_table, MEMBER FUNCTION get_value RETURN NUMBER );
This is a simplified example. It does not show how to specify the body of the method GET_VALUE, which you do with the CREATE OR REPLACE TYPE BODY statement.
Defining an object type does not allocate any storage.
You can use LINEITEM, PERSON, or PURCHASE_ORDER in SQL statements in most of the same places you can use types like NUMBER or VARCHAR2.
For example, you might define a relational table to keep track of your contacts:
The CONTACT table is a relational table with an object type defining one of its columns. Objects that occupy columns of relational tables are called column objects (see "Row Objects and Column Objects").