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:
| ID | STRUCTURE | NAME |
|---|---|---|
| 1 | 1 | Universal |
| 2 | 1.1 | Telekinesis |
| 3 | 1.1.1 | Force Push |
| 4 | 1.1.2 | Force Pull |
| 5 | 1.1.3 | Force Jump |
| 6 | 2 | Light |
| 7 | 2.1 | Mind Trick |
| 8 | 2.1.1 | Force Persuation |
| 9 | 3 | Dark |
| 10 | 3.1 | Force wound |
| 11 | 3.1.1 | Force grip |
| 12 | 3.1.2 | Force choke |
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:
| ID | STRUCTURE | NAME | LEVEL1 | LEVEL2 | LEVEL3 | DEPTH | SORT | PARENT_ID |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Universal | 1 | 0 | 0 | 1 | 001000000 | NULL |
| 2 | 1.1 | Telekinesis | 1 | 1 | 0 | 2 | 001001000 | 1 |
| 3 | 1.1.1 | Force Push | 1 | 1 | 1 | 3 | 001001001 | 2 |
| 4 | 1.1.2 | Force Pull | 1 | 1 | 2 | 3 | 001001002 | 2 |
| 5 | 1.1.3 | Force Jump | 1 | 1 | 3 | 3 | 001001003 | 2 |
| 6 | 2 | Light | 2 | 0 | 0 | 1 | 002000000 | NULL |
| 7 | 2.1 | Mind Trick | 2 | 1 | 0 | 2 | 002001000 | 6 |
| 8 | 2.1.1 | Force Persuation | 2 | 1 | 1 | 3 | 002001001 | 7 |
| 9 | 3 | Dark | 3 | 0 | 0 | 1 | 003000000 | NULL |
| 10 | 3.1 | Force wound | 3 | 1 | 0 | 2 | 003001000 | 9 |
| 11 | 3.1.1 | Force grip | 3 | 1 | 1 | 3 | 003001001 | 10 |
| 12 | 3.1.2 | Force choke | 3 | 1 | 2 | 3 | 003001002 | 10 |
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)
You can run the whole example on LiveSQL and get it on my github repo.
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.
Top comments (0)