Roll Your Own Data Types

Oracle8 extends the SQL data-type system with a TYPE object that comes complete with its own CREATE, ALTER, and DROP statements. Types include both attributes (i.e., data) and methods (i.e., stored subprograms that operate on the data). The data attributes can be standa rd SQL types, such as VARCHAR2 or NUMBER, previously declared object types, or collections of nested tables. Here's a simple example:

CREATE OR REPLACE TYPE Address (

   Street     VARCHAR2(1
00),

   City       VARCHAR2(100),

   State      CHAR(2),

   Country    VARCHAR(50),

   PostalCode VARCHAR2(25),

   Location   REF GPSLocation,

   MEMBER FUNCTION Label RETURN VARCHAR2,

   MEMBER FUNCTION Location RETURN GPSLocation

   MEMBER FUNCTION Location RETURN Building);

The separate CREATE TYPE BODY statement has the PL/SQL implementations of the member methods. The figure "Referencing Objects" shows this type using the object-oriented Universal Modeling Language (UML) design notation. The best way to use these types is to create a table as the type

CREATE TABLE Addresses OF Address;

This object table lets you create objects with the INSERT statement using the built-in constructor method for the type, which you can also use when creating objects embedded in tables or other objects:

INSERT INTO Addresses
VALUES (Address('1600 Pennsylvania Avenue',
                     'Washington', 'DC', 'USA', '12345'));

The trick to embedding objects, though, is that you can't use pointers to such objects because they don't have object IDs; only stand-alone objects have them. A much better approach than embedding objects is to create references to objects (REF types, pointers to objects of a type) that are stored separately. That lets you refer to and dereference pointers to such objects in your programs, which is generally what you want to do in an OO program. You should realize, of course, that most of what you are likely to want to do with this kind of object through SQL is best done through a PL/SQL or C++ program that enables you to call the methods and get complex results into variables that you can do something with, not with SQL*Plus or other simple query tools.

---

Referencing Objects

illustration_link (8 Kbytes)

You can design complex object types that include methods (i.e., behavior) and references to other object types.