Object-oriented Development with PL/SQL
by Donald J. Bales
(630) 776-0071
[email protected]
http://www.pl-sql.org
http://www.donaldbales.com
http://www.facebook.com/donald.j.bales
http://www.linkedin.com/in/donaldbales
Please buy these, I’d need a vacation!
Audience Assessment







Who has had some experience with object-orientation?
Who has done some object-oriented programming?
Smalltalk?
C++?
Java?
Ruby?
PL/SQL?




Who still uses CREATE PROCEDURE…?
Who still uses CREATE FUNCTION…?
Who has used CREATE TYPE… AS OBJECT?
Who has used CREATE TABLE … OF…

Who knows some Sixties-speak?
“The obvious is always illusive”




Let’s start by defining a TYPE (SQL programming)
Next, code it’s implementation (if it needs one) (PL/SQL programming)
Now that you’ve got a type, you probably want a table of that TYPE to store the
type in. Right? So create an object table. (SQL programming)
Hey man. Got table? Time to create some really cool objects (TYPES) and then put
them away, change ‘em, whack ‘m. Whatever.
Object-orientation
Why use object-orientation?








The real world is object-oriented
“The closer the abstraction of a solution is to the
real world, the better the solution”
Consistency
Quality
A smaller code base means less maintenance
Programmer efficiency (i.e. reuse)
Did I mention reuse?
Inheritance v. code generation
What is object-orientation?

“a usually general or lasting direction of thought, inclination, or interest” about
“something mental or physical toward which thought, feeling, or action is
directed” (W)

Reality man.

Object-oriented Development:
– Object-oriented Analysis: abstracting reality into a simplified model
– Object-oriented Design: mimicking nature
– Object-oriented Programming: mimicking nature using software
• Polymorphism
• Inheritance
• Encapsulation
Polymorphism


How to change a pirate’s parrot into a computer program? No.
Using the same word with the same meaning in varying contexts



It’s about substitute-ability
Use generalized invocation to cast upward, aka widdenning
Use TREAT(super-type AS sub-type) to cast downward, aka narrowing
Encapsulation


Capsulation for N? Any ideas? No?
Hiding how the same word with its same meaning are implemented in varying
contexts

All type specification attributes are public -- just like a table (or package
specification)
All type specification methods are public -- just like a package specification
Methods only declared in a type body are private -- just like a package body


Inheritance
CREATE TYPE subtype_name UNDER supertype_name











Why we blame our parents for all our troubles? No.
A means of using the same word with its same meaning in varying contexts
A means of inheriting (re-using) the implementation of the same word with its same meaning
in varying contexts
A means of building more complex things out of less complex things
Type have single inheritance: no Mom and Dad, just Mom
A parent type is called a super-type: because children always start out thinking their parents
are super
A child type is called a sub-type: because parents (or a parent in this case) start out taller
than their children and their for look down on them
Sub-types inherit all of their super-type's attributes and methods, even when changes are
made to a super-type after a sub-type is created. I’d like to see my Mom do that!
In a sub-type, you can: add new attributes, add new methods, and override a super-type's
member method. (ah-hem) Over-ridding a method is different from overloading a method.
Types are FINAL by default, so specify NOT FINAL unless you're sure you'll never inherit from
one. Why? Because you can only alter a type from NOT FINAL to FINAL if the target type has
no subtypes.
Types are INSTANTIABLE by default, so specify NOT INSTANTIABLE if your are creating an
abstract type
Class
The Class of an object—it’s definition—or should I say TYPE?
“It’s all about the class, man.”






Most object-oriented programming languages refer to an object’s definition as its
Class
SQL (and accordingly PL/SQL) refers to an object’s definition as its TYPE
A TYPE has attributes and methods, which are analogous to columns in a table and
functions or procedures in a package
TYPEs are defined using CREATE TYPE type_name… AS OBJECT
TYPEs are implemented using CREATE TYPE BODY type_name AS…, that is, if they
have methods (functions or procedures)
A TYPE is sometimes referred to as an abstract data type (ADT) or a user-defined
data type, or just a user-defined type
CREATE TYPE type_name AS OBJECT (…)





A TYPE’s definition is like the cross between CREATE TABLE and CREATE PACKAGE
syntax
Start with attributes (like column names): name and type,
Continue with constructor, member, map [or order], and static methods
Everything specified in the TYPE specification is public
Add AUTHID CURRENT_USER to make the TYPE re-useable in any schema
create or replace TYPE ancestor as object (
id
number,
code
varchar2(30),
desription
varchar2(500),
active_date
date,
inactive_date
date,
MEMBER PROCEDURE member_procedure,
STATIC PROCEDURE static_procedure
) not final;
/
show errors type ancestor;
CREATE TYPE BODY type_name AS


