OracleBlog

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;