Mastering Oracle SQL

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.^[21] 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.

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 ...