10.2 Objects and Collections

10.2 Objects and Collections

Beginning with Version 8.0, Oracle added object-oriented features to what was a purely relational database server. Object types and collections were introduced in Oracle8, and both have been sufficiently refined in Oracle8i and Oracle9i so that they may now be considered fully-functional.^[7] Oracle now considers its database engine to be object-relational, in that a database may mix relational constructs such as tables and constraints with object-oriented constructs such as object types, collections, and references.

^[7] In release 8.0, for example, object types didn't support inheritance, and collections could not be nested (i.e., an array of arrays), resulting in a fairly cool reception to Oracle's early attempts at object-orientation.

10.2.1 Object Types

An object type is a user-defined datatype that combines data and related methods in order to model complex entities. In this regard, they are similar to class definitions in an object-oriented language such as C++ or Java. Unlike Java and C++, however, Oracle object types have a built-in persistence mechanism, since a table can be defined to store an object type in the database. Thus, Oracle object types can be directly manipulated via SQL.

The best way to define the syntax and features of an object type is with an example. The following DDL statement creates an object type used to model an equity security such as a common stock:

CREATE TYPE equity AS OBJECT (
  issuer_id NUMBER,
  ticker VARCHAR2(6),
  outstanding_shares NUMBER,
  last_close_price NUMBER(9,2),
MEMBER PROCEDURE 
  apply_split(split_ratio in VARCHAR2));

The equity object type has four data members and a single member procedure. The body of the apply_split procedure is defined within a CREATE TYPE BODY statement. The following example illustrates how the apply_split member procedure might be defined:

CREATE TYPE BODY equity AS
  MEMBER PROCEDURE apply_split(split_ratio in VARCHAR2) IS
    from_val NUMBER;
    to_val NUMBER;
  BEGIN
    /* parse the split ratio into its components */
    to_val := SUBSTR(split_ratio, 1, INSTR(split_ratio, ':') -- 1);
    from_val := SUBSTR(split_ratio, INSTR(split_ratio, ':') + 1);

    /* apply the split ratio to the outstanding shares */
    SELF.outstanding_shares := 
     (SELF.outstanding_shares * to_val) / from_val;

    /* apply the split ratio to the last closing price */
    SELF.last_close_price := 
     (SELF.last_close_price * from_val) / to_val;
  END apply_split;
END;

In this example, the SELF keyword is used to identify the current instance of the equity object type. Although it is not required, we recommend using SELF in your code so that it is clear that you are referencing or modifying the current instance's data. We will explore how to call member functions and procedures a bit later in the chapter.

Instances of type equity are created using the default constructor, which has the same name as the object type and expects one parameter per attribute of the object type. As of Oracle9i, there is no way to generate your own constructors for your object types. The following PL/SQL block demonstrates how an instance of the equity object type can be created using the default constructor:

DECLARE
  eq equity := NULL;
BEGIN
  eq := equity(198, 'ACMW', 1000000, 13.97);
END;

Object type constructors may also be called from within DML statements. The next example queries the issuer table to find the issuer with the name 'ACME Wholesalers', and then uses the retrieved issuer_id field to construct an instance of the equity type:

DECLARE
  eq equity := NULL;
BEGIN
  SELECT equity(i.issuer_id, 'ACMW', 1000000, 13.97)
  INTO eq
  FROM issuer i
  WHERE i.name = 'ACME Wholesalers';
END;

The next three sections briefly describe some of the ways you can incorporate object types into your database and/or your database logic.

10.2.1.1 Object attributes

An object type may be used along with Oracle's built-in datatypes as an attribute of a table. The following table definition includes the equity object type as an attribute of the common_stock table:

CREATE TABLE common_stock (
  security_id NUMBER NOT NULL,
  security equity NOT NULL,
  issue_date DATE NOT NULL,
  currency_cd VARCHAR2(5) NOT NULL);

When adding records to the table, you must utilize the object type constructor, as illustrated by the following INSERT statement:

INSERT INTO common_stock (security_id, security, issue_date, currency_cd)
VALUES (1078, equity(198, 'ACMW', 1000000, 13.97), SYSDATE, 'USD');

