Object-translational features of oracle
DEFINING TYPES DROPPINGS QUERIES INVOLVING TYPES DESTED TABLES NESTED TABLES OF REFERENCES Converting Relations To Object-Relations
Defining TypeesoSoracle 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 TypeSto 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 Would Would Because Table
Lines Still EXISTS AND USES
LINTYPE.
CONSTRUCTING OBJECT VALUESLIKE C , Oracle Provides Built-in Constructors for Values of a Decilated Type, And these Constructionors Bear The name of the type. Thus, a value of type
PointType is formed by the word
PointType and a penthesized list of appropriate values. For example, here is how we would in, INSERT INTO
Lines a line with id27 That Ran from the Origin to the Point (3, 4):
INSERT INTO LINES VALUES (27, LINTYPE (PointType (PointType (0.0, 0.0), PointType (3.0, 4.0))); That IS, We Construct Two Values of TypePointType, The 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 methodsa type declaration can also include Methods That is deflared 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 type.
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 Return Number, Pragma Restrict_Reference (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 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 TypesValues of Components of An Object Areced with the dot notation. We act ot notation. We atually 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., IT Actually Products TWICE THESELENGTHS).
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. Sinceline is an attribute of type linetype, one can apply Ty, using the dot notation shown. here. here................
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 THYPE CONSTRUCTORS Are Used for Output As Well as for Input.
Types Can Also Be Relation Schemasthe Uses of Types So Far Has Been As `` Column Types, '' That IS, TYPES OF Attributes. In A
Create Table Statement We Can Replace The Preenthesized List of Schema Elements by the Keyword
Of and the name of a type. This type is the the said to be used as a `` row type. '' For example, to create a relation each of whose tuples is a pair of point, 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 typefor Every Type
T,
Ref
T is the Type of References (Object ID's if you will) to value of type
T. This Type Can Be Used in Places Where A Type Is Called 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 value, support, suppose we have limited
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 the x-value of the second) by:
INSERT INTO Lines2 SELECT REF (pp), REF (qq) FROM Points pp, Points qq WHERE pp.x The points referred to must be tuples of a relation of type PointType, such as Points above. They can not 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 POINTTYPE (1,2) DON '`` Live' 'in Any Relation. To Follow a Reference, We Use the dot notation, as if the attribute of review type were really the same Value referred to. for instance, this query gets the x-coordinates of the ends of all the lines inlines2. Select ll.end1.x, ll.end2.x from lines2 ll; Nested TablesA 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 RELAES for SCHEMA (A, B) HAS B-VALUES THAT ARE RELATIONS with Schema (x, y, z). AB- XYZ ---------- XYZ ---- XYZ ------------- 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 THEPE Polygontype is a rellation whose tuples are of type PointType; I., They Have Two Components, X and Y, Which Are Real Numbers. NOW, WE CAN DECLARE A RELAES 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 thepoints attribute Rather, they are stored in a single table Whose Name Must Be Decilad (Althought WA CANNOT REFER TO IT IN ANY WAY). WE See this Declaration FOLLOWING THENTHESIZED 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 Column That Arefi Nested-Relation Type, We Use Type Construction for the Nested-Relation Type PolygonType in Our Example) To Surround The Value of One of these Nested Relations. The value of the nested limited is represented by a list of values of the appreate type; in Our Example Type IS PointType and is represented by the Type Construction 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 (ie,. X = y) .Select ss.x from the (select point ") ss where ss.x = ss.y; in this query, the nested limited is given an alias SS, WHICH IS USED in the SELECT AND WHERE CLAUSES as if it wele any one ordinary relation. Combining Nested Relations and ReferencesThings 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 reason, 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-RelationsIf we have data in an ordinary relation (ie , 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 inserated 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 reference contacts lines represented in the `` Old '' Style, That IS, An ID and FOUR components representing the x- y- Coordinates of Two Points. We can Copy this data INTO Lines and give it the right structure by: INSERT INTO LINES SELECT ID, LINTTYPE (PointType (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, WE CAN USE The with specified values. For instance, if we want to insert the point for the poification `` 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 Repensenting 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 the in intolygons 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. Here is the command: Insert Into Polygons Values ('Square', Cast (SELT X, Y from Polyflat Where Name = 'Square') AS Polygontype); Even More Complex Is The Way We can CAN Copy Data from The Fla Polyflat to put all the polygons and their sets of points int Polygons. The folowing 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 instator Select Doesn't Work. We Have To Find A Way To Perform The Insert For Each Polygon Name Only, And A Reasonable Way Is To Add A WHERE CLAUSE THAT INSISTS THE X and Y Components of The x and y Polyflat tuple be lexicographical 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 r.x THIS Document Was Wristn ORIGINALLY BY JEFF ULLMAN for CS145 in The Autumn of 1998. Special Thanks to Ian Mizrahi for The Detective Work on The Feature.
