Structure Force Powers with PL/SQL Object Types

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;

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
);

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;

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;