In order to see the attributes of the equity object, you must provide an alias for the table and reference the alias, the name of the attribute containing the object type, and the object type's attribute. The next query retrieves the security_id, which is an attribute of the common_stock table, and the ticker, which is an attribute of the equity object within the common_stock table:

SELECT c.security_id security_id, 
  c.security.ticker ticker
FROM common_stock c;

SECURITY_ID TICKER
----------- ------
       1078 ACMW

10.2.1.2 Object tables

In addition to embedding object types in tables alongside other attributes, you can also build a table specifically for holding instances of your object type. Known as an object table, these tables are created by referencing the object type in the CREATE TABLE statement using the OF keyword:

CREATE TABLE equities OF equity;

The equities table can be populated using the constructor for the equity object type, or it may be populated from existing instances of the equity object type. For example, the next statement populates the equities table using the security column of the common_stock table:

INSERT INTO equities
SELECT c.security FROM common_stock c;

When querying the equities table, you can reference the object type's attributes directly, just as you would an ordinary table:

SELECT issuer_id, ticker 
FROM equities;

ISSUER_ID TICKER
--------- ------
      198 ACMW

If you want to retrieve the data in the equities table as an instance of an equity object rather than as a set of attributes, you can use the VALUE function to return an object. The following PL/SQL block retrieves the object having a ticker equal to 'ACMW' from the equities table:

DECLARE
  eq equity := NULL;
BEGIN
  SELECT VALUE(e) 
  INTO eq
  FROM equities e
  WHERE ticker = 'ACMW';
END;

Thus, object tables represent the best of both worlds, in that you can treat them as either a relational table or as a set of objects.

Note the use of a table alias with the VALUE function. Use of VALUE requires the use of tables aliases.

Now that you have an object stored in the database, you can explore how to call the apply_split member procedure defined earlier. Before you call the procedure, you need to find the target object in the table and then tell the object to run its apply_split procedure. The following PL/SQL block expands on the previous example, which finds the object in the equities table with a ticker of 'ACMW', by finding an equity object, invoking its apply_split method, and saving it back to the table again:

DECLARE
  eq equity := NULL;
BEGIN
  SELECT VALUE(e) 
  INTO eq
  FROM equities e
  WHERE ticker = 'ACMW';

  /* apply a 2:1 stock split */
  eq.apply_split('2:1');

  /* store modified object */
  UPDATE equities e
  SET e = eq
  WHERE ticker = 'ACMW';
END;

It is important to realize that the apply_split procedure is not operating directly on the data in the equities table; rather, it is operating on a copy of the object held in memory. After the apply_split procedure has executed against the copy, the UPDATE statement overwrites the object in the equities table with the object referenced by the local variable eq, thus saving the modified version of the object.

10.2.1.3 Object parameters

Regardless of whether you decide to store object types persistently in the database, you can use them as vehicles for passing data within or between applications. Object types may be used as input parameters and return types for PL/SQL stored procedures and functions. Additionally, SELECT statements can instantiate and return object types even if none of the tables in the FROM clause contain object types. Therefore, object types may be used to graft an object-oriented veneer on top of a purely relational database design.

To illustrate how this might work, let's build an API for our example database that both accepts and returns object types in order to find and build customer orders. First, identify the necessary object types:

CREATE TYPE customer_obj AS OBJECT
 (cust_nbr NUMBER,
  name VARCHAR2(30));

CREATE TYPE employee_obj AS OBJECT
 (emp_id NUMBER,
  name VARCHAR2(50));

CREATE TYPE order_obj AS OBJECT
 (order_nbr NUMBER,
  customer customer_obj,
  salesperson employee_obj,
  order_dt DATE,
  price NUMBER,
  status VARCHAR2(20));

CREATE TYPE line_item_obj AS OBJECT (
  part_nbr VARCHAR2(20),
  quantity NUMBER(8,2));

Using these object definitions, you can now define a PL/SQL package containing procedures and functions that support the lifecycle of a customer order:

CREATE PACKAGE order_lifecycle AS
  FUNCTION create_order(v_cust_nbr IN NUMBER, v_emp_id IN NUMBER) 
    RETURN order_obj;
  PROCEDURE cancel_order(v_order_nbr IN NUMBER);
  FUNCTION get_order(v_order_nbr IN NUMBER) RETURN order_obj;
  PROCEDURE add_line_item(v_order_nbr IN NUMBER, 
    v_line_item IN line_item_obj);
