An object type is a user-defined data type that combines data and related methods to model complex entities. In this regard, they are similar to class definitions in an object-oriented language such as C++ or Java. However, unlike Java and C++, 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) ) NOT FINAL;
The equity object type has four attributes and a single member procedure. The NOT FINAL declaration at the end of the type definition allows for subtypes to be defined in the future (more on this later). 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. 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;
Oracle9i introduced several notable features to the object-oriented toolset, including inheritance (release 1) and user-defined constructors (release 2). The following type definition demonstrates both of these features:
CREATE TYPE preferred_stock UNDER equity ( last_dividend_date DATE, last_dividend_amount NUMBER(9,2), CONSTRUCTOR FUNCTION preferred_stock(last_div_dt DATE, last_div_amt NUMBER, eq equity) RETURN SELF AS RESULT ) FINAL;
Because preferred stock is a special type of equity, you can create a subtype that includes all of the attributes and methods of the equity type along with additional attributes to hold dividend information. In this case, we have added two attributes to hold information about the last dividend payment, along with a user-defined constructor for the preferred_stock type, which is defined in the type body:
CREATE TYPE BODY preferred_stock AS CONSTRUCTOR FUNCTION preferred_stock(last_div_dt DATE, last_div_amt NUMBER, eq equity) RETURN SELF AS RESULT IS BEGIN SELF.last_dividend_date := last_div_dt; SELF.last_dividend_amount := last_div_amt; SELF.issuer_id := eq.issuer_id; SELF.ticker := eq.ticker; SELF.outstanding_shares := eq.outstanding_shares; SELF.last_close_price := eq.last_close_price; RETURN; END preferred_stock; END;
For the constructor, we chose to pass in an instance of equity rather than pass in the four equity attributes individually. The next example shows how to create an instance of preferred_stock by creating an instance of equity and passing it into the user-defined constructor:
DECLARE eq equity := NULL; ps preferred_stock := NULL; BEGIN eq := equity(198, 'ACMW', 1000000, 13.97); ps := preferred_stock(SYSDATE, 0.1, eq); END;
Because preferred_stock is a subtype of equity, an instance of preferred_stock is also an instance of equity. The next example uses the IS OF function to demonstrate this:
SELECT equities.eq FROM (SELECT equity(198, 'ACMW', 1000000, 13.97) eq FROM DUAL UNION ALL SELECT preferred_stock(SYSDATE, 0.1, equity(198, 'ACMW', 1000000, 13.97)) eq FROM DUAL ) equities WHERE equities.eq IS OF (equity); EQ(ISSUER_ID, TICKER, OUTSTANDING_SHARES, LAST_CLOSE_PRICE) ------------------------------------------------------------- EQUITY(198, 'ACMW', 1000000, 13.97) PREFERRED_STOCK(198, 'ACMW', 1000000, 13.97, '03-DEC-03', .1)
The equities inline view constructs an instance of equity and an instance of preferred_stock, and the containing query returns those objects that are of type equity; as you can see, both instances, one of equity and one of preferred_stock, are returned. However, an instance of equity is not an instance of preferred_stock, as demonstrated in the next example:
SELECT equities.eq FROM (SELECT equity(198, 'ACMW', 1000000, 13.97) eq FROM DUAL UNION ALL SELECT preferred_stock(SYSDATE, 0.1, equity(198, 'ACMW', 1000000, 13.97)) eq FROM DUAL ) equities WHERE equities.eq IS OF (preferred_stock); EQ(ISSUER_ID, TICKER, OUTSTANDING_SHARES, LAST_CLOSE_PRICE) ------------------------------------------------------------- PREFERRED_STOCK(198, 'ACMW', 1000000, 13.97, '03-DEC-03', .1)
In this case, the equity instance is not returned, since it is not of type preferred_stock. This distinction will be important when we look at collections later in the chapter.
So far, we have created several object types and generated several non-persistent (not stored in a database) instances of those object types. If you want to store instances of your object types in a database, you have two choices: create a column in a table to store instances of your object type, or create a special type of table, called an object table, to store instances of your object type. This section will explore how to generate object-based columns, while the following section will explore object tables.
The following table definition shows how an equity object type may be used as an attribute of the fund_holding table:
CREATE TABLE fund_holding ( fund_id VARCHAR2(10) NOT NULL, security equity NOT NULL, num_shares NUMBER NOT NULL);
While the fund_id and num_shares columns are defined using standard built-in data types, the security column is defined as type equity. When adding records to the table, you must utilize the object type constructor, as illustrated by the following INSERT statement:
INSERT INTO fund_holding (fund_id, security, num_shares) VALUES ('101A', equity(198, 'ACMW', 1000000, 13.97), 20000);
To see the attributes of the equity object, you must provide an alias for the table and reference the alias, the name of the column containing the object type, and the object type's attribute. The next query retrieves the fund_id, which is a column in the fund_holding table, and the ticker, which is an attribute of the equity object within the fund_holding table:
SELECT fh.fund_id fund_id, fh.security.ticker ticker FROM fund_holding fh; FUND_ID TICKER ---------- ------ 101A ACMW
Since preferred_stock is a subtype of equity, you can substitute an instance of preferred_stock:
UPDATE fund_holding fh SET fh.security = preferred_stock(SYSDATE, 0.1, equity(198, 'ACMW', 1000000, 13.97));
However, as shown in the next example, you will not be able to query attributes of the preferred_stock type explicitly, because the table definition specifies type equity:
SELECT fh.fund_id fund_id, fh.security.last_dividend_amount div_amt FROM fund_holding fh; ERROR at line 2: ORA-00904: "FH"."SECURITY"."LAST_DIVIDEND_AMOUNT": invalid identifier
Even though you can substitute an instance of a subtype for an instance of a parent type, you are limited to using the attributes and methods defined for the parent type. In the most recent example, the SELECT statement "sees" a table of equity objects. That does not change just because one of those objects happens to be a specific subtype of equity.
In addition to creating object type columns, you can also build an object table specifically for holding instances of your object type. You create these tables by referencing the object type in the CREATE TABLE statement using the OF keyword:
CREATE TABLE equities OF equity;
You can populate the equities table using the constructor for the equity object type, or you may populate it from existing instances of the equity object type. For example, the next statement populates the equities table using the security column of the fund_holding table, which is defined as an equity type:
INSERT INTO equities SELECT fh.security FROM fund_holding fh;
You can also store any subtype of the equity type in the table:
INSERT INTO equities VALUES (preferred_stock(SYSDATE - 20, 0.05, equity(199, 'XYZ', 2000000, 8.87)));
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 199 XYZ
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 query retrieves the object having a ticker equal to 'ACMW' from the equities table:
SELECT VALUE(e) FROM equities e WHERE e.ticker = 'ACMW'; VALUE(E)(ISSUER_ID, TICKER, OUTSTANDING_SHARES, LAST_CLOSE_PRICE) ----------------------------------------------------------------- PREFERRED_STOCK(198, 'ACMW', 1000000, 13.97, '20-MAR-04', .1)
Since you can treat an object table as either a relational table or as a set of objects, object tables represent the best of both worlds.
Now that you have an object stored in the database, we 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' FOR UPDATE; /* 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.
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 to find and build customer orders. First, we'll 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, we 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 CROSS JOIN 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 INNER JOIN customer c ON co.cust_nbr = c.cust_nbr INNER JOIN employee e ON co.sales_emp_id = e.emp_id WHERE co.order_nbr = v_order_nbr; 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.