A TYPE’s BODY is like a PACKAGE BODY
Any function or procedure in the TYPE BODY that is not declared in the TYPE
specification is private
create or replace TYPE BODY ancestor as
MEMBER PROCEDURE member_procedure is
begin
pl(chr(10)||'ancestor.member_procedure called.');
end member_procedure;
STATIC PROCEDURE static_procedure is
begin
pl(chr(10)||'ancestor.static_procedure called.');
end static_procedure;
end;
/
show errors type body ancestor;
pl(aiv_text)?
“OK. So I’m lazy.”

A wrapper procedure for SYS.DBMS_OUTPUT.put_line(); for the lazy typist.
create or replace PROCEDURE pl(
aiv_text
in
begin
SYS.DBMS_OUTPUT.put_line(aiv_text);
end pl;
/
show errors procedure pl
varchar2 ) is
CREATE TYPE table_name AS TABLE OF type_name


A TABLE, or nested table, is an unbounded, possibly sparse, collection
Method table_name(), its constructor, is used to create an empty collection

It’s an array, man!
create TYPE ancestor_table AS TABLE OF ancestor;
/
show errors type ancestor_table;
CREATE TYPE varray_name AS VARRAY (#) OF type_name


A VARRAY is a bounded (fixed size) collection
Method table_name(), its constructor, is used to create an empty collection

Hey man, it’s an array too! So which one do I use?
create TYPE ancestor_varray AS VARRAY(3) OF ancestor;
/
show errors type ancestor_varray;
Attributes


A TYPE must have at least on attribute
An attribute consists of an attribute name, and a type (predefined or user-defined)
create or replace TYPE person UNDER base_ (
...
last_name
varchar2(100),
first_name
varchar2(100),
middle_initial
varchar2(2),
born
date,
gender_id
number,
Methods are Functions or Procedures

Methods can be of one of the following types:
–
–
–
–
–


Constructor
Member
Map Member
Order Member
Static
You’ll call these methods using an object variable (for member methods) and dot
notation or the object name (for static methods) and dot notation
No. You can’t call a method on a null object variable, because no object exists!
Constructor Methods
CONSTRUCTOR FUNCTION type_name(
SELF in out nocopy type_name,…)
return self as result







Used to initialize a new instance of a TYPE (object)
A default constructor is created with all attributes as parameters in declared order
An instance of an object knows about it-SELF
SELF is always the first parameter passed to the method whether it is declared or
not!
SELF does not use the NOCOPY hint by default, but you should
Always define a no attribute constructor for convenience
Always define an all attribute (user-defined) constructor for control (parameters
must be named exactly the same as the attributes)
Constructor Specification Examples
create or replace TYPE base_ as object (
...
id
number,
/*
A constructor for creating a new instance of type base_ with NULL
values.
*/
CONSTRUCTOR FUNCTION base_(
self
in out nocopy base_)
return
self as result deterministic,
/*
A constructor for creating a new instance of type base_ for insert.
*/
CONSTRUCTOR FUNCTION base_(
self
in out nocopy base_,
id
number)
return
self as result deterministic,
Constructor Body Examples
create or replace TYPE BODY base_ as
...
CONSTRUCTOR FUNCTION base_(
self
in out base_)
return
self as result deterministic is
begin
pl('base_: CONSTRUCTOR FUNCTION base_()');
return;
end base_;
/*
This function hides the default constructor,
so the signature must be exact, i.e. I can't
use ain_id as the argument name!
*/
CONSTRUCTOR FUNCTION base_(
self
in out base_,
id
number)
return
self as result deterministic is
begin
pl('base_: CONSTRUCTOR FUNCTION base_(id)');
self.id := id;
return;
end base_;
Member Methods
MEMBER FUNCTION method_name (
[self in out nocopy,] ...) return ...
MEMBER PROCEDURE method_name (
[self in out nocopy,] ...)






An instance of an object knows about it-SELF
SELF is always the first parameter passed to the method whether it is declared or
not!
Prefix with FINAL and it can’t be overridden
Prefix with NOT INSTANTIABLE and its TYPE becomes abstract, i.e. the TYPE can’t
be instantiated
Prefix with OVERRIDING in order to override an ancestor method’s
implementation—more about that to come…
“Redefining an inherited member method to customize its behavior in a subtype is
called overriding” (what’s new)
Member Method Specification Examples
create or replace TYPE base_ as object (
...
/*
Returns a new primary key id value for a row.
*/
MEMBER FUNCTION get_id
return
number,
/*
Saves the current object.
*/
MEMBER PROCEDURE save,
Member Method Body Examples
create or replace TYPE BODY base_ as
...
MEMBER FUNCTION get_id
return
number is
n_id
number;
begin
pl('base_: MEMBER FUNCTION get_id()');
execute immediate '
select '||sequence_name||'.nextval
from
SYS.DUAL'
into
n_id;
return n_id;
end get_id;
Member Method Body Examples continued
create or replace TYPE BODY base_ as
...
MEMBER PROCEDURE save is
v_table_name
varchar2(100);
v_type_name
varchar2(100);
begin
pl('base_: MEMBER PROCEDURE save()');
v_table_name := self.table_name();
v_type_name := self.type_name();
execute immediate '
update '||v_table_name||' t
set
value(t) = treat(:self as '||v_type_name||')
where id
= :id'
using self, self.id;
if nvl(sql%rowcount, 0) = 0 then
execute immediate '
insert into '||v_table_name||' t
values ( treat(:self as '||v_type_name||') )'
using self;
pl(nvl(sql%rowcount, 0)||' row(s) inserted.');
else
pl(nvl(sql%rowcount, 0)||' row(s) updated.');
end if;
end save;
Map Member Method
MAP MEMBER FUNCTION method_name (
[self in out nocopy,] ...) return ...




A special method (oooh, I’m impressed)
Returns a scalar value to be used to compare objects for ordering by DISTINCT,
GROUP BY, UNION, and ORDER BY, (or equality)
A subtype can declare a map method only if its root supertype declares one
I say, “be kind to yourself. Consistently use one map member function method
name for all objects!”
Map Member Method Example
-- The specification
MAP MEMBER FUNCTION to_varchar2
return
varchar2,
-- The body
MAP MEMBER FUNCTION to_varchar2
return
varchar2 is
begin
pl('base_: MAP MEMBER FUNCTION to_varchar2()');
-12345678901234567890123456789012345678
return ltrim(to_char(id, '00000000000000000000000000000000000009'));
end to_varchar2;
Order Member Method
ORDER MEMBER FUNCTION method_name (
[self in out nocopy,] ...) return INTEGER



Another special method (I’m more less impressed)
Returns -1, 0, or 1 in order to tell you that the declared parameter object is less
than, equal to, or greater than SELF
A subtype cannot define an order member function (OK. Then I’m not using it.)


Consistently use one order member function method name for all objects!
Or better yet, don’t use Order Member Methods at all (hah)
Order Member Method Example
“I don’t use ‘em man, you’re on your own.”
Static Methods
STATIC FUNCTION method_name (...) return ...
STATIC PROCEDURE method_name (...)




No SELF exists!
Invoked on the TYPE, not an instance of the type
Similar to a package method
“Redefining an inherited static method to customize its behavior in a subtype is
called hiding” (right.)

Honestly? These aren’t that useful, man.
Static Method Specification Example
create or replace TYPE base_ as object (
...
/*
Text-based help for this package. "set serveroutput on" in SQL*Plus.
*/
STATIC PROCEDURE help,
Static Method Body Example
create or replace TYPE base_ as object (
...
STATIC PROCEDURE help is
begin
pl('base_: MEMBER PROCEDURE help()');
pl('No help at this time.');
end help;
PL/SQL Variable Declaration

You can declare a PL/SQL variable for an object (an instance of a TYPE) using the
TYPE’s name, or an object table’s row type
-- Using an object’s type name
declare
o_person person;
begin
...
end;
/
-- Using a object table’s row type
declare
o_person persons%ROWTYPE;
begin
...
end;
/
PL/SQL Variable Assignment


You can assign a PL/SQL object variable a value just like any other PL/SQL variable
And you can assign a PL/SQL object variable using SQL functions VALUE(alias) and
DEREF(ref)
declare
o_person_position person_position;
begin
...
o_person_position
:=
o_person_position.id
:=
o_person_position.active
:=
o_person_position.inactive
:=
o_person_position.person_id
:=
o_person_position.position_id :=
...
end;
/
new person_position();
o_person_position.get_id();
to_date('20000101', 'YYYYMMDD');
to_date('20011231', 'YYYYMMDD');
o_person.id;
o_position.get_id('CEO');
declare
o_person person;
begin
select value(p) into o_person from PERSONS p where
end;
/
p.name() = 'Bales, Donald J';
Accessing Object Attributes and Methods

You access an object’s attributes and methods using dot notation
declare
o_person_position person_position;
begin
...
o_person_position
:=
o_person_position.id
:=
o_person_position.active
:=
o_person_position.inactive
:=
o_person_position.person_id
:=
o_person_position.position_id :=
...
end;
/
new person_position();
o_person_position.get_id();
to_date('20000101', 'YYYYMMDD');
to_date('20011231', 'YYYYMMDD');
o_person.id;
o_position.get_id('CEO');
Downcasting
gender under code_ under historical_ under base_
declare
o_base
base_;
o_historical historical_;
o_code
code_;
o_gender
gender;
begin
pl('test downcast');
begin
o_gender
:= new gender('Hi', 'Howdy');
o_code
:= o_gender;
o_historical := o_gender;
o_base
:= o_gender;
exception
when OTHERS then
pl(SQLERRM);
end;
pl('Since code is defined in code_ it''s accessible!');
pl(o_code.code);
end;
/
Downcasting
gender under code_ under historical_ under base_
declare
o_base
base_;
o_historical historical_;
o_code
code_;
o_gender
gender;
begin
pl('test downcast');
begin
o_gender
:= new gender('Hi', 'Howdy');
o_code
:= o_gender;
o_historical := o_gender;
o_base
:= o_gender;
exception
when OTHERS then
pl(SQLERRM);
end;
pl('code is not defined in base_, so we use TREAT to upcast ');
pl('it back to a code_, where code is accessible ');
pl(TREAT(o_base as code_).code);
end;
/
Upcasting
gender under code_ under historical_ under base_
exec pl('This won''t even compile!');
declare
o_base
base_;
o_historical historical_;
o_code
code_;
o_gender
gender;
begin
pl('test upcast');
begin
o_base
:= new base_(1);
o_historical := o_base;
o_code
:= o_base;
o_gender
:= o_base;
exception
when OTHERS then
pl(SQLERRM);
end;
pl('You''ll never make it here!');
end;
/
Upcasting
gender under code_ under historical_ under base_
declare
o_base
base_;
o_historical historical_;
o_code
code_;
o_gender
gender;
begin
pl('test upcast');
begin
o_base
:= new base_(1);
o_historical := TREAT(o_base as
o_code
:= TREAT(o_base as
o_gender
:= TREAT(o_base as
exception
when OTHERS then
pl('This compiles, but causes
pl(SQLERRM);
end;
pl('So you can''t treat a supertype
end;
/
historical_);
code_);
gender);
a runtime exception:');
as a subtype, because it''s not!');
Upcasting
gender under code_ under historical_ under base_
declare
o_base
base_;
o_historical historical_;
o_code
code_;
o_gender
gender;
begin
pl('test upcast');
begin
o_base
:= new gender(1, SYSDATE, SYSDATE + 36525, 'H', 'Hermaphrodite');
o_historical := TREAT(o_base as historical_);
o_code
:= TREAT(o_base as code_);
o_gender
:= TREAT(o_base as gender);
exception
when OTHERS then
pl(SQLERRM);
end;
pl('A subtype can be stored in a supertype variable!');
end;
/
Persistence
Object Tables
CREATE TABLE table_name OF type_name




A table with one column based on a TYPE
Can be treated as a relational table with primary and foreign keys
Or, treated as an object table with references
An object table can hold subtypes

Personally, all things considered, I use the relational model for compatibility with
presentation-layer technologies, but I think the reference implementation is better
Object Table Example
-- First, the type
create or replace TYPE gender under code_ (
...
) not final;
/
show errors type gender;
-- The corresponding object table
create table GENDERS
of
gender (
constraint
GENDERS_PK
primary key ( id ) )
object identifier is primary key;
create sequence GENDER_ID
start with 1;
alter table GENDERS add
constraint
GENDERS_UK
unique ( code )
using index;
Object Table Example continued
-- Now let’s describe the table
desc GENDERS
Name
Null?
------------------------------- -------ID
NOT NULL
ACTIVE
INACTIVE
CODE
DESCRIPTION
Type
---------------------NUMBER
DATE
DATE
VARCHAR2(30)
VARCHAR2(500)
-- Hey man, it looks like a table.
-- I wonder what the type looks like?
desc GENDER
GENDER extends BPS.CODE_
GENDER is NOT FINAL
Name
Null?
------------------------------- -------ID
ACTIVE
INACTIVE
CODE
DESCRIPTION
Type
---------------------NUMBER
DATE
DATE
VARCHAR2(30)
VARCHAR2(500)
METHOD
-----MEMBER FUNCTION SEQUENCE_NAME RETURNS VARCHAR2
METHOD
-----MEMBER FUNCTION TABLE_NAME RETURNS VARCHAR2
METHOD
-----MEMBER FUNCTION TYPE_NAME RETURNS VARCHAR2
METHOD
-----MEMBER
STATIC
MEMBER
STATIC
FUNCTION GET_ID RETURNS NUMBER
PROCEDURE HELP
PROCEDURE SAVE
PROCEDURE HELP
METHOD
-----MEMBER PROCEDURE GET_CODE_DESCRIPTION
Argument Name
Type
------------------------------ ----------------------AIN_ID
NUMBER
AOV_CODE
VARCHAR2
AOV_DESCRIPTION
VARCHAR2
In/Out Default?
------ -------IN
OUT
OUT
METHOD
-----MEMBER PROCEDURE GET_CODE_ID_DESCRIPTION
Argument Name
Type
------------------------------ ----------------------AIOV_CODE
VARCHAR2
AON_ID
NUMBER
AOV_DESCRIPTION
VARCHAR2
AID_ON
DATE
In/Out Default?
------ -------IN/OUT
OUT
OUT
IN
METHOD
-----MEMBER PROCEDURE GET_CODE_ID_DESCRIPTION
Argument Name
Type
------------------------------ ----------------------AIOV_CODE
VARCHAR2
AON_ID
NUMBER
AOV_DESCRIPTION
VARCHAR2
In/Out Default?
------ -------IN/OUT
OUT
OUT
METHOD
-----MEMBER FUNCTION GET_ID RETURNS
Argument Name
-----------------------------AIV_CODE
STATIC PROCEDURE HELP
NUMBER
Type
In/Out Default?
----------------------- ------ -------VARCHAR2
IN
METHOD
-----MAP MEMBER FUNCTION TO_VARCHAR2 RETURNS VARCHAR2
METHOD
-----FINAL CONSTRUCTOR FUNCTION GENDER RETURNS SELF AS RESULT
METHOD
-----FINAL CONSTRUCTOR FUNCTION GENDER RETURNS SELF AS RESULT
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ -------ID
NUMBER
IN
ACTIVE
DATE
IN
INACTIVE
DATE
IN
CODE
VARCHAR2
IN
DESCRIPTION
VARCHAR2
IN
METHOD
-----FINAL CONSTRUCTOR FUNCTION GENDER RETURNS SELF AS RESULT
Argument Name
Type
In/Out Default?
------------------------------ ----------------------- ------ -------CODE
VARCHAR2
IN
DESCRIPTION
VARCHAR2
IN
STATIC PROCEDURE HELP
“Holy cow!”
BTW, You Can Create Object Views too!





Yes, you can create constraints against the attributes of an object table (null-ability,
etc.) and against an object table (primary key, foreign key)
You can also create indexes against object attributes and any deterministic object
functions
You can turn relational tables into object views, i.e. “have you cake and eat it too.”
If you’re interested, suggest it as the topic of a future session
Or, buy my first book
Data Manipulation Language for objects in PL/SQL


INSERT using a constructor
INSERT using an instance of an object


UPDATE using attributes
UPDATE using an instance of an object


DELETE using attributes
DELETE using a reference to an object



SELECT using attributes
SELECT using value() to get an instance of an object
SELECT using ref() to get a reference to an instance of an object
New SQL Functions








VALUE(alias) – instantiates a copy of an object from an object table or object view
REF(alias) – gets a reference to an object an object table or object view
DEREF(ref) – given a reference to an, instantiates a copy of the object
TREAT(supertype AS subtype) – allows you to cast a super-type (parent type) back
to a sub-type (child type) if the object is actually an instance of the sub-type
IS OF TYPE(type) – allows you to check that a sub-type is of a super-type
Pseudo columns OBJECT_ID and OBJECT_VALUE
IS TABLE OF – allows you to check that a nested-table collection is of a particular
type
IS VARRAY(#) OF – allows you to check that a varray-table collection is of a
particular type has the same maximum number of elements
INSERT INTO table_name VALUES (constructor_name())
declare
o_male
gender;
begin
select value(g) into o_male from GENDERS g where code = 'M';
insert into PERSONS values (
PERSON(
PERSON_ID.nextval,
'Bales',
'Donald',
'J',
to_date('19580101', 'YYYYMMDD'),
o_male.id ) );
commit;
end;
/
INSERT INTO table_name VALUES ( instance_name )
declare
o_person
person;
o_position
position;
o_person_position person_position;
begin
select
into
from
where
value(p)
o_person
PERSONS p
p.name() = 'Bales, Donald J';
o_position
:= new position();
o_person_position
o_person_position.id
o_person_position.active
o_person_position.inactive
o_person_position.person_id
o_person_position.position_id
:=
:=
:=
:=
:=
:=
new person_position();
o_person_position.get_id();
to_date('20000101', 'YYYYMMDD');
to_date('20011231', 'YYYYMMDD');
o_person.id;
o_position.get_id('CEO');
insert into PERSON_POSITIONS values ( o_person_position );
commit;
end;
/
UPDATE table_name SET attribute_name = …
declare
begin
update POSITIONS
set
description = 'One Who Is Peed On'
where code
= 'PEON';
commit;
end;
/
UPDATE table_name alias SET VALUE(alias)…
declare
o_position position;
begin
select
into
from
where
value(p)
o_position
POSITIONS p
code = 'PEON';
o_position.description := 'A Pea On Top Of Something';
update POSITIONS p
set
value(p) = o_position
where code
= o_position.code;
commit;
end;
/
DELETE table_name WHERE attribute_name …
declare
begin
delete POSITIONS
where code = 'PEON';
end;
/
select * from positions
/
rollback
/
DELETE table_name alias WHERE REF(alias) =
declare
r_position ref position;
begin
select
into
from
where
ref(p)
r_position
POSITIONS p
code = 'PEON';
delete POSITIONS p
where ref(p) = r_position;
end;
/
select * from positions
/
rollback
/
SELECT attribute_name,… FROM table_name
declare
cursor c_positions is
select code,
description
from
POSITIONS
order by code;
begin
for r_positions in c_positions loop
pl(r_positions.code||' '||r_positions.description);
end loop;
end;
/
CEO Chief Executive Officer
CFO Chief Financial Officer
CIO Chief Information Officer
PEON A Pea On Top Of Something
SELECT VALUE(alias) FROM table_name alias…
declare
cursor c_positions is
select value(p) position
from
POSITIONS p
order by code;
begin
for r_positions in c_positions loop
pl(r_positions.position.code||' '||r_positions.position.description);
end loop;
end;
/
CEO Chief Executive Officer
CFO Chief Financial Officer
CIO Chief Information Officer
PEON A Pea On Top Of Something
Leveraging
Polymorphism, Inheritance, and Encapsulation
“Now that you basically know what a TYPE is, and
where you can save one, let’s talk through an
example.”

I’m going to show you:
– Lots of polymorphism
– Four levels of inheritance
– Lots of encapsulation

In the process, I’ll create:
–
–
–
–
–
Seven types
Two code object-tables
One content object-table
Create one historical relationship (many to many) object-table
And, how to leverage object-orientation
“In the beginning (there) was the word…”(John 1)
Type Hierarchy
BASE_
HISTORICAL_
PERSON
CODE_
PERSON_POSITION
GENDER
POSITION
BASE_
create or replace TYPE base_ as object (
id
number,
CONSTRUCTOR FUNCTION base_(
self
in out nocopy base_)
return
self as result deterministic,
CONSTRUCTOR FUNCTION base_(
self
in out nocopy base_,
id
number)
return
self as result deterministic,
MEMBER FUNCTION sequence_name
return
varchar2,
MEMBER FUNCTION table_name
return
varchar2,
MEMBER FUNCTION type_name
return
varchar2,
MEMBER FUNCTION get_id
return
number,
MEMBER PROCEDURE save,
MAP MEMBER FUNCTION to_varchar2
return
varchar2,
STATIC PROCEDURE help
) not final;
/
show errors type base_;
HISTORICAL_
create or replace TYPE historical_ UNDER base_ (
active
date,
inactive
date,
CONSTRUCTOR FUNCTION historical_(
self
in out nocopy historical_)
return
self as result deterministic,
CONSTRUCTOR FUNCTION historical_(
self
in out nocopy historical_,
id
number,
active
date,
inactive
date)
return
self as result deterministic,
OVERRIDING MAP MEMBER FUNCTION to_varchar2
return
varchar2,
STATIC PROCEDURE help
) not final;
/
show errors type historical_;
CODE_
create or replace TYPE code_ UNDER historical_ (
code
varchar2(30),
description
varchar2(500),
...
MEMBER PROCEDURE get_code_description(
ain_id
in
number,
aov_code
out varchar2,
aov_description
out varchar2),
MEMBER PROCEDURE get_code_id_description(
aiov_code
in out varchar2,
aon_id
out number,
aov_description
out varchar2,
aid_on
in
date),
MEMBER PROCEDURE get_code_id_description(
aiov_code
in out varchar2,
aon_id
out number,
aov_description
out varchar2),
MEMBER FUNCTION get_id(
aiv_code
in
varchar2)
return
number,
OVERRIDING MAP MEMBER FUNCTION to_varchar2
return
varchar2,
STATIC PROCEDURE help,
) not final;
/
show errors type code_;
GENDER – A code type
create or replace TYPE gender UNDER code_ (
...
/*
A constructor for creating a new instance of type gender for insert.
*/
CONSTRUCTOR FUNCTION gender(
self
in out nocopy gender,
code
varchar2,
description
varchar2)
return
self as result deterministic,
STATIC PROCEDURE help
) not final;
/
show errors type gender;
and POSITION too…
create or replace TYPE position UNDER code_ (
...
PERSON – A content type
create or replace TYPE person UNDER base_ (
last_name
varchar2(100),
first_name
varchar2(100),
middle_initial
varchar2(2),
born
date,
gender_id
number,
...
MEMBER FUNCTION name
return
varchar2 deterministic,
MEMBER FUNCTION age(
aid_on
date := SYSDATE)
return
number,
MEMBER FUNCTION positions
return
array,
STATIC PROCEDURE help
) not final;
/
show errors type person;
PERSON_POSITION – A relationship type
create or replace TYPE person_position UNDER historical_ (
person_id
number,
position_id
number,
CONSTRUCTOR FUNCTION person_position(
self
in out nocopy person_position)
return
self as result deterministic,
STATIC PROCEDURE help
) not final;
/
show errors type person_position;
Code tables GENDERS and POSITIONS
-- you already saw me create the GENDERS table, so here’s POSITIONS
create table POSITIONS
of
position (
constraint
POSITIONS_PK
primary key ( id ) )
object identifier is primary key;
create sequence POSITION_ID
start with 1;
alter table POSITIONS add
constraint
POSITIONS_UK
unique ( code )
using index;
Content table PERSONS
create table PERSONS
of
person (
constraint
PERSONS_PK
primary key ( id ) )
object identifier is primary key;
create sequence PERSON_ID
start with 1;
alter table PERSONS add
constraint
PERSONS_UK
unique ( last_name, first_name, middle_initial, born, gender_id )
using index;
Historical Relationship table PERSON_POSITIONS
create table PERSON_POSITIONS
of
person_position (
constraint
PERSON_POSITIONS_PK
primary key ( id ) )
object identifier is primary key;
create sequence PERSON_POSITION_ID
start with 1;
alter table PERSON_POSITIONS add
constraint
PERSON_POSITIONS_UK
unique ( person_id, active )
using index;
BASE_ - an object descended from it has…

id - By convention, every table should have a primary, id



type_name() - Find out its own type name
table_name() - Using convention, find its table name
sequence_name() - Using convention, find its sequence name, the one used for its
primary key values
get() – given an id, returns an instance of the corresponding object from its table
get_id() – Using convention, allocate a new sequence value for its primary key, id
save() – insert or update itself



Let’s look at type_name() and save()
BASE_.type_name()
MEMBER FUNCTION type_name
return
varchar2 is
a_self
v_type_name
SYS.ANYDATA;
varchar2(61);
begin
pl('base_: MEMBER FUNCTION type_name()');
a_self
:= SYS.ANYDATA.ConvertObject(SELF);
v_type_name := a_self.GetTypeName();
return substrb(v_type_name, instr(v_type_name, '.') + 1);
end type_name;


Uses ANYDATA to get the type’s name
Once an object knows what kind it is, kinds of cool functionality can be created
that is inheritable
BASE_.save()
MEMBER PROCEDURE save is
v_table_name
varchar2(100);
v_type_name
varchar2(100);
begin
pl('base_: MEMBER PROCEDURE save()');
v_table_name := self.table_name();
v_type_name := self.type_name();
execute immediate '
update '||v_table_name||' t
set
value(t) = treat(:self as '||v_type_name||')
where id
= :id'
using self,
self.id;
if nvl(sql%rowcount, 0) = 0 then
execute immediate '
insert into '||v_table_name||' t
values ( treat(:self as '||v_type_name||') )'
using self;
pl(nvl(sql%rowcount, 0)||' row(s) inserted.');
else
pl(nvl(sql%rowcount, 0)||' row(s) updated.');
end if;
end save;
HISTORICAL_ - an object descended from it has…

active and inactive dates – to historically keep track of the objects state
CODE_ - an object descended from it has…

code and description – to define a quickly entered and highly descriptive attributes


get_code_description() – given an id, retrieves both the code and description
get_code_id_description() – given a partial or complete code, retrieves the
complete code, id and description, for a given date
get_code_id_description() – as above, but overridden for the current date
get_id() – given a code, returns the corresponding id


This means that types GENDER and POSITION that descend from CODE_
have all this functionality. They can create, save, and retrieve themselves!
PERSON is descended from BASE_

Adds attributes: last_name, first_name, middle_initial, born, and gender_id


name() – returns a person’s full name in a standard format
age() – returns a person’s age on a given date, or if not date is specified, their
current age
positions() – returns a nested-table with the person’s position history

Let’s examine PERSON.positions()
PERSON.positions()
MEMBER FUNCTION positions
return
array is
o_array array;
begin
pl('person: MEMBER FUNCTION positions');
execute immediate '
select value(p)
from
PERSON_POSITIONS p
where p.person_id = :person_id
order by active'
bulk collect into o_array
using self.id;
pl('person: sql%rowcount='||sql%rowcount);
pl('o_array.count='||o_array.count);
return o_array;
end positions;


array is a nested-table type of base_
Accordingly, positions() returns a nested-table of person_position objects
masquerading as base_ objects
Let’s see how a consumer of this function deals with this…
Test Unit @test_person_dot_positions3.sql
declare
o_position
position := new position();
o_person
person;
o_person_position person_position;
o_person_positions array;
begin
select value(p) into o_person from PERSONS p where p.name() = 'Bales, Donald J';
o_person_positions := o_person.positions();
for n_index in o_person_positions.first..o_person_positions.last loop
o_person_position := treat(o_person_positions(n_index) as person_position);
pl(treat(o_position.get(o_person_position.position_id) as position).description);
end loop;
end;
/




array is defined as “create TYPE array as table of base_;”
Variable o_person_positions is declared as an array
It is assigned the return value from o_person.positions()
The person_position objects masquerading as base_ objects in nested-table type
array are then, in turn, treated as person_position objects
position: CONSTRUCTOR FUNCTION position()
person: MEMBER FUNCTION name()
person: MEMBER FUNCTION positions
person: sql%rowcount=4
o_array.count=4
base_: MEMBER FUNCTION get(ain_id)
base_: MEMBER FUNCTION type_name()
position: CONSTRUCTOR FUNCTION position()
base_: MEMBER FUNCTION table_name()
base_: MEMBER FUNCTION type_name()
A Pea On Top Of Something
base_: MEMBER FUNCTION get(ain_id)
base_: MEMBER FUNCTION type_name()
position: CONSTRUCTOR FUNCTION position()
base_: MEMBER FUNCTION table_name()
base_: MEMBER FUNCTION type_name()
Chief Information Officer
base_: MEMBER FUNCTION get(ain_id)
base_: MEMBER FUNCTION type_name()
position: CONSTRUCTOR FUNCTION position()
base_: MEMBER FUNCTION table_name()
base_: MEMBER FUNCTION type_name()
Chief Financial Officer
base_: MEMBER FUNCTION get(ain_id)
base_: MEMBER FUNCTION type_name()
position: CONSTRUCTOR FUNCTION position()
base_: MEMBER FUNCTION table_name()
base_: MEMBER FUNCTION type_name()
Chief Executive Officer
What I didn’t cover…







Using relational schema in an object-oriented fashion. I cover this nicely in Java
Programming with Oracle JDBC and Beginning PL/SQL: From Novice to Professional
Object views. I cover this nicely in Java Programming with Oracle JDBC and
Beginning PL/SQL: From Novice to Professional
Nested Tables – the use of large object graphs has proven to be a negative
performance issue, so nested tables are not practical yet. I cover this nicely in Java
Programming with Oracle JDBC
REFs – I presented a more relational implementation of object-relational. Using
references is a more object like implementation. I cover this nicely in Java
Programming with Oracle JDBC
Returning a cursor instead of a nested-table for lazy loading functions like
PERSON.positions()
Package DBMS_OBJECTS_UTILS
Package UTL_REF
Closing Thoughts





Objects (TYPEs) better model the real world, and hence provide a better solution
Well though out use inheritance can significantly reduce the amount of code to
write, the time it takes to write it, and the time it takes to maintain it
Using objects provides better consistency, in turn, better consistency provide
higher quality
In an object-relational setting Packages are better suited as role players that
orchestrate the use of objects
Or perhaps, those roles should be objects too?
References







Beginning PL/SQL: From Novice to Professional by Donald J. Bales (APress)
Java Programming with Oracle JDBC by Donald J. Bales (O'Reilly)
Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) (Oracle)
Oracle® Database SQL Language Reference 11g Release 2 (11.2) (Oracle)
Oracle® Database Object-Relational Developer's Guide 11g Release 2 (11.2) (Oracle)
Oracle PL/SQL Programming by By Steven Feuerstein, Bill Pribyl (O'Reilly)
Object-Oriented Technology: A Manager's Guide by David A. Taylor (Addison-Wesley)




http://www.pl-sql.org
http://technet.oracle.com
http://www.apress.com/book/catalog?category=148
http://oreilly.com/pub/topic/oracle
Descargar

Object-oriented Development with PL/SQL