END order_lifecycle;

CREATE PACKAGE BODY order_lifecycle AS
  FUNCTION create_order(v_cust_nbr IN NUMBER, v_emp_id IN NUMBER) 
    RETURN order_obj IS
    ord_nbr NUMBER;
  BEGIN
    SELECT seq_order_nbr.NEXTVAL INTO ord_nbr FROM DUAL;

    INSERT INTO cust_order (order_nbr, cust_nbr, sales_emp_id,
      order_dt, expected_ship_dt, status)
    SELECT ord_nbr, c.cust_nbr, e.emp_id,
      SYSDATE, SYSDATE + 7, 'NEW'
    FROM customer c, employee e
    WHERE c.cust_nbr = v_cust_nbr
      AND e.emp_id = v_emp_id;

     RETURN order_lifecycle.get_order(ord_nbr);
  END create_order;

  PROCEDURE cancel_order(v_order_nbr IN NUMBER) IS
  BEGIN
    UPDATE cust_order SET cancelled_dt = SYSDATE, 
      expected_ship_dt = NULL, status = 'CANCELED'
    WHERE order_nbr = v_order_nbr;
  END cancel_order;

  FUNCTION get_order(v_order_nbr IN NUMBER) RETURN order_obj IS
    ord order_obj := NULL;
  BEGIN
    SELECT order_obj(co.order_nbr, 
      customer_obj(c.cust_nbr, c.name),
      employee_obj(e.emp_id, e.fname || ' ' || e.lname),
      co.order_dt, co.sale_price, co.status)
    INTO ord
    FROM cust_order co, customer c, employee e
    WHERE co.order_nbr = v_order_nbr
      AND co.cust_nbr = c.cust_nbr
      AND co.sales_emp_id = e.emp_id;

    RETURN ord;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN ord;
  END get_order;

  PROCEDURE add_line_item(v_order_nbr IN NUMBER, 
    V_line_item IN line_item_obj) IS
  BEGIN
    INSERT INTO line_item (order_nbr, part_nbr, qty)
    VALUES (v_order_nbr, v_line_item.part_nbr, 
      v_line_item.quantity);
  END add_line_item;
END order_lifecycle;

From the API user's standpoint, objects are being stored and retrieved from the database, even though the database behind the API is purely relational. If you are squeamish about using object types in your database schema, this approach can be an attractive alternative to asking your Java coders to directly manipulate relational data.

10.2.2 Collection Types

During a traditional relational design process, one-to-many relationships, such as a department having many employees or an order consisting of many line items, are resolved as separate tables where the child table holds a foreign key to the parent table. In the example schema, each row in the line_item table knows which order it belongs to via a foreign key, but a row in the cust_order table does not directly know anything about line items. Beginning with Oracle8, such relationships can be internalized within the parent table using a collection. The two collection types available in Oracle8 and above are variable arrays, which are used for ordered, bounded sets of data, and nested tables, which are used for unordered, unbounded data sets.

10.2.2.1 Variable arrays

Variable arrays, also called varrays, are arrays stored within a table. Elements of a varray must be of the same datatype, are bounded by a maximum size, and are accessed positionally. Varrays may contain either a standard Oracle datatype, such as DATE or VARCHAR2, or a user-defined object type. The following example illustrates the creation of a varray and its use as a column of a table:

CREATE TYPE resupply_dates AS VARRAY(100) OF DATE;

CREATE TABLE part_c (
  part_nbr VARCHAR2(20) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  supplier_id NUMBER(6),
  unit_cost NUMBER(8,2),
  inventory_qty NUMBER(6),
  restocks resupply_dates);

Along with descriptive information about the part, each row in the part_c table can hold up to 100 dates corresponding to when a shipment was received from the supplier.

10.2.2.2 Nested tables

Like varrays, nested table elements must be of the same datatype. Unlike varrays, however, nested tables do not have a maximum size and are not accessed positionally. Rows in the nested table can only have one column, which may be defined as a standard datatype or an object type. If an object type is used, the effect is the same as if the nested table were allowed to have multiple columns, since an object type may contain multiple attributes. The following example defines a nested table type containing an object type:

