Thursday, June 30, 2005

OOP in PL/SQL? Yep

I was recently speaking with someone who was stunned to find out that object-oriented programming is available in PL/SQL (as of version 9, basically).

I guess I was stunned he didn't know this, but then again, I guess there isn't much fanfare about it.

Inheritance? Yep.
Polymorphism? Yep.
Encapsulation? Yep.

I gave him this basic, bare-bones template. It isn't much, but it can get someone started.

CREATE OR REPLACE TYPE some_object AS OBJECT (

some_variable NUMBER(10),

MEMBER FUNCTION member_function RETURN NUMBER,
MEMBER FUNCTION member_function (l_overloading IN NUMBER) RETURN NUMBER,
MEMBER PROCEDURE member_procedure,

-- Static functions can be used with an instance of the object
-- They can NOT reference any other non-static member functions or variables
STATIC FUNCTION static_function (l_value IN NUMBER DEFAULT 1) RETURN NUMBER,

-- Constructors must return self. Constructors are optional
CONSTRUCTOR FUNCTION some_object (some_variable NUMBER) RETURN SELF AS RESULT,

-- Used for comparison purposes: GROUP BY, ORDER BY, DISTINCT
-- No parameters allowed, returns NUMBER, DATE, VARCHAR2, CHAR or REAL
MAP MEMBER FUNCTION map_member_function RETURN NUMBER

-- ORDER takes one parameter of same type, and returns NUMBER
-- You may only have EITHER MAP OR ORDER
-- ORDER MEMBER FUNCTION order_member_function (some_other_object IN some_object) RETURN NUMBER

)
INSTANTIABLE -- Or "NOT INSTANTIABLE" if this is a base class only
NOT FINAL -- Or "FINAL" if this class will NOT have a sub-class
;

CREATE OR REPLACE TYPE composition_object AS OBJECT (

composed_object some_object

);

CREATE OR REPLACE TYPE derived_object
UNDER some_object (

OVERRIDING MEMBER PROCEDURE member_procedure

);

CREATE OR REPLACE TYPE BODY some_object AS

MEMBER FUNCTION member_function RETURN NUMBER
IS
BEGIN
-- The "SELF" isn't necessary, but is always available by default in member functions
RETURN SELF.some_variable;
END member_function;

MEMBER FUNCTION member_function (l_overloading IN NUMBER) RETURN NUMBER
IS
BEGIN
RETURN l_overloading;
END member_function;

MEMBER PROCEDURE member_procedure
IS
BEGIN
NULL;
END member_procedure;

-- Note: Unlike with packages, no private functions or declarations are allowed.
-- MEMBER FUNCTION hidden_proc RETURN NUMBER ...

-- Remember, static functions can't access SELF variables
STATIC FUNCTION static_function (l_value IN NUMBER DEFAULT 1) RETURN NUMBER
IS
BEGIN
RETURN l_value;
END static_function;

CONSTRUCTOR FUNCTION some_object (some_variable NUMBER) RETURN SELF AS RESULT
AS
BEGIN
SELF.some_variable := some_variable;

-- It will automatically return self, don't even try to return anything else
RETURN;

END some_object;

MAP MEMBER FUNCTION map_member_function RETURN NUMBER IS
BEGIN
RETURN SELF.some_variable;
END map_member_function;

-- ORDER MEMBER FUNCTION order_member_function (some_other_object IN some_object) RETURN NUMBER IS
-- BEGIN
-- IF some_other_object.some_variable < SELF.some_variable THEN RETURN 1;
-- ELSIF some_other_object.some_variable > SELF.some_variable THEN RETURN -1;
-- ELSE RETURN 0;
-- END IF;
-- END order_member_function;

END;

CREATE OR REPLACE TYPE BODY derived_object AS

OVERRIDING MEMBER PROCEDURE member_procedure
IS
BEGIN
NULL;
END member_procedure;

END;


-- Test!
DECLARE
-- You MUST instantiate it to use it. A NULL object is hard to use.
my_some_object some_object := some_object(0);
my_composition_object composition_object := composition_object(my_some_object);
my_number NUMBER;
BEGIN
my_number := my_composition_object.composed_object.member_function;
my_number := some_object.static_function(my_number);
END;

Comments:
Coming from real OOP programming languages world, this OOP in PL/SQL looks kinda ugly, don't you think?
 
Encapsulation: NO
Where is the scope definition, such as "public member function" or "private member function"?
 
That depends on your definition of "real OOP programming languages" and "ugly."

I bet most of the former looked like the latter to everyone before they became proficient at them.

And you want encapsulation in PL/SQL? Here you go.
 
Hi, I found your entry very helpful. I'm a little stuck though, and I was hoping maybe you can give me some hints...

Using your example, I have some_object. I have derived_object_a and derived_object_b, each with different additional attributes. For 95% of what I am doing, I can treat these things two objects the same, however on a few occasions, I need to access the derived_object's attributes.

So I have a procedure, let's say... foo( o in some_object ), but inside foo, I need to access o as if it were derived_object_a so that I might access its attributes.

Is there a way to cast o as derived_object in PL/SQL? Or is this just a design flaw in my code?

I've tried using TREAT, but it looks like it is meant to only work in SQL (it runs in PL/SQL when I try, but gives me the dreaded ORA 600 in my database log).

I'm using 10g, by the way.
 
I think what bachew meant is that he wants encapsulation along with polymorphism, inheritance, etc. all in the objets.

Packages have encapsulation but not other OO properties. Objects don't give you encapsulation.

Not that you can't do your job with PL/SQL. But if you're used to real OO languages, PL/SQL is awkward.
 
There is another interesting article about OOP in Pl/SQL:
http://plsql-object-types.blogspot.com/
 
Actually even in 11g OOP in PL/SQL has a long way left to go... try the following:

create a virtual superclass A,
derive class B from this superclass,

then create a pl/sql table type using class A

type table_type is table of A
A_table table_type:= table_type()

create an instance of class B

B_obj B := new B();

then extend the A_table, so it holds the place for an Object.

then try to insert the B_obj into the A_table. Guess what, i comes with an error message telling, that B_obj is of wrong type.

Since, B is derived from A, every B object can be put in any container that would take A. That's what we call polymorphism.

In pl/sql it's not working in general.

So, it's nice to use the provided O-based features oracle provides, but calling it really OO is far to much. Oracle might be used in an object-based manner, but it's not OO as we know it from Java or C#.
 
Anonymous, your assertion is wrong. The code below works fine on 10g or 11g.

create or replace type abstract_type as object (
obj_id varchar2(80)
)
not final;
/

create or replace type concrete_type UNDER abstract_type (
obj_value number
);
/

declare
type abstract_type_table is table of abstract_type;
mytable abstract_type_table := abstract_type_table();
myvar concrete_type := concrete_type( 'my object', 12345 );
begin
mytable.extend;
mytable(1) := myvar;
for i in mytable.first .. mytable.last
loop
dbms_output.put_line( mytable(i).obj_id );
end loop;
end;
/
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?