Object-Relational Features of Oracle
|Object-Relational Features of Oracle|
- Defining Types
- Dropping Types
- Constructing Objects
- Queries Involving Types
- Declaring Types For Relations
- Nested Tables
- Nested Tables of References
- Converting Relations to Object-Relations
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.
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 );
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) ); /
- 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.
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.
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 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.
SELECT ll.end1.x, ll.end2.x FROM Lines2 ll;
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.
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 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:
- Querying PolyFlat for the points of a square.
- Turning the collection of answers to our query into a relation by applying the keyword MULTISET.
- Turning the relation into a value of type PolygonType with the expression CAST ... AS PolygonType.
- Using 'square' and the value constructed in (3) as arguments to a VALUE expression.
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.