CREATE TYPE line_item_obj AS OBJECT (
  part_nbr VARCHAR2(20),
  quantity NUMBER(8,2));

CREATE TYPE line_item_tbl AS TABLE OF line_item_obj;

Now that you have created a nested table type for line_item objects, you can choose to embed it into our cust_order table, as in the following:

CREATE TABLE cust_order_c (
  order_nbr NUMBER(8) NOT NULL,
  cust_nbr NUMBER(6) NOT NULL,
  sales_emp_id NUMBER(6) NOT NULL,
  order_dt DATE NOT NULL,
  sale_price NUMBER(9,2),
  order_items line_item_tbl)
NESTED TABLE order_items STORE AS order_items_table;

Using a nested table, you have absorbed an order's line items into the cust_order table, eliminating the need for the line_item table. Later in the chapter, you'll see Oracle provides a way to detach the order_items collection when it is advantageous.

10.2.3 Collection Creation

While the table definitions in the previous section look fairly straightforward, it isn't immediately obvious how you might go about populating the resulting tables. Whenever you want to create an instance of a collection, you need to use its constructor, which is a system-generated function with the same name as the collection. The constructor accepts one or more elements; for varrays, the number of elements cannot exceed the maximum size of the varray. For example, adding a row to the part_c table, which contains a varray column, can be done as follows:

INSERT INTO part_c (part_nbr, name, supplier_id, unit_cost,
  inventory_qty, restocks)
VALUES ('GX5-2786-A2', 'Spacely Sprocket', 157, 75, 22,
  resupply_dates(TO_DATE('03-SEP-1999','DD-MON-YYYY'),
    TO_DATE('22-APR-2000','DD-MON-YYYY'),
    TO_DATE('21-MAR-2001','DD-MON-YYYY')));

In this example, the resupply_dates constructor is called with three parameters, one for each time a shipment of parts was received. If you are using a collection-savvy query tool such as Oracle's SQL*Plus, you can query the collection directly, and the tool will format the results:

SELECT part_nbr, restocks 
FROM part_c 
WHERE name = 'Spacely Sprocket';

PART_NBR        RESTOCKS
--------------- ----------------------------------------------------
GX5-2786-A2     RESUPPLY_DATES('03-SEP-99', '22-APR-00', '21-MAR-01')

You deal with nested tables in a manner similar to varrays. The next example demonstrates how you would insert a new row into the cust_order_c table, which contains a nested table column:

INSERT INTO cust_order_c (order_nbr, cust_nbr, sales_emp_id,
  order_dt, sale_price, order_items)
VALUES (1000, 9568, 275, 
  TO_DATE('21-MAR-2001','DD-MON-YYYY'), 15753, 
  line_item_tbl(
    line_item_obj('A675-015', 25),
    line_item_obj('GX5-2786-A2', 1),
    line_item_obj('X378-9JT-2', 3)));

If you look carefully, you will notice that there are actually two different constructors called: one to create the nested table line_item_tbl, and the other to create each of three instances of the line_item_obj object type. Remember, the nested table is a table of line_item_obj objects. The end result is a single row in cust_order_c containing a collection of three line items.

10.2.4 Collection Unnesting

Even if your developer community is comfortable manipulating collections within your database, it is often unrealistic to expect the various tools and applications accessing your data (data load and extraction utilities, reporting and ad-hoc query tools, etc.) to correctly handle them. Using a technique called collection unnesting, you can present the contents of the collection as if it were rows of an ordinary table. Using the TABLE expression, write a query which unnests the order_items nested table from the cust_order_c table, as in:

SELECT co.order_nbr, co.cust_nbr, co.order_dt, li.part_nbr, li.quantity
FROM cust_order_c co, 
  TABLE(co.order_items) li;

ORDER_NBR   CUST_NBR ORDER_DT  PART_NBR               QUANTITY
---------- ---------- --------- -------------------- ----------
      1000       9568 21-MAR-01 A675-015                     25
      1000       9568 21-MAR-01 GX5-2786-A2                   1
      1000       9568 21-MAR-01 X378-9JT-2                    3

Note that the two data sets do not need to be explicitly joined, since the collection members are already associated with a row in the cust_order_c table.

In order to make this unnested data set available to your users, you can wrap the previous query in a view:

