By this time, most of us have
been introduced to the object-oriented features included in
Oracle. While many may be conversantly familiar with the new
functionality that the object extensions to Oracle have
introduced, far fewer have taken advantage of this exciting
departure from conventional relational design.
While it is recognized that the
first deployment of the object-relational database by Oracle
does not address every aspect of object-oriented methodology,
most of the key elements are covered.
One of the most exciting of these
key elements in the object extensions is the support for
methods. A method, in the purely object-oriented
paradigm, is the incorporation of a specific behavior assigned
to an object or element. Fundamentally, it is recognized that
the encapsulation of data with its associated behavior, is key
to the effective modeling and implementation of complex systems.
Part of the tremendous appeal of
the object-oriented paradigm is this ability to encapsulate data
and behavior together. Oracle’s newest version of the database
brings this sorely needed functionality to the object-relational
paradigm. The concept behind methods is of crucial importance in
the object-oriented world and will certainly prove to be equally
so in implementations requiring Oracle.
Two questions should immediately
come to mind in any discussion about object functionality in
Oracle. The first, most obvious one is: "How does it work"? The
second, but no less important one will be: "How does this impact
the way that database systems will be designed"? This paper will
seek to describe the ways in which methods can be effectively
incorporated into a working object-relational design.
Oracle Method Basics
If one were to consider a purely
object-oriented language like C++, it would be found that the
underlying principle of method design is the encapsulation of
data with its inherent behavior. Indeed, methods represent the
actions or events that are associated with a given data object
or class. Let us consider for a moment, a simple class object in
C++. Recall that a class is a type that consists of data members
and their functions. The associated functions comprise the class
methods and are also constituent members of that class. In a
language like C++ it is possible for elements to achieve this
homogeneous characteristic.
In the traditional framework of
relational design, behaviors have always maintained a disjoint
relationship with the data. The problem of data behavior has
been addressed through the use of database triggers, stored
packages and procedures, external procedures, and so on. In
truth, this approach never coupled behavior with data, but in
all fairness, it was never designed to do so. The
object-relational nature of Oracle makes great strides in
remedying the disparate way that relational methodology treats
the association between data and behavior.
Oracle has included a number of
extensions to SQL to support the creation and administration of
the object features, not the least of which are methods. Methods
must be declared as PL/SQL objects but a method definition may,
in turn, call any program accessible by the C language. Oracle
currently supports four different types of methods:
constructor, map, order, and
user defined.
A constructor method is a
function that creates a user defined object based on the
definition stated in the object type's specification. Oracle
names the new constructor method the same name as the new object
type. The parameters are the same as the attributes named in the
object type definition. From the standpoint of method execution,
think of the use of the object type name as the invocation of
the constructor method.
The creation of a type
automatically creates a method (constructor) to populate the
type. To call the method the original type name is used. The
constructor is never called implicitly but must be called
explicitly. The constructor can be used anywhere a function call
is used. Here is an example insert into the employee_info
table.
insert into employee_info
values(1,name_t('Michael','Ault','R'), <--- Note use of the name_t constructor method
dependent_list( <--- Note use of dependent_list constructor method to build insert array
dependent_t('Wife',name_t('Susan','Ault','K'),'39'), <---- Note use of dependent_t method
dependent_t('Daughter',name_t('Marie','Ault','C'),23),
dependent_t('Daughter',name_t('Michelle','Ault','E'),19))
6* );
Notice that the format for using
the constructor methods walk down the type tree. In this case
the type tree for the dependent type looks like so:
dependents <---- Attribute in table
dependent_list <----- Nested Table Type
dependent_t <----- Base Type
Dependents
is the attribute so it isn't used in the type tree. Therefore,
we start at the dependent_list type as the outermost
method and end at dependent_t, the basic type constructor
method. Since the type is for a nested table in this example, we
specify the dependent_t method multiple times for each
insert into the nested table. Notice that we also use a
constructor method, name_t, inside the innermost type
constructor to perform the insert into the dependents name
attribute which is itself a type. Population of a varray type is
identical to population of a nested table type with the
exception that you can only insert up to the maximum specified
count of the varray.
In Oracle, methods can also be
used in comparative operations when used with user-defined
object types. In the traditional relational model, scalar values
of type VARCHAR2 and NUMBER were easy to compare
and contrast. This simplicity diminishes when the data types are
more complex. Map and Order methods comprise the
set of comparison type methods. Map methods
provide a way to compare two objects to determine a quantitative
outcome. For example, let's say you defined an object type of
VEHICLE, you might specify an attribute that describes the
maximum speed. Map methods could perform a comparison to
determine the vehicle with the fastest or slowest maximum speed.
Order methods offer a different perspective. Order
methods use internal logic to compare two objects. Order methods
are well-suited for making comparisons of small groups of
instances against a single value. An order method returned value
is not quantitative but, rather, qualitative. In other words, a
return value would simply state that object A is less than
object B (return value of -1), objects A and B are the
same/equal (return value of 0), or object A is greater than
object B (return value of 1).
When declaring map and order
methods for comparative use of user defined data types, it is
important to note that there can be only one map or order method
per type. One or the other may be specified, but not both. The
following code example illustrates the use of map methods:
CREATE OR REPLACE TYPE site_t as object (
site_id INTEGER,
company company_t,
address address_t,
employee_id_r REF emp_t,
MEMBER FUNCTION get_ref
(emp_id IN integer)
RETURN REF emp_t,
MAP MEMBER FUNCTION give_site_id
RETURN INTEGER)
/
CREATE TYPE BODY site_t AS
MEMBER FUNCTION get_ref (emp_id IN integer)
RETURN INTEGER IS
status INTEGER:=0;
BEGIN
SELF.employee_id_r := emp_t.give_ref(emp_id);
RETURN status;
EXCEPTION
WHEN others THEN
status := 1;
RETURN status;
END;
MAP MEMBER FUNCTION give_site_id
RETURN INTEGER IS
BEGIN
RETURN site_id;
END;
END;
The previous listing will return
a site_id value whenever comparison values are required from the
site_t type. This allows GROUP BY, ORDER BY and other
sort dependent calls against the type to function properly.
The ORDER type MEMBER function
allows for ordering of entries that don't have simple solutions
like returning a site_id that is already stored in the
table. An ORDER function must return an integer value and is
only used once per call. A MAP function is used recursively. An
ORDER is most useful when you will be comparing a small number
of instances against a single value. A MAP function is most
useful when you are sorting large numbers of instances. An
example of an ORDER function is shown below:
CREATE OR REPLACE TYPE site_t as object (
site_id INTEGER,
company company_t,
address address_t,
employee_id_r REF emp_t,
MEMBER FUNCTION get_ref
(emp_id IN integer)
RETURN REF emp_t,
ORDER MEMBER FUNCTION give_order( site INTEGER)
RETURN INTEGER)
/
CREATE TYPE BODY site_t AS
MEMBER FUNCTION get_ref (emp_id IN integer)
RETURN INTEGER IS
status INTEGER:=0;
BEGIN
SELF.employee_id_r := emp_t.give_ref(emp_id);
RETURN status;
EXCEPTION
WHEN others THEN
status := 1;
RETURN status;
END;
ORDER MEMBER FUNCTION give_order( site INTEGER)
RETURN INTEGER IS
BEGIN
IF site<SELF.site
THEN
RETURN 1;
ELSIF site>SELF.site
THEN
RETURN -1;
Else
RETURN 0;
END;
END;
The code just illustrated creates
an ORDER function called give_order that returns -1, 1,
or 0 depending on the comparison of the input value to the value
of the instance attribute. Again, an ORDER function is used on a
one time basis (it can only compare the input value to the
current instance value) while a MAP method "maps" all instance
values into scalars when it is called.
Use of Other Built-in Methods for
Collections
In addition to the constructor
type Oracle also provides collection methods for use with
VARRAYS and NESTED TABLEs. These methods cannot be used outside
of PL/SQL. Collection methods cannot be used in DML but only in
procedural statements.
EXISTS -- Used to determine if a
specific element in a collection exists. EXISTS is used with
nested tables
COUNT -- Returns the number of
elements that a collection currently contains not including null
values. For varrays, count is equal to LAST. For nested tables,
COUNT and LAST may be different due to deleted values in
interstitial data sites in the nested table.
LIMIT -- Used for VARRAYS to
determine the maximum number of values allowed. If LIMIT is used
on a nested table it will return a null.
FIRST and LAST -- Return the
smallest and largest index numbers for the collection
referenced. Naturally, they return null if the collection is
empty. For VARRAYS FIRST always returns 1, for nested tables
FIRST returns the value of the first filled spot for that entry.
LAST returns the last filled instance of a VARRAY and a nested
table. For a VARRAY COUNT will always equal LAST. For a nested
table they can be different but LAST should always be greater
than COUNT if they are different for a nested table.
PRIOR and NEXT -- Return the
prior or next value based on the input value for the collection
index. PRIOR and NEXT ignore deleted instances in a collection.
EXTEND -- Appends instances to a
collection. EXTEND has three forms, EXTEND, which adds one null
instance, EXTEND(n) which adds "n" null instances and
EXTEND(n,m) which appends N copies of instance "m" to the
collection. For not null specified collections forms one and two
cannot be used.
TRIM -- Trim removes instances
from a collection. TRIM used with no arguments removes the last
instance, TRIM(n) removes "n" instances from the collection.
DELETE -- DELETE removes
specified items from a nested table or all of a VARRAY. DELETE
specified with no arguments removes all instances of a
collection. For nested tables only DELETE(n) removes the nth
instance and DELETE(n,m) deletes the nth through the mth
instances of the nested table that relate to the specified
master record.
Remember when using these methods
that a VARRAY is a dense type, that its values start at one and
go to the last filled value as far as the index is concerned
with no breaks and no null values allowed. A nested table is
allowed to have deleted values (i.e. it can be "sparse").
Therefore if you attempt to use a method that is inappropriate
such as DELETE with an argument against a VARRAY you will
receive an error.
Collection methods can raise the
following exceptions:
COLLECTION_IS_NULL -- Caused when
the collection referenced is atomically null.
NO_DATA_FOUND -- Subscript points
to a null instance of the collection.
SUBSCRIPT_BEYOND_COUNT -- The
specified subscript is beyond the number of instances in the
collection.
SUBSCRIPT_OUTSIDE_LIMIT -- The
specified subscript is outside the legal range (usually received
from VARRAY references)
VALUE_ERROR -- Subscript is null
or is not an integer.
These collection methods can be
used in a variety of ways. In the case of a nested table where
some of the values are null, selection using a standard cursor
could result in an exception. Use the FIRST and NEXT collection
methods to transverse these null values. The following code
sample illustrates an interesting use of several collection
methods:
j := dependents.FIRST;
WHILE j IS NOT NULL LOOP
process dependents(j)
j := dependents.NEXT(j);
END LOOP
So what does this code fragment
do? First, we use the FIRST collection method to get the index
integer of the first valid record for the dependents
nested table. Remember that a nested table is a sparse construct
so the first valid value may not be one. Next, we begin loop
processing of values assuming we didn’t get a null value on our
call to FIRST. Once we have processed the first value we reset
out counter to the appropriate NEXT value. Once NEXT evaluates
to NULL we exit the loop.
Another example would be for a
VARRAY where we want to only process the number of values that
are in each VARRAY instance set. To do this we can use FIRST and
LAST or, one (1) and COUNT.
FOR j IN addresses.FIRST..addresses.LAST LOOP
-- or ñ
FOR j in 1..addresses.COUNT LOOP
The last example shows how to
determine if a specific number of instances can be added to a
VARRAY using the LIMIT collection method.
IF (addresses.COUNT + 5) < addresses.LIMIT THEN
-- add 5 more addresses
Again, let us stress, as is shown
in these code fragments, the collection methods can only be used
in procedural statements and not in DML.
User Defined Methods
User defined methods are, as the
name implies, program units that perform some specialized action
other than a comparative function. The content and action of
user defined methods is at the developers discretion. User
defined methods can contain embedded calls to other methods,
external procedures, or even other programs. While the
declarative basis for all methods is PL/SQL, a method may access
any program callable by the C language. This provides a
tremendous amount of flexibility to extend the programmatic
control which developers may now exercise. When methods are
defined for a supertype, they are inherited by all subtypes
under the supertype. This points out two important aspects of
object type and method design: Created behaviors are shared
under an object type class, and each subtype receives the exact
same behavior (method). To illustrate some of the utility found
in user defined methods, the next section presents a brief but
powerful example.
Creation of a REF Request and REF Passing
Method Set
To properly implement an
object-oriented type set where a dependent table object uses a
REF referencing a master table object, two methods should be
created. The first method, which for convenience we will call
the get_ref method, and a second, which we will call the
give_ref method will be created. The first will reside in
the dependent table object type specification and the second
will reside in the master table object type specification. As
its name implies the whole purpose of the first method is to
request the value of a specific REF from the master table. The
whole purpose of the second method is to receive the request for
the REF, get the requested REF from the master table object and
return the REF to the calling method. For this example we have
two object tables, EMPLOYEES and JOB_SITES that are related as
is shown in figure 1.
Figure 1
As you can see from looking at
the OOERD in Figure 1, we have the two object tables, their main
types and a single method in each. The code to implement this
structure is shown below.
CREATE OR REPLACE TYPE address_t (
street VARCHAR2(30),
city VARCHAR2(30),
state CHAR(2),
zip CHAR(5))
/
CREATE OR REPLACE TYPE emp_t AS OBJECT (
employee_id INTEGER ,
name name_t,
birthdate DATE,
hiredate DATE,
address address_t,
dependents dependent_list,
MEMBER FUNCTION give_ref(id IN integer)
RETURN REF emp_t,
PRAGMA RESTRICT_REFERENCES (give_ref,WNPS)
)
/
rem
rem Now create the object employees which contains
rem nested tables, types and normal attributes
rem
CREATE TABLE employees OF emp_t
OIDINDEX oid_employees (TABLESPACE raw_INDEX)
NESTED TABLE dependents STORE AS dependentsv8a
PCTFREE 10 PCTUSED 80
INITRANS 5 MAXTRANS 255
TABLESPACE raw_data
STORAGE (
INITIAL 20m NEXT 10m
MINEXTENTS 1 MAXEXTENTS 10
PCTINCREASE 0
)
/
ALTER TABLE employees ADD
CONSTRAINT PK_employees
PRIMARY KEY (employee_id)
USING INDEX
PCTFREE 20
INITRANS 5
MAXTRANS 255
TABLESPACE raw_index
STORAGE (
INITIAL 10m NEXT 10m
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 0 FREELISTS 5
)
/
ALTER TABLE employees MODIFY
name NOT NULL
/
CREATE OR REPLACE TYPE BODY emp_t IS
MEMBER FUNCTION give_ref(id IN integer)
RETURN REF emp_t IS
ref_val REF emp_t;
BEGIN
SELECT REF(e) INTO ref_val
FROM employees e
WHERE e.employee_id=id;
RETURN ref_val;
END;
END;
/
CREATE or replace TYPE company_t (
company_name VARCHAR2(32),
company_business VARCHAR2(32),
company_contact name_t,
contact_home VARCHAR2(20))
/
rem
rem The job_sites object is an independent one-to-many
rem from employees. We will REF emp_t and EMPLOYEES
rem
CREATE OR REPLACE TYPE site_t as object (
site_id INTEGER,
company company_t,
address address_t,
employee_id_r REF emp_t,
MEMBER FUNCTION get_ref
(emp_id IN integer)
RETURN REF emp_t)
/
rem
rem job_sites is an object table definition
rem
CREATE TABLE job_sites OF site_t
(employee_id_r WITH ROWID
SCOPE IS employees)
OIDINDEX oid_job_sites (TABLESPACE raw_index)
PCTFREE 10 PCTUSED 80
INITRANS 5 MAXTRANS 255
TABLESPACE raw_data
STORAGE ( INITIAL 20m NEXT 10m
MINEXTENTS 1 MAXEXTENTS 10
PCTINCREASE 0
)
/
ALTER TABLE job_sites ADD
CONSTRAINT PK_job_sites
PRIMARY KEY (site_id)
USING INDEX
PCTFREE 20
INITRANS 5 MAXTRANS 255
TABLESPACE raw_index
STORAGE (INITIAL 10m NEXT 10m
MINEXTENTS 1 MAXEXTENTS 121
PCTINCREASE 0 FREELISTS 5
)
/
CREATE TYPE BODY site_t AS
MEMBER FUNCTION get_ref (emp_id IN integer)
RETURN INTEGER IS
status INTEGER:=0;
BEGIN
SELF.employee_id_r := emp_t.give_ref(emp_id);
RETURN status;
EXCEPTION
WHEN others THEN
status := 1;
RETURN status;
END;
END;
/
As you can see the actual
methods, get_ref and give_ref are quite small and
non-complex. To use the methods they are called like normal
functions during insert or update operations involving the REFed
row in the employees table object or the actual stored REF value
in the job_sites table object.
Overloading of Methods
Method overloading could be used
to provide a common name for a method that would actually be
several methods that performed differently dependent upon the
values passed as messages to the method. An example of this type
of method would be an update method that updates a table
attribute based on the value it is passed. If it were passed a
date value it would do the conversion to character, same with a
number. Or perhaps it would insert into a "TYPE" attribute, a
code based on input value.
The technique for overloading a
method procedure or function is identical to the overloading of
standard procedure or function.
create type test3(type_passed varchar2(8),
member procedure input_type(in_char char),
member procedure input_type(in_varchar varchar2),
member procedure input_type(in_date date),
member procedure input_type(in_number number))
/
This method overloading allows
incorporation of polymorphism into object types.
Method Design Concepts
It is clear that moving
procedures out of the application programs and into the database
engine is a significant benefit of Oracle's object-relational
methodology. Not only does this provide a more secure repository
for the code, but it encourages the concept of code reuse. If
you combine these aspects with the ability to represent
aggregate objects, then you have an extremely robust framework
for dealing with complex real world application designs. From a
pragmatic point of view, the following reasons are at the heart
of any complete method design effort:
- Code Reusability
--Process
code only needs to be written once. Cleanly debugged and
generated program units can be used in other modules and
applications.
- Configuration Management
--Storing
code in a central location in a common format, namely, the
database, supports rapid code retrieval and simplifies the
maintenance task. The database can also be used for tasks
like code searches and code backup.
- Proactive Tuning
--Because
the SQL is stored in the database, DBAs and developers can
extract and test the methods used in applications.
Information garnered from these tests can be used to
pinpoint areas where indexes are needed, identify tables
that can benefit from caching in the buffer pool, and other
performance tuning techniques.
- Application Portability
--Because
code is resident in the database, it (the code) is immune
from platform changes like operating system changes or
multiplatform execution. Portability from platform to
platform is simplified because the executable code will not
require recompilation.
- Process Cross-Referencing
--Documentation
of a method's use in an application is simplified through
the use of a data dictionary. Method calls can be examined
to identify data and program dependencies. Maintenance is
also simplified because all applications that reference a
particular entity can be identified easily.
Designing a Method Network (Hierarchy)
The concept of developing a
method network or hierarchy can be compared to planning a
hierarchy of user defined data types. The principle is
essentially the same, except that the focus of the hierarchy is
on the various levels of process functionality rather than data
types. However, remember that while methods may be mapped as a
hierarchy, in reality they have a many-to-many relationship to
other methods, and a method may have many sub-methods, while at
the same time participating as a method in many super-methods.
There are three types of processes to consider during the design
phase that will impact the method hierarchy. The three types
are:
- Processes independent of
database objects.
- Processes attached to
base-level database objects.
- Processes attached to
aggregate database objects.
You now have the ability to nest
methods one inside the other. Just as data can have sub-elements
and at the same time be a part of a larger data element,
behaviors can be composed of sub-behaviors, while at the same
time being a sub-behavior in a larger method. It is advisable to
develop a series of steps to accomplish this effort of mapping
data to methods. The following steps provide a good starting
point for doing so.
- Create and refine the
following essential analysis and design documents:
- A complete set of fully
decomposed data flow diagrams. This becomes the starting
point for all the methods that will be generated. The
data flow diagrams assist in determining method names,
input and output values, and a preliminary plan for
nested methods.
- A detailed
object-relational element diagram (an entity
relationship diagram in the relational paradigm) used
for identifying the base classes in the application
system.
- A diagram showing the
aggregate objects that will be used to associate the
higher-level processes with the appropriate methods
- Create a prototype for each
process identified in the data flow diagram in order to
define input and output requirements as well as to track
method dependencies.
- Identify any
standalone program units in the system, and trace their
relationships to other program units and data objects.
- Perform an initial
mapping of method prototypes to data elements.
There are two ways to proceed
once the listed steps have been performed. A top-down or a
bottom-up strategy can be implemented. The disadvantage of the
latter is that it is difficult to ascertain early in the effort
which atomic-level program units are needed to fulfill the
project requirements. The top-down approach is effective because
only those processes required to meet the business objectives
will be targeted. As more facts surrounding the relationship
between the methods are uncovered, more of the sub-methods will
be moved to different parts of the network. As we stated
earlier, the effort starts with the data flow diagram or
functional model. The data dictionary is used in conjunction
with the data flow diagram to associate the atomic-level
attributes participating in the data process flow. At this
stage, processes have a much broader scope, and it is fully
understood that further decomposition will yield further
sub-processes.
As you progress from one level of
detail to the next, you should find the sub-processes required
to perform the overall tasks for which they are assigned. At
each level of analysis, you should perform an evaluation to
identify duplicate process behaviors under different
higher-level processes.
At this stage of analysis,
duplicated processes are not eliminated or consolidated because
further analysis at greater granularity might reveal subtle
differences in the processes after all. What is done is that the
two (or more) processes are labeled to identify their system
duplicity and the analysis proceeds. Ultimately, you'll want to
proceed until decomposition is no longer possible. At that
point, the effort turns to organizing the defined processes. In
which case, you start by grouping the processes into the three
categories named earlier: independent, base level, and
aggregate.
The next step is to revisit the
duplicated processes and consolidate them. This is not to say
that you will create one big package and put redundant
sub-processes together. Instead, the effort is focused on
organization. Later, redundant processes will be grouped
categorically by function. These steps have proven to be a most
effective means of code organization in terms of maintenance and
deployment.
Once categorization is complete,
the groundwork for the method hierarchy will be in place.
The benefits of developing a
method network are threefold.
- It serves to provide
much-needed information regarding method interdependency
and, toward that end, provides the stepping stone to
effective documentation of the system being built.
- It provides the mechanism
for decomposing processes so that more generic and useful
atomic level sub-processes can be created, thus reducing the
brunt of coding activity in the long run because reuse is
encouraged.
- The process of creating a
method network serves to reduce the confusion level that is
sure to ensue when there is a propensity of code embedded in
the database in the form of coupled behavior with data
elements.
Conclusion
Methods are a powerful new
tool in the Oracle developer’s arsenal. For the first time,
database applications can more closely mirror the functionality
of object-oriented programming by fusing data with behavior. The
one aspect of modeling database applications in the real world
that confounded developers was the ability to adequately combine
the behavior (business rule) of an element with the data itself.
The encapsulation of data with its associated behavior, as is
commonly done in the object-oriented paradigm, is key to the
effective modeling and implementation of complex systems. The
exciting new object extensions in Oracle bring this sorely
needed functionality to the object-relational paradigm. Along
with this exciting new functionality, however, comes added
complexity in planning and design.
