Object-oriented Development with PL-SQL

Object-oriented Development with PL/SQL
by Donald J. Bales
(630) 776-0071
don@donaldbales.com
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.

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

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 varchar2 ) is
begin
SYS.DBMS_OUTPUT.put_line(aiv_text);
end pl;
/
show errors procedure pl

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 (
...
idid 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.
*/
self in out nocopy base_,
idid number)

Constructor Body Examples
create or replace TYPE BODY base_ as
...
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!
*/
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
...
/*
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
...
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
...
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
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
...
/*
Text-based help for this package. "set serveroutput on" in SQL*Plus.
*/
STATIC PROCEDURE help,

Static Method Body Example
...
STATIC PROCEDURE help is
begin
pl('base_: MEMBER PROCEDURE help()');
pl('No help at this time.');
end help;

PL/SQL Variable Declaration
-- Using an object’s type name
declare
o_person personperson;
begin
...
end;
/
-- Using a object table’s row type
declare
o_person persons%ROWTYPEpersons%ROWTYPE;
begin
...
end;
/
 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

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 := new person_position();o_person_position := new person_position();
o_person_position.id := o_person_position.get_id();
o_person_position.active := to_date('20000101', 'YYYYMMDD');
o_person_position.inactive := to_date('20011231', 'YYYYMMDD');
o_person_position.person_id := o_person.id;
o_person_position.position_id := o_position.get_id('CEO');
...
end;
/
declare
o_person person;
begin
select value(p) into o_personvalue(p) into o_person from PERSONS p where p.name() = 'Bales, Donald J';
end;
/

Accessing Object Attributes and Methods
 You access an object’s attributes and methods using dot notation
declare
begin
...
o_person_position := new person_position();:= new person_position();
o_person_position.id.id := o_person_position.get_id().get_id();
o_person_position.active.active := to_date('20000101', 'YYYYMMDD');
o_person_position.inactive.inactive := to_date('20011231', 'YYYYMMDD');
o_person_position.person_id.person_id := o_person.id.id;
o_person_position.position_id.position_id := o_position.get_id('CEO').get_id('CEO');
...
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_gendero_gender := new gendernew 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!Since code is defined in code_ it''s accessible!');
pl(o_code.code);
end;
/

Downcasting
declare
o_base base_;
o_code code_;
o_gender gender;
begin
pl('test downcast');
begin
o_gendero_gender := new gendernew 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 upcastcode is not defined in base_, so we use TREAT to upcast ');
pl('it back to a code_, where code is accessibleit back to a code_, where code is accessible ');
pl(TREAT(o_baseo_base as code_code_).code);
end;
/

Upcasting
exec pl('This won''t even compile!This won''t even compile!');
declare
o_base base_;
o_code code_;
o_gender gender;
begin
pl('test upcast');
begin
o_baseo_base := new 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
declare
o_base base_;
o_code code_;
o_gender gender;
begin
pl('test upcast');
begin
o_baseo_base := new base_new base_(1);
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('This compiles, but causes a runtime exception:');
pl(SQLERRM);
end;
pl('So you can''t treat a supertype as a subtypeyou can''t treat a supertype as a subtype, because it''s not!');
end;
/

