Как грамотно сделать декомпозицию сложной SQL функции?

Пока сделал декомпозицию, вынеся логику для получения RegEx и для нормализации различных сущностей в отдельные функции.

create or replace function normalize_csv_values (
    str             varchar2,
    typ             varchar2    := 'phone',
    target_sep      char        := '; '
) return varchar2
as
    s           varchar2(96);
    n           int := 0;
    pattern     varchar2(64)    := '\s*[,;]\s*';
    tokens      varchartab      := varchartab ();
    l_re_pref   varchar2(64)    := '([^[:space:]].*?)((';
    l_re_suff   varchar2(64)    := ')|($))';
    l_type      varchar2(128)   := lower(trim(typ));
    l_res       varchar2(4096);
begin
    -- normalizes CSV-like strings in the following way:
    --  1. in a loop split [str] by the separator that is returned from "get_csv_split_re_pattern()"
    --      1.1 get a single splitted value
    --      1.2 normalize it, using "normalize_value(val, l_type)"
    --      1.3 append normalized value to collection [tokens]
    --  2. pack values from [tokens] collection back to CSV string using [target_sep] separator
    --  3. return resulting CSV string

    -- get split RegEx for the [typ] entity type
    pattern := l_re_pref || get_csv_split_re_pattern(l_type) || l_re_suff;

    -- loop through values in the CSV string
    <<split>> loop n := n + 1;
        -- get N-th value from the CSV string
        s := regexp_substr (str, pattern, 1, n, null, 1);
        exit split when s is null;
        s := normalize_value(s, l_type);
        tokens.extend;
        tokens(tokens.last) := s;
    end loop;

    -- pack parsed CSV values back to CSV form, using specified [target_sep]
    select listagg(column_value, target_sep) within group (order by rownum)
    into l_res
    from table(tokens);

    return l_res;
end;
/

Предложение для реализации.

Вынести из всех функций шаблоны регулярных выражений в отдельную таблицу:

create table normalizerconf (tabname, colname, ty, splitpatt, rmpatt) as
    select 'data1', 'col1', 'phone', '(.*?)((,\s+)|($))', '\D+' from dual  
/

Тогда рабочий запрос будет выглядеть так (на db<>fiddle):

create table data1 (id, col1) as
    select 1, '123/456789, 032-156789' from dual
/

with 
function getphonelist (
    col varchar2, splitpatt varchar2, rmpatt varchar2) return varchar2 is
begin
    return NormalizePhone (col, splitpatt, rmpatt).join();
end;
select d.col1, getphonelist (col1, splitpatt, rmpatt) result
from data1 d
cross join normalizerconf c
where c.tabname = 'data1' and colname = 'col1'
/

COL1                   RESULT                
---------------------- ----------------------
123/456789, 032-156789 123456789; 032156789  

Создать базовый пользовательский тип, для каждого спец. вида данных свой унаследованный тип:

create or replace type Normalizer as object (
    tokens tokenList,
    member procedure split (str varchar2, pattern char), -- return tokenList,
    not instantiable member procedure normalize (rmpatt char),
    member function join (delimiter char := '; ') return varchar2
) not instantiable not final
/
create or replace type NormalizePhone under Normalizer (
    constructor function NormalizePhone (
        str varchar2, splitpatt char, rmpatt char) return self as result,
    overriding member procedure normalize (rmpatt char) -- return tokenList,
) instantiable final
/

Пример реализации типов:

create or replace type body Normalizer as
    member procedure split (str varchar2, pattern char) is
        s varchar2(96);
        c int := 0;
    begin
        <<split>> loop c := c + 1;
            s := regexp_substr (str, pattern, 1, c, null, 1);
            exit split when s is null;
            self.tokens.extend;
            self.tokens(tokens.last) := s;
        end loop;
    end split;
    member function join (delimiter char := '; ') return varchar2 is
        ret varchar2 (32767);
    begin
        for i in 1..self.tokens.count loop 
            ret := ret||tokens(i)||delimiter; end loop;
        return rtrim (ret, delimiter);
    end join;
end;
/

create or replace type body NormalizePhone as
    constructor function NormalizePhone (
        str varchar2, splitpatt char, rmpatt char) return self as result is
    begin
        self.tokens := tokenList(); 
        self.split (str, splitpatt);
        normalize (rmpatt);
        return;
    end;
    overriding member procedure normalize (rmpatt char) is
    begin 
        for i in 1..self.tokens.count loop
            self.tokens(i) := regexp_replace (self.tokens(i), rmpatt);
        end loop;
    end;
end;
/

Так будет выглядеть решение с функциями в пакете.

Конечный запрос с данными из этого ответа, будет выглядеть так:

with function getphonelist (
    col varchar2, splitpatt varchar2, rmpatt varchar2) return varchar2 is
begin
    return
        packNormalizer.join (
            packNormalizer.normalize (
                packNormalizer.split (col, splitpatt), rmpatt));
end;
select d.col1, getphonelist (col1, splitpatt, rmpatt) result
from data1 d
cross join normalizerconf c
where c.tabname = 'data1' and colname = 'col1'
/

COL1                   RESULT                          
---------------------- --------------------------------
123/456789, 032-156789 123456789; 032156789            

Реализация пакета (на db<>fiddle):

create or replace package packNormalizer as 
    function split (str varchar2, pattern char) return tokenList;
    function normalize (tl tokenList, rmpatt char) return tokenList;
    function join (tokens tokenList, delimiter char := '; ') return varchar2;
end;
/

create or replace package body packNormalizer as
    function split (str varchar2, pattern char) return tokenList is
        tokens tokenList := tokenList ();
        s varchar2(96);
        c int := 0;
    begin
        <<split>> loop c := c + 1;
            s := regexp_substr (str, pattern, 1, c, null, 1);
            exit split when s is null;
            tokens.extend;
            tokens(tokens.last) := s;
        end loop;
        return tokens;
    end split;
    function normalize (tl tokenList, rmpatt char) return tokenList is
        tokens tokenList := tl;
    begin 
        for i in 1..tokens.count loop
            tokens(i) := regexp_replace (tokens(i), rmpatt);
        end loop;
        return tokens;
    end; 
    function join (tokens tokenList, delimiter char := '; ') return varchar2 is
        ret varchar2 (32767);
    begin
        for i in 1..tokens.count loop ret := ret||tokens(i)||delimiter; end loop;
        return rtrim (ret, delimiter);
    end join;
end;
/