Object-Relational Features of Oracle

Object-Relational Features of Oracle

• Defining Types
• Dropping Types
• Constructing Objects
• Methods
• Queries Involving Types
• Declaring Types For Relations
• References
• Nested Tables
• Nested Tables of References
• Converting Relations to Object-Relations

---

Defining Types

Oracle allows us to define types similar to the types of SQL. The syntax is

 CREATE TYPE t AS OBJECT ( list of attributes and methods ); / 

• Note the slash at the end, needed to get Oracle to process the type definition.

For example here is a definition of a point type consisting of two numbers:

 CREATE TYPE PointType AS OBJECT ( x NUMBER, y NUMBER ); / 

An object type can be used like any other type in further declarations of object-types or table-types. For instance, we might define a line type by:

 CREATE TYPE LineType AS OBJECT ( end1 PointType, end2 PointType ); / 

Then, we could create a relation that is a set of lines with ``line ID's'' as:

 CREATE TABLE Lines ( lineID INT, line LineType ); 

---

Dropping Types

To get rid of a type such as LineType, we say:

 DROP TYPE Linetype; 

However, before dropping a type, we must first drop all tables and other types that use this type. Thus, the above would fail because table Lines still exists and uses LineType.

---

Constructing Object Values

Like C++, Oracle provides built-in constructors for values of a declared type, and these constructors bear the name of the type. Thus, a value of type PointType is formed by the word PointType and a parenthesized list of appropriate values. For example, here is how we would insert into Lines a line with ID 27 that ran from the origin to the point (3,4):

 INSERT INTO Lines VALUES(27, LineType( PointType(0.0, 0.0), PointType(3.0, 4.0) ) ); 

That is, we construct two values of type PointType, these values are used to construct a value of type LineType, and that value is used with the integer 27 to construct a tuple for Lines.

---

Declaring and Defining Methods

A type declaration can also include methods that are defined on values of that type. The method is declared by MEMBER FUNCTION or MEMBER PROCEDURE in the CREATE TYPE statement, and the code for the function itself (the definition of the method) is in a separate CREATE TYPE BODY statement.

Methods have available a special tuple variable SELF, which refers to the ``current'' tuple. If SELF is used in the definition of the method, then the context must be such that a particular tuple is referred to. There are some examples of applying methods correctly in The Section on Queries and The Section on Row Types.

For example, we might want to add a length function to LineType. This function will apply to the ``current'' line object, but when it produces the length, it also multiplies by a ``scale factor.'' We revise the declaration of LineType to be:

 CREATE TYPE LineType AS OBJECT ( end1 PointType, end2 PointType, MEMBER FUNCTION length(scale IN NUMBER) RETURN NUMBER, PRAGMA RESTRICT_REFERENCES(length, WNDS) ); / 

• Like ODL methods, you need to specify the mode of each argument --- either IN, OUT, or INOUT.

• It is legal, and quite common, for a method to take zero arguments. If so, omit the parentheses after the function name.

• Note the ``pragma'' that says the length method will not modify the database (WNDS = write no database state). This clause is necessary if we are to use length in queries.

All methods for a type are then defined in a single CREATE BODY statement, for example:

 CREATE TYPE BODY LineType AS MEMBER FUNCTION length(scale NUMBER) RETURN NUMBER IS BEGIN RETURN scale * SQRT((SELF.end1.x-SELF.end2.x)*(SELF.end1.x-SELF.end2.x) + (SELF.end1.y-SELF.end2.y)*(SELF.end1.y-SELF.end2.y) ); END; END; / 

• Notice that the mode of the argument is not given here.

---

Queries to Relations That Involve User-Defined Types

Values of components of an object are accessed with the dot notation. We actually saw an example of this notation above, as we found the x-component of point end1 by referring to end1.x, and so on. In general, if N refers to some object O of type T, and one of the components (attribute or method) of type T is A, then N.A refers to this component of object O.

For example, the following query finds the lengths of all the lines in relation Lines, using scale factor 2 (i.e., it actually produces twice these lengths).

 SELECT lineID, ll.line.length(2.0) FROM Lines ll; 

• Note that in order to access fields of an object, we have to start with an alias of a relation name. While lineID, being a top-level attribute of relation LInes, can be referred to normally, in order to get into the attribute line, we need to give relation Lines an alias (we chose ll) and use it to start all paths to the desired subobjects.

• Dropping the ``ll.'' or replacing it by ``Lines.'' doesn't work.

• Notice also the use of a method in a query. Since line is an attribute of type LineType, one can apply to it the methods of that type, using the dot notation shown.

Here are some other queries about the relation lines.

 SELECT ll.line.end1.x, ll.line.end1.y FROM Lines ll; 

prints the x and y coordinates of the first end of each line.

 SELECT ll.line.end2 FROM Lines ll; 