Upcasting
declare
o_base base_;
o_code code_;
o_gender gender;
begin
pl('test upcast');
begin
o_baseo_base := new gendernew 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!A subtype can be stored in a supertype variable!');
end;
/

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 gendergender under code_ (
...
) not final;
/
show errors type gender;
-- The corresponding object table
create table GENDERS
of gendergender (
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? Type
------------------------------- -------- ----------------------
ID NOT NULL NUMBER
ACTIVE DATE
INACTIVE DATE
CODE VARCHAR2(30)
DESCRIPTION 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? Type
------------------------------- -------- ----------------------
ID NUMBER
ACTIVE DATE
INACTIVE DATE
CODE VARCHAR2(30)
DESCRIPTION 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 FUNCTION GET_ID RETURNS NUMBER
STATIC PROCEDURE HELP
MEMBER PROCEDURE SAVE
METHOD
------
MEMBER PROCEDURE GET_CODE_DESCRIPTION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
AIN_ID NUMBER IN
AOV_CODE VARCHAR2 OUT
AOV_DESCRIPTION VARCHAR2 OUT

METHOD
------
MEMBER PROCEDURE GET_CODE_ID_DESCRIPTION
------------------------------ ----------------------- ------ --------
AIOV_CODE VARCHAR2 IN/OUT
AON_ID NUMBER OUT
AID_ON DATE IN
METHOD
------
MEMBER PROCEDURE GET_CODE_ID_DESCRIPTION
------------------------------ ----------------------- ------ --------
AIOV_CODE VARCHAR2 IN/OUT
AON_ID NUMBER OUT
METHOD
------
MEMBER FUNCTION GET_ID RETURNS NUMBER
------------------------------ ----------------------- ------ --------
AIV_CODE VARCHAR2 IN

METHOD
------
MAP MEMBER FUNCTION TO_VARCHAR2 RETURNS VARCHAR2
METHOD
------
FINAL CONSTRUCTOR FUNCTION GENDER RETURNS SELF AS RESULT
METHOD
------
------------------------------ ----------------------- ------ --------
ID NUMBER IN
ACTIVE DATE IN
INACTIVE DATE IN
CODE VARCHAR2 IN
DESCRIPTION VARCHAR2 IN
METHOD
------
------------------------------ ----------------------- ------ --------
CODE VARCHAR2 IN
DESCRIPTION VARCHAR2 IN

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(
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;
begin
select value(p)
into o_person
from PERSONS p
where p.name() = 'Bales, Donald J';
o_position := new position();
o_person_positiono_person_position := new person_position();
o_person_position.id := o_person_position.get_id();
o_person_position.active := to_date('20000101', 'YYYYMMDD');
o_person_position.inactive := to_date('20011231', 'YYYYMMDD');
o_person_position.person_id := o_person.id;
o_person_position.position_id := o_position.get_id('CEO');
insert into PERSON_POSITIONS values ( o_person_positiono_person_position );
commit;
end;
/

UPDATE table_name SET attribute_name = …
declare
begin
update POSITIONS
set descriptiondescription = 'One Who Is Peed On'
where code = 'PEON';
commit;
end;
/

UPDATE table_name alias SET VALUE(alias)…
declare
o_position position;
begin
select value(p)
into o_position
from POSITIONS p
o_position.description := 'A Pea On Top Of Something';
update POSITIONS p
set value(p)value(p) = o_position
where code = o_position.code;
commit;
end;
/

DELETE table_name WHERE attribute_name …
declare
begin
delete POSITIONS
end;
/
select * from positions
/
rollback
/

DELETE table_name alias WHERE REF(alias) =
declare
r_positionr_position ref positionref position;
begin
select ref(p)ref(p)
into r_positionr_position
from POSITIONS p
delete POSITIONS p
where ref(p)ref(p) = r_positionr_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) positionvalue(p) position
from POSITIONS p
order by code;
begin
for r_positions in c_positions loop
pl(r_positions.positionposition.code||' '||r_positions.positionposition.description);
end loop;
end;
/
CEO Chief Executive Officer
CFO Chief Financial Officer
CIO Chief Information Officer
PEON A Pea On Top Of Something

LeveragingPolymorphism, 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_
GENDER
PERSON_POSITION
POSITION

BASE_
id number,
self in out nocopy base_)
self in out nocopy base_,
id number)
MEMBER FUNCTION sequence_name
return varchar2,
MEMBER FUNCTION table_name
return varchar2,
MEMBER FUNCTION type_name
return varchar2,
return number,
MEMBER PROCEDURE save,
return varchar2,
STATIC PROCEDURE help
) not final;
/
show errors type base_;

HISTORICAL_
create or replace TYPE historical_ UNDERUNDER base_base_ (
active date,
inactive date,
CONSTRUCTOR FUNCTION historical_(
self in out nocopy historical_)
CONSTRUCTOR FUNCTION historical_(
self in out nocopy historical_,
id number,
active date,
inactive date)
OVERRIDING MAP MEMBER FUNCTION to_varchar2
return varchar2,
) not final;
/
show errors type historical_;

CODE_
create or replace TYPE code_ UNDER historical_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_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)
) not final;
/
show errors type gender;
and POSITION too…
create or replace TYPE position UNDER code_UNDER code_ (
...

PERSON – A content type
create or replace TYPE person UNDER base_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,
) not final;
/
show errors type person;

PERSON_POSITION – A relationship type
create or replace TYPE person_position UNDER historical_UNDER historical_ (
person_id number,
position_id number,
CONSTRUCTOR FUNCTION person_position(
self in out nocopy person_position)
) 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 positionposition (
constraint POSITIONS_PK
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 personperson (
constraint PERSONS_PK
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_positionperson_position (
constraint PERSON_POSITIONS_PK
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 SYS.ANYDATA;
begin
pl('base_: MEMBER FUNCTION type_name()');
a_self := SYS.ANYDATA.ConvertObject(SELFSELF);
v_type_name := a_self.GetTypeName()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);
begin
pl('base_: MEMBER PROCEDURE save()');
v_table_name := self.table_name();
v_type_name := self.type_name();
execute immediate '
update '||v_table_namev_table_name||' t
set value(t)value(t) = treat(treat(:self asas '||v_type_namev_type_name||')
where id = :id'
using self,
self.id;
if nvl(sql%rowcount, 0) = 0 then
execute immediate '
insert into '||v_table_namev_table_name||' t
values ( treat(treat(:self asas '||v_type_namev_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_This means that types GENDER and POSITION that descend from CODE_
have all this functionality. They can create, save, and retrieve themselves!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()
 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…
MEMBER FUNCTION positions
return arrayarray is
o_array arrayo_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_arraybulk 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;

Test Unit @test_person_dot_positions3.sql
 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
declare
o_position position := new position();
o_person person;
o_person_positions arrayo_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()o_person_positions := o_person.positions();
for n_index in o_person_positions.first..o_person_positions.last loop
o_person_position := treat(treat(o_person_positions(n_index) as person_position);as person_position);
pl(treat(o_position.get(o_person_position.position_id) as position).description);
end loop;
end;
/

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()
base_: MEMBER FUNCTION table_name()
A Pea On Top Of Something
Chief Information Officer
Chief Financial Officer
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

Object-oriented Development with PL-SQL

More Related Content

Similar to Object-oriented Development with PL-SQL

Object-oriented Development with PL-SQL