Как разбить строку по разделителю на табличные записи для использования в запросах?

Question

Есть таблица params (key, valueList) с ключом и листом значений с каким-то разделителем, например:

KEY  VALUELIST          
---- -------------------
JOBS HR_REP;AC_MGR;AD_VP

Есть ли встроенная функция, чтобы разбить лист значений по разделителю на табличные записи?

Вот так хотелось бы её использовать:

declare 
    procedure processJobs (Jobs tableType) 
    begin
        for r in (
            select *
            from hr.employees 
            join valueList on jobid = job_id
        ) loop /* какие то действия */ end loop;
    end;
begin 
    processJobs (splitToTable (valueList));
end;

Answer 1

Пока такой функции "из коробки" нет. При необходимости её надо реализовать самому.

Можно создать пользовательский тип данных с основным набором функциональности:

create or replace type tokenList force is table of varchar2 (32767)
/
create or replace type Tokenizer as object (
    tokens tokenList,
    constructor function Tokenizer return self as result,
    constructor function Tokenizer (str varchar2, delimiter char) return self as result,
    static function split (str varchar2, delimiter char) return tokenList,
    member function join (delimiter char := ',') return varchar2
) instantiable not final
/

Рабочий пример использования как в вопросе:

var rc refcursor
declare 
    tokens Tokenizer;
    procedure processJobs (jobs Tokenizer, rc out sys_refcursor) is 
    begin
        open rc for
        select employee_id, last_name||','||first_name name, job_id
        from hr.employees 
        join table (jobs.tokens) on column_value = job_id;
    end;
begin
    select Tokenizer (valueList, ';') into tokens
    from params where key = 'JOBS'; 
    processJobs (tokens, :rc);
end;
/
EMPLOYEE_ID NAME                 JOB_ID    
----------- -------------------- ----------
        203 Mavris,Susan         HR_REP    
        205 Higgins,Shelley      AC_MGR    
        101 Kochhar,Neena        AD_VP

Будет прекрасно работать и в чисто SQL запросах:

select Tokenizer (cast (collect (key) as tokenList)).join(';') out
from (
    select column_value key 
    from Tokenizer ('abc##123##zzz','##').tokens)
where key in (select * from Tokenizer.split ('abc,zzz,nnn',',')); 

OUT
--------------------
abc;zzz

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

create or replace type body Tokenizer as
    constructor function Tokenizer (str varchar2, delimiter char) return self as result is
    begin 
        self.tokens := Tokenizer.split (str, delimiter); 
        return; 
    end;
    constructor function Tokenizer (tokens tokenList) return self as result is
    begin 
        self.tokens := tokens; 
        return; 
    end Tokenizer;
    static function split (str varchar2, delimiter char) return tokenList is
        type posValues is record (s int := 1, e int  := 0);
        pos posValues;
        tokens tokenList := tokenList (); 
        len constant int := length (str);
    begin 
        <<split>> loop 
            pos.e := instr (str, delimiter, pos.s);
            exit split when pos.e = 0 and pos.s >= len;
            if pos.e = 0 then pos.e := len+1; end if; 
            tokens.extend;
            tokens(tokens.last) := substr (str, pos.s, pos.e-pos.s);
            pos.s := pos.e + length (delimiter); 
        end loop;
        return tokens;
    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;

PS Пример реализации с функциями regexp_*:

static function split (str varchar2, delimiter char, flag char) return tokenList is
    pattern constant varchar2 (64) := '(.*?)('||delimiter||')';
    tokens tokenList := tokenList ();
    occurrences int;
begin 
    occurrences := regexp_count (str||delimiter, pattern, 1, flag);   
    <<split>> for occurr in 1..occurrences loop 
        tokens.extend;
        tokens(tokens.last) := regexp_substr(str||delimiter, pattern, 1, occurr, flag, 1);
    end loop;
    return tokens;
end split;

здесь факультативно, так как явно уступает по производительности in/substr:

in/substr elapsed=3,28   (tokens=10,iters=100000)
regexp_fn elapsed=77,29  (tokens=10,iters=100000)

БЛОГ НА HUSL

Как разбить строку по разделителю на табличные записи для использования в запросах?

Ответы (1 шт):