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; /
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.