I dived into object-oriented programming in PL/SQL a while ago due to utPLSQL and also used it several times for my normal work meanwhile.
This powerful feature of the PL/SQL language is highly underused and therefore I want to create some examples around it in hopes to show some of the advantages objects in PL/SQL have to offer.
Let’s assume we want to implement some behavior for force-sensitive beings, whose most prominent representatives are, of course, Jedi and Sith:
-- First create a base type
create or replace type force_sensitive force as object
(
-- First we define object-level attributes
c_name varchar2(100),
-- notice the comma instead of semicolon:
-- this is a definition, similar to table definition - not
-- an implemenntation.
-- define a function we are going to implement
member function name
return varchar2,
-- and one we are only going to implement
-- inside the children
not instantiable member function alignment
return varchar2
)
-- For this is our base type we dont want it to be
-- instantiable, e.g. no "new force_sensitive()" possible
not final not instantiable;
/
-- Now implement the body of the base type
create or replace type body force_sensitive as
member function name
return varchar2
as
begin
return c_name;
end;
end;
/
-- We want to have a real type now
create or replace type jedi under force_sensitive (
overriding member function alignment
return varchar2
);
/
create or replace type body jedi as
overriding member function alignment
return varchar2
as
begin
return 'light';
end;
end;
/
-- And another real type with a specialty
create or replace type sith under force_sensitive (
-- Default constructor has all member-attributes
-- as parameters and assignes them. We can change
-- this with our own constructor
constructor function sith( c_name in varchar2 )
return self as result,
overriding member function alignment
return varchar2
);
/
create or replace type body sith as
constructor function sith( c_name in varchar2 )
return self as result
is
begin
-- self is a special built-in parameter
-- which is passed to each member function as first
-- parameter, no matter if explicitly defined or not.
-- It holds an instance to the object
self.c_name := 'Darth ' || c_name;
-- however, in a constructor we dont return self
-- but just return.
return;
end;
overriding member function alignment
return varchar2 is
begin
return 'dark';
end;
end;
/
declare
l_luke force_sensitive;
l_vader force_sensitive;
-- We can here see one of the first gifts of plsql-
-- objects: We can build generalized methods which
-- work for any subtype
procedure output_alignment(
i_force_user in out nocopy force_sensitive )
as
begin
dbms_output.put_line(i_force_user.name() ||
' is aligned to ' || i_force_user.alignment());
end;
begin
-- Create a new instance of a concrete type
l_luke := new jedi('Luke Skywalker');
output_alignment(l_luke);
l_vader := new sith('Vader');
output_alignment(l_vader);
end;
/
Output:
Luke Skywalker is aligned to light
Darth Vader is aligned to dark
You can run this example on LiveSQL.
This is only the first of several examples about PL/SQL objects in that specific theme to come, so stay tuned.
Top comments (0)