ObjectRelational Features of
Oracle 
CREATE TYPE t AS OBJECT ( list of attributes and methods ); /
CREATE TYPE PointType AS OBJECT ( x NUMBER, y NUMBER ); /An object type can be used like any other type in further declarations of objecttypes or tabletypes. 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 );
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.
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.
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) ); /
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.xSELF.end2.x)*(SELF.end1.xSELF.end2.x) + (SELF.end1.ySELF.end2.y)*(SELF.end1.ySELF.end2.y) ); END; END; /
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;
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.
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;
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 xvalue of the first is less than the xvalue 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.
SELECT ll.end1.x, ll.end2.x FROM Lines2 ll;
a  b  

 
 
 
 
 

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.
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.
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;
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 ycoordinates 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:
INSERT INTO Polygons VALUES('square', CAST( MULTISET(SELECT PointType(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 PointType(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 PointType(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 );