| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle8 can support list or set type?
CREATE TYPE Complex AS OBJECT
rpart REAL,
ipart REAL,
MEMBER FUNCTION plus (x Complex) RETURN Complex,
MEMBER FUNCTION less (x Complex) RETURN Complex,
MEMBER FUNCTION times (x Complex) RETURN Complex,
MEMBER FUNCTION divby (x Complex) RETURN Complex
);
CREATE TYPE BODY Complex AS
MEMBER FUNCTION plus (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart + x.rpart, ipart + x.ipart);
END plus;
MEMBER FUNCTION less (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart - x.rpart, ipart - x.ipart);
END less;
MEMBER FUNCTION times (x Complex) RETURN Complex IS
BEGIN
RETURN Complex(rpart * x.rpart - ipart * x.ipart,
rpart * x.ipart + ipart * x.rpart);
END times;
z REAL := x.rpart**2 + x.ipart**2; BEGIN
RETURN Complex((rpart * x.rpart + ipart * x.ipart) / z,
(ipart * x.rpart - rpart * x.ipart) / z);
END divby;
Attributes
Like a variable, an attribute is declared with a name and datatype. The name
must be unique within the object type (but can be reused in other object
types). The datatype can be any Oracle type except
LONG and LONG RAW
NCHAR, NCLOB, and NVARCHAR2
MLSLABEL and ROWID
the PL/SQL-specific types BINARY_INTEGER (and its subtypes), BOOLEAN,
PLS_INTEGER, RECORD, REF CURSOR, %TYPE, and %ROWTYPE
types defined inside a PL/SQL package
For example, the REAL variables rpart and ipart are attributes of object
type Complex (defined in the previous section).
You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Also, you cannot impose the NOT NULL constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.
The kind of data structure formed by a set of attributes depends on the real-world object being modeled. For example, to represent a rational number, which has a numerator and a denominator, you need only two INTEGER variables. On the other hand, to represent a college student, you need several VARCHAR2 variables to hold a name, address, phone number, status, and so on, plus a VARRAY variable to hold courses and grades.
The data structure can be very complex. For example, the datatype of an attribute can be another object type (called a nested object type). That lets you build a complex object type from simpler object types. Some object types such as queues, lists, and trees are dynamic, meaning that they can grow as they are used. Recursive object types, which contain direct or indirect references to themselves, allow for highly sophisticated data models.
Methods
In general, a method is a subprogram declared in an object type
specification using the keyword MEMBER. The method cannot have the same name
as the object type or any of its attributes.
Like packaged subprograms, most methods have two parts: a specification and a body. The specification consists of a method name, an optional parameter list, and, for functions, a return type. The body is the code that executes to perform a specific operation. For example, the functions plus, less, times, and divby are methods of object type Complex. These methods are always available to Complex objects.
For each method specification in an object type specification, there must be a corresponding method body in the object type body. To match method specifications and bodies, the PL/SQL compiler does a token-by-token comparison of their headers. So, the headers must match word for word.
In an object type, methods can reference attributes and other methods without a qualifier, as the example below shows.
CREATE TYPE Stack AS OBJECT
top INTEGER,
MEMBER FUNCTION full RETURN BOOLEAN,
MEMBER PROCEDURE push (n IN INTEGER),
...
);
CREATE TYPE BODY Stack AS
...
MEMBER PROCEDURE push (n IN INTEGER) IS
BEGIN
IF NOT full THEN
top := top + 1;
...
CREATE TYPE Complex AS OBJECT
MEMBER FUNCTION transform (SELF IN OUT Complex) ...
In member functions, if SELF is not declared, its parameter mode defaults to IN. However, in member procedures, if SELF is not declared, its parameter mode defaults to IN OUT. You cannot specify a different datatype for SELF.
In a method body, SELF denotes the object whose method was called. As the following example shows, methods can reference the attributes of SELF without a qualifier:
CREATE FUNCTION gcd (x INTEGER, y INTEGER) RETURN INTEGER AS -- find greatest common divisor of x and y
ans INTEGER;
BEGIN
IF (y <= x) AND (x MOD y = 0) THEN ans := y;
ELSIF x < y THEN ans := gcd(y, x);
ELSE ans := gcd(y, x MOD y);
END IF;
RETURN ans;
END;
CREATE TYPE Rational AS OBJECT
num INTEGER,
den INTEGER,
MEMBER PROCEDURE normalize,
...
);
CREATE TYPE BODY Rational AS
MEMBER PROCEDURE normalize IS
g INTEGER;
BEGIN
You cannot overload two methods if their formal parameters differ only in parameter mode. Also, you cannot overload two member functions that differ only in return type. For more information, see "Overloading".
Map and Order Methods
The values of a scalar datatype such as CHAR or REAL have a predefined
order, which allows them to be compared. But, instances of an object type
have no predefined order. To put them in order, PL/SQL calls a map method
supplied by you. In the following example, the keyword MAP indicates that
method convert orders Rational objects by mapping them to REAL values:
CREATE TYPE Rational AS OBJECT
num INTEGER,
den INTEGER,
MAP MEMBER FUNCTION convert RETURN REAL,
...
);
CREATE TYPE BODY Rational AS
MAP MEMBER FUNCTION convert RETURN REAL IS
An object type can contain only one map method, which must be a parameterless function with one of the following scalar return types: DATE, NUMBER, VARCHAR2, an ANSI SQL type such as CHARACTER or REAL.
Alternatively, you can supply PL/SQL with an order method. In the example below, the keyword ORDER indicates that method match compares two objects. Every order method takes just two parameters: the built-in parameter SELF and another object of the same type.
If c1 and c2 are Customer objects, a comparison such as c1 > c2 calls method match automatically. The method returns a negative number, zero, or a positive number signifying that SELF is respectively less than, equal to, or greater than the other parameter.
CREATE TYPE Customer AS OBJECT
id NUMBER,
name VARCHAR2(20),
addr VARCHAR2(30),
ORDER MEMBER FUNCTION match (c Customer) RETURN INTEGER
);
CREATE TYPE BODY Customer AS
ORDER MEMBER FUNCTION match (c Customer) RETURN INTEGER IS BEGIN
IF id < c.id THEN
RETURN -1; -- any negative number will do
ELSIF id > c.id THEN
RETURN 1; -- any positive number will do
ELSE
RETURN 0;
END IF;
Guidelines
A map method, acting like a hash function, maps object values into scalar
values (which are easier to compare), then compares the scalar values. An
order method simply compares one object value to another.
You can declare a map method or an order method but not both. If you declare either method, you can compare objects in SQL and procedural statements. However, if you declare neither method, you can compare objects only in SQL statements and only for equality or inequality. (Two objects of the same type are equal only if the values of their corresponding attributes are equal.)
When sorting or merging a large number of objects, use a map method. One call maps all the objects into scalars, then sorts the scalars. An order method is less efficient because it must be called repeatedly (it can compare only two objects at a time). You must use a map method for hash joins because PL/SQL hashes on the object value.
Constructor Methods
Every object type has a constructor method (constructor for short), which is
a system-defined function with the same name as the object type. You use the
constructor to initialize and return an instance of that object type.
Oracle generates a default constructor for every object type. The formal parameters of the constructor match the attributes of the object type. That is, the parameters and attributes are declared in the same order and have the same names and datatypes.
PL/SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed. For more information, see "Calling Constructors and Methods".
Pragma RESTRICT_REFERENCES
To execute a SQL statement that calls a member function, Oracle must know
the purity level of the function, that is, the extent to which the function
is free of side effects. (In this context, side effects are references to
database tables or packaged variables.)
Side effects can prevent the parallelization of a query, yield order-dependent (and therefore indeterminate) results, or require that package state be maintained across user sessions (which is not allowed). So, the following rules apply to a member function called from SQL statements:
It cannot insert into, update, or delete from database tables.
It cannot be executed remotely or in parallel if it reads or writes the
values of packaged variables.
It cannot write the values of packaged variables unless it is called from a
SELECT, VALUES, or SET clause.
It cannot call another method or subprogram that breaks one of the foregoing
rules. Also, it cannot reference a view that breaks one of the rules.
(Oracle replaces references to a view with a stored SELECT operation, which
can include function calls.)
You use the pragma (compiler directive) RESTRICT_REFERENCES to enforce these
rules. The pragma tells the PL/SQL compiler to deny the member function
read/write access to database tables, packaged variables, or both.
In the object type specification, you code the pragma somewhere after the method to which it applies. The syntax follows:
PRAGMA RESTRICT_REFERENCES ({DEFAULT | method_name},
{RNDS | WNDS | RNPS | WNPS}[, {RNDS | WNDS | RNPS | WNPS}]...); For example, the following pragma constrains map method convert to read no database state (RNDS), write no database state (WNDS), read no package state (RNPS), and write no package state (WNPS):
CREATE TYPE Rational AS OBJECT
num INTEGER,
den INTEGER,
MAP MEMBER FUNCTION convert RETURN REAL,
...
PRAGMA RESTRICT_REFERENCES (convert, RNDS,WNDS,RNPS,WNPS)
);
You can specify up to four constraints in any order. To call the method from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.
If you specify the keyword DEFAULT instead of a method name, the pragma applies to all member functions including the system-defined constructor. For example, the following pragma constrains all member functions to write no database or package state:
PRAGMA RESTRICT_REFERENCES (DEFAULT, WNDS, WNPS) You can declare the pragma for any member function. Such pragmas override the default pragma. However, a non-default pragma can apply to only one method. So, among overloaded methods, the pragma always applies to the nearest preceding method.
For more information about pragma RESTRICT_REFERENCES, see Oracle8 Application Developer's Guide.
Defining Object Types
An object type can represent any real-world entity. For example, an object
type can represent a student, bank account, computer screen, rational
number, or data structure such as a queue, stack, or list. This section
gives several complete examples, which teach you a lot about the design of
object types and prepare you to start writing your own.
Currently, you cannot define object types in a PL/SQL block, subprogram, or package. However, you can define them interactively in SQL*Plus or Enterprise Manager using the following syntax:
CREATE TYPE type_name {IS | AS} OBJECT (
attribute_name datatype[, attribute_name datatype]...
[{MAP | ORDER} MEMBER function_specification,]
[ MEMBER {procedure_specification | function_specification}
| restrict_references_pragma
[, MEMBER {procedure_specification | function_specification}
| restrict_references_pragma]]...);
[CREATE TYPE BODY type_name {IS | AS}
{ {MAP | ORDER} MEMBER function_body;
| MEMBER {procedure_body | function_body};}
[MEMBER {procedure_body | function_body};]... END;]
Object Type Stack
A stack holds an ordered collection of data items. As the name implies,
stacks have a top and a bottom. But, items can be added or removed only at
the top. So, the last item added to a stack is the first item removed.
(Think of the stack of clean serving trays in a cafeteria.) The operations
push and pop update the stack while preserving last in, first out (LIFO)
behavior.
Stacks have many applications. For example, they are used in systems programming to prioritize interrupts and to manage recursion. The simplest implementation of a stack uses an integer array. Integers are stored in array elements, with one end of the array representing the top of the stack.
PL/SQL provides the datatype VARRAY, which allows you to declare variable-size arrays (varrays for short). To declare a varray attribute, we must first define its type. However, we cannot define types in an object type specification. So, we define a stand-alone varray type, specifying its maximum size, as follows:
CREATE TYPE IntArray AS VARRAY(25) OF INTEGER;
Now, we can write our object type specification, as follows:
CREATE TYPE Stack AS OBJECT
max_size INTEGER,
top INTEGER,
position IntArray,
MEMBER PROCEDURE initialize,
MEMBER FUNCTION full RETURN BOOLEAN,
MEMBER FUNCTION empty RETURN BOOLEAN,
MEMBER PROCEDURE push (n IN INTEGER),
MEMBER PROCEDURE pop (n OUT INTEGER)
);
Finally, we write the object type body, as follows:
CREATE TYPE BODY Stack AS
MEMBER PROCEDURE initialize IS
BEGIN
top := 0;
/* Call constructor for varray and set element 1 to NULL. */
position := IntArray(NULL);
max_size := position.LIMIT; -- get varray size constraint (25)
position.EXTEND(max_size - 1, 1); -- copy element 1 into 2..25
END initialize;
MEMBER FUNCTION full RETURN BOOLEAN IS
BEGIN
RETURN (top = max_size); -- return TRUE if stack is full
END full;
MEMBER FUNCTION empty RETURN BOOLEAN IS
BEGIN
RETURN (top = 0); -- return TRUE if stack is empty
END empty;
MEMBER PROCEDURE push (n IN INTEGER) IS BEGIN
IF NOT full THEN
top := top + 1; -- push integer onto stack
position(top) := n;
ELSE -- stack is full
RAISE_APPLICATION_ERROR(-20101, `stack overflow');
END IF;
MEMBER PROCEDURE pop (n OUT INTEGER) IS BEGIN
IF NOT empty THEN
n := position(top);
top := top - 1; -- pop integer off stack
ELSE -- stack is empty
RAISE_APPLICATION_ERROR(-20102, `stack underflow');
END IF;
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(err_num) || ': ' || err_msg);
The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg.
Alternatively, the program can use pragma EXCEPTION_INIT to map the error numbers returned by raise_application_error to named exceptions, as the following example shows:
DECLARE
stack_overflow EXCEPTION;
stack_underflow EXCEPTION;
PRAGMA EXCEPTION_INIT(stack_overflow, -20101);
PRAGMA EXCEPTION_INIT(stack_underflow, -20102);
BEGIN
...
EXCEPTION
WHEN stack_overflow THEN ...
Object Type Ticket_Booth
Consider a chain of low-budget, triplex movie theatres. Each theatre has a
ticket booth where tickets for three different movies are sold. All tickets
are priced at $2.50. Periodically, ticket receipts are collected and the
stock of tickets is replenished.
Before defining an object type that represents a ticket booth, we must consider the data and operations needed. For a simple ticket booth, the object type needs attributes for the ticket price, quantity of tickets on hand, and receipts. It also needs methods for the following operations: purchase ticket, take inventory, replenish stock, and collect receipts.
For receipts, we use a three-element varray. Elements 1, 2, and 3 record the ticket receipts for movies 1, 2, and 3, respectively. To declare a varray attribute, we must first define its type, as follows:
CREATE TYPE RealArray AS VARRAY(3) OF REAL;
Now, we can write our object type specification, as follows:
CREATE TYPE Ticket_Booth AS OBJECT
price REAL,
qty_on_hand INTEGER,
receipts RealArray,
MEMBER PROCEDURE initialize,
MEMBER PROCEDURE purchase (
movie INTEGER,
amount REAL,
change OUT REAL),
Finally, we write the object type body, as follows:
CREATE TYPE BODY Ticket_Booth AS
MEMBER PROCEDURE initialize IS
BEGIN
price := 2.50;
qty_on_hand := 5000; -- provide initial stock of tickets
-- call constructor for varray and set elements 1..3 to zero
receipts := RealArray(0,0,0);
MEMBER PROCEDURE purchase (
movie INTEGER,
amount REAL,
change OUT REAL) IS
BEGIN
IF qty_on_hand = 0 THEN
RAISE_APPLICATION_ERROR(-20103, `out of stock');
END IF;
IF amount >= price THEN
qty_on_hand := qty_on_hand - 1;
receipts(movie) := receipts(movie) + price;
change := amount - price;
ELSE -- amount is not enough
change := amount; -- so return full amount
END IF;
MEMBER FUNCTION inventory RETURN INTEGER IS
BEGIN
RETURN qty_on_hand;
END inventory;
MEMBER PROCEDURE replenish (quantity INTEGER) IS
BEGIN
qty_on_hand := qty_on_hand + quantity;
END replenish;
MEMBER PROCEDURE collect (movie INTEGER, amount OUT REAL) IS BEGIN
amount := receipts(movie); -- get receipts for a given movie
receipts(movie) := 0; -- reset receipts for that movie to zero
END collect;
First, we write the object type specification, as follows:
CREATE TYPE Bank_Account AS OBJECT
acct_number INTEGER(5),
balance REAL, status VARCHAR2(10),
RETURN REAL
);
Then, we write the object type body, as follows:
CREATE TYPE BODY Bank_Account AS
MEMBER PROCEDURE open (amount IN REAL) IS
MEMBER PROCEDURE verify_acct (num IN INTEGER) IS
MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL) IS
MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL) IS BEGIN
verify_acct(num);
IF NOT amount > 0 THEN
RAISE_APPLICATION_ERROR(-20104, `bad amount');
END IF;
balance := balance + amount;
MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL) IS
MEMBER FUNCTION curr_bal (SELF IN OUT Bank_Account, num IN INTEGER)
RETURN REAL IS
BEGIN
verify_acct(num);
RETURN balance;
CREATE TYPE Rational AS OBJECT
num INTEGER,
den INTEGER,
MAP MEMBER FUNCTION convert RETURN REAL,
MEMBER PROCEDURE normalize,
MEMBER FUNCTION reciprocal RETURN Rational,
MEMBER FUNCTION plus (x Rational) RETURN Rational,
MEMBER FUNCTION less (x Rational) RETURN Rational,
MEMBER FUNCTION times (x Rational) RETURN Rational,
MEMBER FUNCTION divby (x Rational) RETURN Rational,
PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS,WNDS,RNPS,WNPS)
);
PL/SQL does not allow the overloading of operators. That is why we define methods named plus, less (the word minus is reserved), times, and divby instead of overloading the infix operators +, -, *, and /.
Next, we create the following stand-alone stored function, which will be called by method normalize.
CREATE FUNCTION gcd (x INTEGER, y INTEGER) RETURN INTEGER AS -- find greatest common divisor of x and y
ans INTEGER;
BEGIN
IF (y <= x) AND (x MOD y = 0) THEN
ans := y;
ELSIF x < y THEN
ans := gcd(y, x); -- recursive call
ELSE
ans := gcd(y, x MOD y); -- recursive call
END IF;
RETURN ans;
END;
Then, we write the object type body, as follows:
CREATE TYPE BODY Rational AS
MAP MEMBER FUNCTION convert RETURN REAL IS
MEMBER PROCEDURE normalize IS
MEMBER FUNCTION reciprocal RETURN Rational IS
MEMBER FUNCTION plus (x Rational) RETURN Rational IS
MEMBER FUNCTION less (x Rational) RETURN Rational IS
MEMBER FUNCTION times (x Rational) RETURN Rational IS
MEMBER FUNCTION divby (x Rational) RETURN Rational IS
Such objects follow the usual scope and instantiation rules. In a block or subprogram, local objects are instantiated when you enter the block or subprogram and cease to exist when you exit. In a package, objects are instantiated when you first reference the package and cease to exist when you end the database session.
Declaring Objects
You can use object types wherever built-in types such as CHAR or NUMBER can
be used. In the block below, you declare object r of type Rational. Then,
you call the constructor for object type Rational to initialize the object.
The call assigns the values 6 and 8 to attributes num and den, respectively.
DECLARE
r Rational;
BEGIN
r := Rational(6, 8);
DBMS_OUTPUT.PUT_LINE(r.num); -- prints 6
You can declare objects as the formal parameters of functions and procedures. That way, you can pass objects to stored subprograms and from one subprogram to another. In the next example, you use object type Account to specify the datatype of a formal parameter:
DECLARE
...
PROCEDURE open_acct (new_acct IN OUT Account) IS ...
In the following example, you use object type Account to specify the return type of a function:
DECLARE
...
FUNCTION get_acct (acct_id IN INTEGER) RETURN Account IS ...
Initializing Objects
Until you initialize an object by calling the constructor for its object
type, the object is atomically null. That is, the object itself is null, not
just its attributes. Consider the following example:
DECLARE
r Rational; -- r becomes atomically null
BEGIN
r := Rational(2,3); -- r becomes 2/3
A null object is never equal to another object. In fact, comparing a null object with any other object always yields NULL. Also, if you assign an atomically null object to another object, the other object becomes atomically null (and must be reinitialized). Likewise, if you assign the non-value NULL to an object, the object becomes atomically null, as the following example shows:
DECLARE
r Rational;
BEGIN
r Rational := Rational(1,2); -- r becomes 1/2
r := NULL; -- r becomes atomically null
IF r IS NULL THEN ... -- condition yields TRUE
A good programming practice is to initialize an object in its declaration, as shown in the following example:
DECLARE
r Rational := Rational(2,3); -- r becomes 2/3
How PL/SQL Treats Uninitialized Objects In an expression, attributes of an uninitialized object evaluate to NULL. Trying to assign values to attributes of an uninitialized object raises the predefined exception ACCESS_INTO_NULL. When applied to an uninitialized object or its attributes, the IS NULL comparison operator yields TRUE.
The following example illustrates the difference between null objects and objects with null attributes:
DECLARE
r Rational; -- r is atomically null
BEGIN
IF r IS NULL THEN ... -- yields TRUE
IF r.num IS NULL THEN ... -- yields TRUE
r := Rational(NULL, NULL); -- initializes r
r.num := 4; -- succeeds because r is no longer atomically null
-- even though all its attributes are null
r := NULL; -- r becomes atomically null again
r.num := 4; -- raises ACCESS_INTO_NULL
EXCEPTION
Accessing Attributes
You can refer to an attribute only by name (not by its position in the
object type). To access or change the value of an attribute, you use dot
notation. In the example below, you assign the value of attribute den to
variable denominator. Then, you assign the value stored in variable
numerator to attribute num.
DECLARE
r Rational := Rational(NULL, NULL);
numerator INTEGER;
denominator INTEGER;
BEGIN
...
denominator := r.den;
r.num := numerator;
Attribute names can be chained, which allows you to access the attributes of a nested object type. For example, suppose we define object types Address and Student, as follows:
CREATE TYPE Address AS OBJECT
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip_code VARCHAR2(5)
);
CREATE TYPE Student AS OBJECT
name VARCHAR2(20),
home_address Address,
phone_number VARCHAR2(10), status VARCAHR2(10), advisor_name VARCHAR2(20),
Notice that zip_code is an attribute of object type Address and that Address is the datatype of attribute home_address in object type Student. If s is a Student object, you access the value of its zip_code attribute as follows:
s.home_address.zip_code
Calling Constructors and Methods
Calls to a constructor are allowed wherever function calls are allowed. Like
all functions, a constructor is called as part of an expression, as the
following example shows:
DECLARE
r1 Rational := Rational(2, 3);
FUNCTION average (x Rational, y Rational) RETURN Rational IS
BEGIN
...
END;
BEGIN
r1 := average(Rational(3, 4), Rational(7, 11));
IF (Rational(5, 8) > r1) THEN
...
END IF;
END;
Passing Parameters to a Constructor
When you pass parameters to a constructor, the call assigns initial values
to the attributes of the object being instantiated. You must supply a
parameter for every attribute because, unlike constants and variables,
attributes cannot have DEFAULT clauses. As the following example shows, the
nth parameter assigns a value to the nth attribute:
DECLARE
r Rational;
BEGIN
r := Rational(5, 6); -- assign 5 to num, 6 to den
You can call a constructor using named notation instead of positional notation, as the following example shows:
BEGIN
r := Rational(den => 6, num => 5); -- assign 5 to num, 6 to den
Calling Methods
Like packaged subprograms, methods are called using dot notation. In the
example below, you call method normalize, which divides attributes num and
den by their greatest common divisor.
DECLARE
r Rational;
BEGIN
r := Rational(6, 8);
r.normalize;;
DBMS_OUTPUT.PUT_LINE(r.num); -- prints 3
As the example below shows, you can chain method calls. Execution proceeds from left to right. First, member function reciprocal is called, then member procedure normalize is called.
DECLARE
r Rational := Rational(6, 8);
BEGIN
r.reciprocal().normalize;
DBMS_OUTPUT.PUT_LINE(r.num); -- prints 4
In SQL statements, calls to a parameterless method require an empty parameter list. In procedural statements, an empty parameter list is optional unless you chain calls, in which case it is required for all but the last call.
You cannot chain additional method calls to the right of a procedure call because procedures are called as statements, not as part of an expression. For example, the following statement is illegal:
r.normalize().reciprocal; -- illegal
Also, if you chain two function calls, the first function must return an object that can be passed to the second function.
Sharing Objects
Most real-world objects are considerably larger and more complex than
objects of type Rational. Consider the following object types:
CREATE TYPE Address AS OBJECT
street_address VARCHAR2(35),
city VARCHAR2(15), state CHAR(2), zip_code INTEGER
CREATE TYPE Person AS OBJECT
first_name VARCHAR2(15),
last_name VARCHAR2(15),
birthday DATE,
home_address Address, -- nested object type
phone_number VARCHAR2(15),
ss_number INTEGER,
...
);
Address objects have twice as many attributes as Rational objects, and Person objects have still more attributes including one of type Address. When an object is large, it is inefficient to pass copies of it from subprogram to subprogram. It makes more sense to share the object. You can do that if the object has an object identifier. To share the object, you use references (refs for short). A ref is a pointer to an object.
Sharing has two important advantages. First, data is not replicated unnecessarily. Second, when a shared object is updated, the change occurs in only one place, and any ref can retrieve the updated values instantly.
In the following example, we gain the advantages of sharing by defining object type Home and then creating a table that stores instances of that object type:
CREATE TYPE Home AS OBJECT
address VARCHAR2(35),
owner VARCHAR2(25), age INTEGER, style VARCHAR(15), floor plan BLOB, price REAL(9,2),
Using Refs
By revising object type Person, we can model a community in which several
people might share the same home. We use the type modifier REF to declare
refs, which hold pointers to objects.
CREATE TYPE Person AS OBJECT
first_name VARCHAR2(10),
last_name VARCHAR2(15),
birthday DATE,
home_address REF Home, -- can be shared by family
phone_number VARCHAR2(15),
ss_number INTEGER,
mother REF Person, -- family members refer to each other father REF Person,
Notice how references from persons to homes and between persons model real-world relationships.
You can declare refs as variables, parameters, fields, or attributes. And, you can use refs as input or output variables in SQL data manipulation statements. However, you cannot navigate through refs. Given an expression such as x.attribute, where x is a ref, PL/SQL cannot navigate to the table in which the referenced object is stored. For example, the following assignment is illegal:
DECLARE
p_ref REF Person;
phone_no VARCHAR2(15);
BEGIN
...
phone_no := p_ref.phone_number; -- illegal
Instead, you must use the operator DEREF to access the object. For some examples, see "Using Operator DEREF".
Forward Type Definitions
You can refer only to schema objects that already exist. In the following
example, the first CREATE TYPE statement is illegal because it refers to
object type Department, which does not yet exist.
CREATE TYPE Employee AS OBJECT (
name VARCHAR2(20),
dept REF Department, -- illegal
...
);
CREATE TYPE Department AS OBJECT (
number INTEGER,
manager Employee,
...
);
Switching the CREATE TYPE statements does not help because the object types are mutually dependent; that is, one depends on the other through a ref. To solve this problem, you use a special CREATE TYPE statement called a forward type definition, which lets you define mutually dependent object types.
To debug the last example, simply precede it with the following statement:
CREATE TYPE Department; -- forward type definition
The object type created by a forward type definition is called an incomplete object type because (until it is defined fully) it has no attributes or methods.
An impure incomplete object type has attributes but compiles with semantic (not syntactic) errors because it refers to an undefined type. For example, the following CREATE TYPE statement compiles with a semantic error because object type Address is undefined:
CREATE TYPE Customer AS OBJECT (
id NUMBER,
name VARCHAR2(20),
addr Address, -- not yet defined
phone VARCHAR2(15)
);
This allows you to defer the definition of object type Address. Moreover, the incomplete type Customer can be made available to other application developers for use in refs.
Manipulating Objects
You can use an object type in the CREATE TABLE statement to specify the
datatype of a column. Once the table is created, you can use SQL statements
to insert an object, select its attributes, call its methods, and update its
state.
In the SQL*Plus script below, the INSERT statement calls the constructor for object type Rational, then inserts the resulting object. The SELECT statement retrieves the value of attribute num. The UPDATE statement calls member method reciprocal, which returns a Rational value after swapping attributes num and den. Notice that a table alias is required when you reference an attribute or method. (For an explanation, see Appendix E.)
CREATE TABLE numbers (rn Rational, ...)
/
INSERT INTO numbers (rn) VALUES (Rational(3, 62)) -- inserts 3/62
/
SELECT n.rn.num INTO my_num FROM numbers n WHERE ... -- returns 3
/
UPDATE numbers n SET n.rn = n.rn.reciprocal WHERE ... -- yields 62/3
/
When you instantiate an object this way, it has no identity outside the database table. However, the object type exists independently of any table, and can be used to create objects in other ways.
In the next example, you create a table that stores objects of type Rational in its rows. Such tables, having rows of objects, are called object tables. Each column in a row corresponds to an attribute of the object type. Rows can have different column values.
CREATE TABLE rational_nums OF Rational;
Each row in an object table has an object identifier, which uniquely identifies the object stored in that row and serves as a reference to the object.
Selecting Objects
Assume that you have run the following SQL*Plus script, which creates object
type Person and object table persons, and that you have populated the table:
CREATE TYPE Person AS OBJECT
first_name VARCHAR2(15),
last_name VARCHAR2(15),
birthday DATE,
home_address Address,
phone_number VARCHAR2(15))
/
CREATE TABLE persons OF Person
/
The following subquery produces a result set of rows containing only the attributes of Person objects:
BEGIN
INSERT INTO employees -- another object table of type Person
SELECT * FROM persons p
WHERE p.last_name LIKE `%Smith';
To return a result set of objects, you must use the operator VALUE, which is discussed in the next section.
Using Operator VALUE
As you might expect, the operator VALUE returns the value of an object.
VALUE takes as its argument a correlation variable. (In this context, a
correlation variable is a row variable or table alias associated with a row
in an object table.) For example, to return a result set of Person objects,
you use VALUE, as follows:
BEGIN
INSERT INTO employees
SELECT VALUE(p) FROM persons p
WHERE p.last_name LIKE `%Smith';
In the next example, you use VALUE to return a specific Person object:
DECLARE
p1 Person;
p2 Person;
...
BEGIN
SELECT VALUE(p) INTO p1 FROM persons p
WHERE p.last_name = `Kroll';
p2 := p1;
...
END;
At this point, p1 holds a local Person object, which is a copy of the stored
object whose last name is 'Kroll', and p2 holds another local Person object,
which is a copy of p1. As the following example shows, you can use these
variables to access and update the objects they hold:
BEGIN
p1.last_name := p1.last_name || `Jr';
Now, the local Person object held by p1 has the last name 'Kroll Jr'.
Using Operator REF
You can retrieve refs using the operator REF, which, like VALUE, takes as
its argument a correlation variable. In the following example, you retrieve
one or more refs to Person objects, then insert the refs into table
person_refs:
BEGIN
INSERT INTO person_refs
SELECT REF(p) FROM persons p
WHERE p.last_name LIKE '%Smith';
In the next example, you retrieve a ref and attribute at the same time:
DECLARE
p_ref REF Person;
taxpayer_id VARCHAR2(9);
BEGIN
SELECT REF(p), p.ss_number INTO p_ref, taxpayer_id
FROM persons p
WHERE p.last_name = 'Parker'; -- must return one row
DECLARE
p_ref REF Person;
my_last_name VARCHAR2(15);
...
BEGIN
...
SELECT REF(p) INTO p_ref FROM persons p
WHERE p.last_name = my_last_name; UPDATE persons p
SET p = Person('Jill', 'Anders', '11-NOV-67', ...)
WHERE REF(p) = p_ref;
BEGIN
UPDATE department SET manager = NULL WHERE manager IS DANGLING;
Using Operator DEREF
You cannot navigate through refs within PL/SQL procedural statements.
Instead, you must use the operator DEREF in a SQL statement. (DEREF is short
for dereference. When you dereference a pointer, you get the value to which
it points.) DEREF takes as its argument a reference to an object, then
returns the value of that object. If the ref is dangling, DEREF returns a
null object.
In the example below, you dereference a ref to a Person object. Notice that you select the ref from dummy table dual. You need not specify an object table and search criteria because each object stored in an object table has a unique, immutable object identifier, which is part of every ref to that object.
DECLARE
p1 Person;
p_ref REF Person;
name VARCHAR2(15);
BEGIN
...
/* Assume that p_ref holds a valid reference
to an object stored in an object table. */ SELECT DEREF(p_ref) INTO p1 FROM dual; name := p1.last_name;
You can use DEREF in successive SQL statements to dereference refs, as the following example shows:
CREATE TYPE PersonRef AS OBJECT (p_ref REF Person)
/
DECLARE
name VARCHAR2(15);
pr_ref REF PersonRef;
pr PersonRef; p Person;
The next example shows that you cannot use operator DEREF within procedural statements:
BEGIN
...
p1 := DEREF(p_ref); -- illegal
Within SQL statements, you can use dot notation to navigate through object columns to ref attributes and through one ref attribute to another. You can also navigate through ref columns to attributes if you use a table alias. For example, the following syntax is legal:
table_alias.object_column.ref_attribute table_alias.object_column.ref_attribute.attribute table_alias.ref_column.attribute
Assume that you have run the following SQL*Plus script, which creates object types Address and Person and object table persons:
CREATE TYPE Address AS OBJECT
street VARCHAR2(35),
city VARCHAR2(15),
state CHAR(2),
zip_code INTEGER)
/
CREATE TYPE Person AS OBJECT
first_name VARCHAR2(15),
last_name VARCHAR2(15),
birthday DATE,
home_address REF Address, -- shared with other Person objects
phone_number VARCHAR2(15))
/
CREATE TABLE persons OF Person
/
Ref attribute home_address corresponds to a column in object table persons that holds refs to Address objects stored in some other table. After populating the tables, you can select a particular address by dereferencing its ref, as follows:
DECLARE
addr1 Address,
addr2 Address,
...
BEGIN
SELECT DEREF(home_address) INTO addr1 FROM persons p
WHERE p.last_name = `Derringer';
In the example below, you navigate through ref column home_address to attribute street. In this case, a table alias is required.
DECLARE
my_street VARCHAR2(25),
...
BEGIN
SELECT p.home_address.street INTO my_street FROM persons p
WHERE p.last_name = `Lucas';
Inserting Objects
You use the INSERT statement to add objects to an object table. In the
following example, you insert a Person object into object table persons:
BEGIN
INSERT INTO persons
VALUES ('Jenifer', 'Lapidus', ...);
Alternatively, you can use the constructor for object type Person to insert an object into object table persons:
BEGIN
INSERT INTO persons
VALUES (Person('Albert', 'Brooker', ...));
In the next example, you use the RETURNING clause to store Person refs in local variables. Notice how the clause mimics a SELECT statement.You can also use the RETURNING clause in UPDATE and DELETE statements.
DECLARE
p1_ref REF Person;
p2_ref REF Person;
...
BEGIN
INSERT INTO persons p
VALUES (Person('Paul', 'Chang', ...))
RETURNING REF(p) INTO p1_ref;
INSERT INTO persons p
VALUES (Person('Ana', 'Thorne', ...))
RETURNING REF(p) INTO p2_ref;
To insert objects into an object table, you can use a subquery that returns objects of the same type. An example follows:
BEGIN
INSERT INTO persons2
SELECT VALUE(p) FROM persons p
WHERE p.last_name LIKE '%Jones';
The rows copied to object table persons2 are given new object identifiers. No object identifiers are copied from object table persons.
The script below creates a relational table named department, which has a column of type Person, then inserts a row into the table. Notice how constructor Person() provides a value for column manager.
CREATE TABLE department (
dept_name VARCHAR2(20),
manager Person,
location VARCHAR2(20))
/
INSERT INTO department
VALUES ('Payroll', Person('Alan', 'Tsai', ...), 'Los Angeles') /
The new Person object stored in column manager is not referenceable because it is stored in a column (not a row) and therefore has no object identifier.
Updating Objects
To modify the attributes of objects in an object table, you use the UPDATE
statement, as the following example shows:
BEGIN
UPDATE persons p SET p.home_address = '341 Oakdene Ave'
WHERE p.last_name = 'Brody';
...
UPDATE persons p SET p = Person('Beth', 'Steinberg', ...)
WHERE p.last_name = 'Steinway';
...
END;
Deleting Objects
You use the DELETE statement to remove objects (rows) from an object table.
To remove objects selectively, you use the WHERE clause, as the following
example shows:
BEGIN
DELETE FROM persons p
WHERE p.home_address = '108 Palm Dr';
...
END;
 |
 |