|
|
Object-Relational 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 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)
);
/
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;
/
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 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.
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 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:
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
);