CREATE VIEW cust_order_line_items AS
SELECT co.order_nbr, co.cust_nbr, co.order_dt, li.part_nbr, li.quantity
FROM cust_order_c co, 
  TABLE(co.order_items) li;

Your users can now interact with the nested table via the view using standard SQL, as in the following:

SELECT *
FROM cust_order_line_items
WHERE part_nbr like 'X%';

ORDER_NBR   CUST_NBR ORDER_DT  PART_NBR               QUANTITY
---------- ---------- --------- -------------------- ----------
      1000       9568 21-MAR-01 X378-9JT-2                    3

10.2.5 Querying Collections

Now that you know how to get data into a collection, you need a way to get it out. Oracle provides a special TABLE expression just for this purpose.^[8]

^[8] Prior to release 8i, the TABLE expression was called THE. Only the TABLE expression is used here.

The TABLE expression can be used in the FROM, WHERE, and HAVING clauses of a query to allow a nested table or varray column to be referenced as if it were a separate table. The following query extracts the resupply dates (from the restocks column) that were added previously to the part_c table:

SELECT * 
FROM TABLE(SELECT restocks 
  FROM part_c
  WHERE part_nbr = 'GX5-2786-A2');

COLUMN_VALU
-----------
03-SEP-1999
22-APR-2000
21-MAR-2001

To better illustrate the function of the TABLE expression, the next query retrieves the restocks varray directly from the part_c table:

SELECT restocks 
FROM part_c
WHERE part_nbr = 'GX5-2786-A2'

RESTOCKS
------------------------------------------------------
RESUPPLY_DATES('03-SEP-99', '22-APR-00', '21-MAR-01')

As you can see, the result set consists of a single row containing an array of dates, whereas the previous query unnests the varray so that each element is represented as a row with a single column.

Since the varray contains a built-in datatype rather than an object type, it is necessary to give it a name so that it may be explicitly referenced in SQL statements. Oracle assigns the varray's contents a default alias of 'column_value' for this purpose. The next example makes use of the column_value alias.

Let's say that you wanted to find all parts resupplied on a particular date. Using the TABLE expression, you can perform a correlated subquery against the restocks varray to see if the desired date is found in the set:

SELECT p1.part_nbr, p1.name
FROM part_c p1
WHERE TO_DATE('03-SEP-1999','DD-MON-YYYY') IN
 (SELECT column_value FROM TABLE(SELECT restocks FROM part_c p2 
  WHERE p2.part_nbr = p1.part_nbr));

PART_NBR             NAME
-------------------- -------------------------------
GX5-2786-A2          Spacely Sprocket

10.2.6 Manipulating Collections

If you want to modify a collection's contents, you have two choices: replace the entire collection, or modify individual elements of the collection. If the collection is a varray, you will have no choice but to replace the entire collection. This can be accomplished by retrieving the contents of the varray, modifying the data, and then updating the table with the new varray. The following statement changes the restock dates for part number 'GX5-2786-A2'. Note that the varray is entirely recreated:

UPDATE part_c
SET restocks = resupply_dates(TO_DATE('03-SEP-1999','DD-MON-YYYY'),
    TO_DATE('25-APR-2000','DD-MON-YYYY'),
    TO_DATE('21-MAR-2001','DD-MON-YYYY'))
WHERE part_nbr = 'GX5-2786-A2';

If you are using nested tables, you can perform DML against individual elements of a collection. For example, the following statement adds an additional line item to your nested cust_order_c table for order number 1000:

INSERT INTO TABLE(SELECT order_items FROM cust_order_c
  WHERE order_nbr = 1000)
VALUES (line_item_obj('T25-ASM', 1));

To update data in the nested table, use the TABLE expression to create a data set consisting of part numbers from order number 1000, and then modify the element with a specified part number:

UPDATE TABLE(SELECT order_items FROM cust_order_c
  WHERE order_nbr = 1000) oi
SET oi.quantity = 2
WHERE oi.part_nbr = 'T25-ASM';

Similarly, you can use the same data set to remove elements from the collection:

DELETE FROM TABLE(SELECT order_items FROM cust_order_c
  WHERE order_nbr = 1000) oi
WHERE oi.part_nbr = 'T25-ASM';