I had two examples about PL/SQL object types in the past, but didn’t manage to showcase their practical use yet: For modern SQL is turing complete you can somehow solve every problem with SQL and it is even easier with the capabilities of procedural PL/SQL.
There are, however, situations where the usage of object types (SQL Types) can help you greatly and for they are available in Oracle database since version 8i (1999), they should be part of every database developer’s toolkit.
We just got the following, structured list of force powers, categorized by their main nature (Universal, Light and Dark) and skill dependencies:
To be able to master Force Pull, Push or Jump you first have to master the basics of Telekinesis which belongs to universal powers, to use the force to persuade someone, you first have to learn the basics of Jedi Mind Tricks (“These are not the droids you are looking for”).
We now want to add several additional information, based on the structure column:
- Integer Level 1-3
- Depth for each force power
- A sort-field of the type 001001001
- The ID of the parent element
We can achieve this with pure SQL:
with leveled_powers as ( select id, structure, name, -- Get the value of each level from structure or 0 nvl(to_number( regexp_substr(structure, '[0-9]+', 1, 1) ), 0) level1, nvl(to_number( regexp_substr(structure, '[0-9]+', 1, 2) ), 0) level2, nvl(to_number( regexp_substr(structure, '[0-9]+', 1, 3) ), 0) level3 from force_powers ) select id, structure, name, level1, level2, level3, -- Use the level to determine depth of current element case when level3 > 0 then 3 when level2 > 0 then 2 when level1 > 0 then 1 else 0 end depth, -- Concatenate the levels to a sortable string lpad(level1, 3, '0') || lpad(level2, 3, '0') || lpad(level3, 3, '0') sort, -- Get the id of the parent by structure (select id from force_powers p where p.structure = case when base.level3 > 0 then to_char(base.level1)||'.'||to_char(base.level2) when base.level2 > 0 then to_char(base.level1) end ) parent_id from leveled_powers base;
This will result in the following table:
And while it totally works this way, I find the SQL pretty hard to read and probably painful to maintain: you need to parse and understand every single line of SQL code to get a clue about what it will produce.
So let’s try to extract all the structure-related logic into a PL/SQL object type, starting by defining a type-header which contains all the necessary functions:
create or replace type t_numeric_structure force as object ( -- for the levels are our most basic data -- lets store them in the object c_level1 number(3,0), c_level2 number(3,0), c_level3 number(3,0), -- constructor to create structure from string constructor function t_numeric_structure( i_struct varchar2 ) return self as result, -- This is just a little helper function member function p$_position_for_level( i_string varchar2, i_level positiven ) return pls_integer, -- Functions to get the different levels member function level1 return pls_integer, member function level2 return pls_integer, member function level3 return pls_integer, -- Function to get the structure string member function structure return varchar2, -- Function to get the sort value member function sort return varchar2, -- Function to get the depth member function depth return pls_integer, -- Function to get an instance of the parent structure member function parent return t_numeric_structure );
We then implement the function, using the exact same logic as in the view:
create or replace type body t_numeric_structure as constructor function t_numeric_structure( i_struct varchar2 ) return self as result as begin -- We do exactly the same here as in the -- leveled_powers-with - just a bit more readable self.c_level1 := p$_position_for_level(i_struct, 1); self.c_level2 := p$_position_for_level(i_struct, 2); self.c_level3 := p$_position_for_level(i_struct, 3); return; end; member function p$_position_for_level( i_string in varchar2, i_level in positiven ) return pls_integer as begin return nvl(to_number( regexp_substr(i_string, '[0-9]+', 1, i_level) ),0); end; member function level1 return pls_integer as begin return c_level1; end; member function level2 return pls_integer as begin return c_level2; end; member function level3 return pls_integer as begin return c_level3; end; member function structure return varchar2 as l_result varchar2(50); begin -- Because we use the atomic levels we have -- to (re-)create the structure if ( c_level1 > 0 ) then l_result := to_char(c_level1); end if; if ( c_level2 > 0 ) then l_result := l_result || '.' || to_char(c_level2); end if; if ( c_level3 > 0 ) then l_result := l_result || '.' || to_char(c_level3); end if; return l_result; end; member function sort return varchar2 as begin -- Same logic as in the view return lpad(c_level1, 3, '0') || lpad(c_level2, 3, '0') || lpad(c_level3, 3, '0'); end; member function depth return pls_integer as begin -- The same logic as in the view -- with different flavour if c_level3 > 0 then return 3; elsif c_level2 > 0 then return 2; elsif c_level1 > 0 then return 1; else return 0; end if; end; member function parent return t_numeric_structure as begin -- We basically do the same here as in the subselect -- for parent_id, but return a new instance of -- the numeric_strucutre-type or NULL if ( c_level3 > 0 ) then return new t_numeric_structure( to_char(c_level1)||'.'||to_char(c_level2)); elsif ( c_level2 > 0 ) then return new t_numeric_structure( to_char(c_level1)); else return null; end if; end; end;
With this object type in place, we can now rewrite our SELECT statement like this:
select base.id, base.struct.structure() structure, base.name, base.struct.level1() level1, base.struct.level2() level3, base.struct.level3() level3, base.struct.depth() depth, base.struct.sort() sort, (select id from force_powers p where p.structure = base.struct.parent().structure() ) parent_id from ( select id, name, t_numeric_structure(structure) struct from force_powers ) base;
But why would I want to add 130 lines of object type code just to reduce my SQL code from 54 to 23 lines?
The benefits I see here are the following:
- When reading the new SQL statement, I get a much quicker understanding of what the code does without having to understand the implementation details: I therefore need much less mental load to get the intention
- When working on the different functions of the numeric_structure, I can concentrate on a single functionality at a time. This again reduces the mental load needed
- I successfully separated different concerns and made the logic to deal with structured information a module instead of being a part of my SELECT statement. This makes it easier to maintain the code and again reduces the mental load I need because I can just look at one module
- The separated modules are much easier to test (I will showcase this in a separate example)
Next thing we’ll do from here is to make the T_NUMERIC_STRUCTURE type a part of the FORCE_POWERS-table, so stay tuned.