prints the second end of each line, but as a value of type PointType, not as a pair of numbers. For instance, one line of output would be PointType(3,4). Notice that type constructors are used for output as well as for input.

---

Types Can Also Be Relation Schemas

The uses of types so far has been as ``column types,'' that is, types of attributes. In a CREATE TABLE statement we can replace the parenthesized list of schema elements by the keyword OF and the name of a type. This type is then said to be used as a ``row type.'' For example, to create a relation each of whose tuples is a pair of points, we could say:

 CREATE TABLE Lines1 OF LineType; 

It is as if we had defined Lines1 by:

 CREATE TABLE Lines1 ( end1 PointType, end2 PointType ); 

but the method length is also available whenever we refer to a tuple of lines1. For instance, we could compute the average length of a line by:

 SELECT AVG(ll.length(1.0)) FROM Lines1 ll; 

---

References as a Type

For every type t, REF t is the type of references (object ID's if you will) to values of type t. This type can be used in places where a type is called for. For instance, we could create a relation Lines2 whose tuples were pairs of references to points:

 CREATE TABLE Lines2 ( end1 REF PointType, end2 REF PointType ); 

We can use REF to create references from actual values. For example, suppose we have a relation Points whose tuples are objects of type PointType. That is, Points is declared by:

 CREATE TABLE Points OF PointType; 

We could make Lines2 be the set of all lines between pairs of these points that go from left to right (i.e., the x-value of the first is less than the x-value of the second) by:

 INSERT INTO Lines2 SELECT REF(pp), REF(qq) FROM Points pp, Points qq WHERE pp.x < qq.x; 

There are several important prohibitions, where you might imagine you could arrange for a reference to an object, but you cannot.

• The points referred to must be tuples of a relation of type PointType, such as Points above. They cannot be objects appearing in some column of another relation.

• It is not permissible to invent an object outside of any relation and try to make a reference to it. For instance, we could not insert into Lines2 a tuple with contrived references such as VALUES(REF(PointType(1,2)), REF(PointType(3,4))), even though the types of things are right. The problem is that the points such as PointType(1,2) don't ``live'' in any relation.

To follow a reference, we use the dot notation, as if the attribute of reference type were really the same as the value referred to. For instance, this query gets the x-coordinates of the ends of all the lines in Lines2.

 SELECT ll.end1.x, ll.end2.x FROM Lines2 ll; 

---

Nested Tables

A more powerful use of object types in Oracle is the fact that the type of a column can be a table-type. That is, the value of an attribute in one tuple can be an entire relation, as suggested by the picture below, where a relation with schema (a,b) has b-values that are relations with schema (x,y,z).

a	b
-	x y z - - - - - - - - -
-	x y z - - -
-	x y z - - - - - -

In order to have a relation as a type of some attribute, we first have to define a type using the AS TABLE OF clause. For instance:

 CREATE TYPE PolygonType AS TABLE OF PointType; / 

says that the type PolygonType is a relation whose tuples are of type PointType; i.e., they have two components, x and y, which are real numbers.

Now, we can declare a relation one of whose columns has values that represent polygons; i.e., they are sets of points. A possible declaration, in which polygons are represented by a name and a set of points is:

 CREATE TABLE Polygons ( name VARCHAR2(20), points PolygonType) NESTED TABLE points STORE AS PointsTable; 

The ``tiny'' relations that represent individual polygons are not stored directly as values of the points attribute. Rather, they are stored in a single table, whose name must be declared (although we cannot refer to it in any way). We see this declaration following the parenthesized list of attributes for the table; the name PointsTable was chosen to store the relations of type PolygonType.

• Be careful to get the punctuation right. There is one semicolon ending the CREATE TABLE statement, and it goes after both the parenthesized list of attributes and the NESTED TABLE clause.

When we insert into a relation like Polygons that has one or more columns that are of nested-relation type, we use the type constructor for the nested-relation type ( PolygonType in our example) to surround the value of one of these nested relations. The value of the nested relation is represented by a list of values of the appropriate type; in our example that type is PointType and is represented by the type constructor of the same name.

Here is a statement inserting a polygon named ``square'' that consists of four points, the corners of the unit square.

 INSERT INTO Polygons VALUES( 'square', PolygonType(PointType(0.0, 0.0), PointType(0.0, 1.0), PointType(1.0, 0.0), PointType(1.0, 1.0) ) ); 

We can obtain the points of this square by a query such as:

 SELECT points FROM Polygons WHERE name = 'square'; 

It is also possible to get a particular nested relation into the FROM clause by use of the keyword THE, applied to a subquery whose result is a relation; the above query is an example, since it returns a whole nested relation. For instance, the following query finds those points of the polygon named square that are on the main diagonal (i.e., x=y).

 SELECT ss.x FROM THE(SELECT points FROM Polygons WHERE name = 'square' ) ss WHERE ss.x = ss.y; 

In this query, the nested relation is given an alias ss, which is used in the SELECT and WHERE clauses as if it were any ordinary relation.

---

Combining Nested Relations and References

Things get tricky when we do the natural thing (to keep data normalized) and make a nested table whose tuples are actually references to tuples in some other table. The problem is that the nested table's attribute has no name. Oracle provides the name COLUMN_VALUE to use in this circumstance. Here's an example that modifies the above discussion of polygons to have a nested table of references. First, we create a new type that is a nested table of references to points:

 CREATE TYPE PolygonRefType AS TABLE OF REF PointType; / 

Next, we need a new relation, similar to Polygons, but with the points of a polygon stored as a nested table of references:

 CREATE TABLE PolygonsRef ( name VARCHAR2(20), pointsRef PolygonRefType) NESTED TABLE pointsRef STORE AS PointsRefTable; 

Remember that the points themselves must be stored in some relation of type PointType; we omit this part of the process of creating and loading data. To query the points in a nested table, as we did for the query above that asked for the points on the main diagonal, we write essentially the same query, except that we must use COLUMN_VALUE to refer to the column of the nested table. The query becomes:

 SELECT ss.COLUMN_VALUE.x FROM THE(SELECT pointsRef FROM PolygonsRef WHERE name = 'square' ) ss WHERE ss.COLUMN_VALUE.x = ss.COLUMN_VALUE.y; 

---

Converting Ordinary Relations to Object-Relations

If we have data in an ordinary relation (i.e., one whose attributes are all built-in types of SQL), and we want to create an equivalent relation whose type is a user-defined object type or a relation one or more of whose attributes are object types, we can use the form of an INSERT statement that defines the inserted tuples by a query. The query can use the type constructors as appropriate.

For example, suppose we have a relation LinesFlat declared by:

 CREATE TABLE LinesFlat( id INT, x1 NUMBER, y1 NUMBER, x2 NUMBER, y2 NUMBER ); 

and this relation contains lines represented in the ``old'' style, that is, an ID and four components representing the x- and y-coordinates of two points. We can copy this data into Lines and give it the right structure by:

 INSERT INTO Lines SELECT id, LineType(PointType(x1,y1), PointType(x2,y2)) FROM LinesFlat; 

Insertion with a SELECT clause into a table with nested relations is tricky. If we simply want to insert into an existing nested relation, we can use THE with specified values. For instance, if we want to insert the point (2.0, 3.0) into the nested relation for the polygon named ``triangle,'' we can write:

 INSERT INTO THE(SELECT points FROM Polygons WHERE name = 'triangle' ) VALUES(PointType(2,0, 3.0)); 

Now, suppose we already have a ``flat'' relation representing points of polygons:

 CREATE TABLE PolyFlat ( name VARCHAR2(20), x NUMBER, y NUMBER ); 

If the points of a square are represented in PolyFlat, then we can copy them into Polygons by:

1. Querying PolyFlat for the points of a square.

2. Turning the collection of answers to our query into a relation by applying the keyword MULTISET.
3. Turning the relation into a value of type PolygonType with the expression CAST ... AS PolygonType.
4. Using 'square' and the value constructed in (3) as arguments to a VALUE expression.

Here is the command:

 INSERT INTO Polygons VALUES('square', CAST( MULTISET(SELECT x, y FROM PolyFlat WHERE name = 'square' ) AS PolygonType ) ); 

Even more complex is the way we can copy data from the flat PolyFlat to put all the polygons and their sets of points into Polygons. The following almost works:

 INSERT INTO Polygons SELECT pp.name, CAST( MULTISET(SELECT x, y FROM PolyFlat qq WHERE qq.name = pp.name ) AS PolygonType ) FROM PolyFlat pp; 

The problem is that if there are four points, then there are four tuples with name 'square' inserted. Adding DISTINCT after the first SELECT doesn't work. We have to find a way to perform the insertion for each polygon name only once, and a reasonable way is to add a WHERE clause that insists the x and y components of the PolyFlat tuple be lexicographically first. Here is a working insertion command:

 INSERT INTO Polygons SELECT pp.name, CAST( MULTISET(SELECT x, y FROM PolyFlat qq WHERE qq.name = pp.name ) AS PolygonType ) FROM PolyFlat pp WHERE NOT EXISTS( SELECT * FROM PolyFlat rr WHERE rr.name = pp.name AND rr.x < pp.x OR rr.x = pp.x AND rr.y < pp.y ); 

---

This document was written originally by Jeff Ullman for CS145 in the Autumn of 1998. Special thanks to Ian Mizrahi for the detective work on the  feature.