* Defining Types
* Dropping Types
* Constructing Objects
* Queries Involving Types
* Declaring Types For Relations
* Nested Tables
* Nested Tables of References
* Converting Relations to Object-Relations
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 (
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 (
Then, we could create a relation that is a set of lines with “line ID’s” as:
CREATE TABLE Lines (
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
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 (
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
RETURN scale *
* Notice that the mode of the argument is not given here.
source : internet