Как создать пакетную функцию с параметром CSV строки и возвращающую результат в формате таблицы?

Question

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

select * from table(schema.mypackage.myfunction('one, two, three'))

Ожидаю такой результат:

one
two
three

Попробовал кое-что найденое на Ask Tom, но там решение только с SQL типами.

Есть ли что-то встроенное?

Использую 11g.

_{Свободный перевод вопроса how to convert csv to table in oracle от участника @Mehur}

Answer 1

Короткий ответ, если принять во внимание фразу - но там решение только с SQL типами - нет, это невозможно.

Перед тем тем, как перейти к решению, короткая информация о контекстах выполнения. Их два -- SQL и PL/SQL. Оба имеют свои собственные компиляторы, интерпретаторы байт-кода, и т.д.. Вызов функций из одного контекста выпполнения в другом осуществляется посредством RPC (Remote Procedural Call). Этот вызов невозможен, если тип результата известен только в одном из контекстов выполнения.

В данном случае, тип данных, который вернёт функция в операторе table() должен быть известен SQL парсеру, неважно, создан этот тип явно или неявно.

В качестве решения возможно воспользоваться:

SQL типом созданым явно, или уже существующими SQL типами, например, публично доступная коллекция символьных значений - sys.odciVarchar2List.
PL/SQL типами, определёнными в спецификации пакета. В этом случае будут неявно созданы структуры (shadow types), которые станут известны SQL парсеру. Эти типы накладывают не очень существенное ограничение - с ними нельзя создать обычные табличные функции, только конвейерные (pipelined).

Пример реализации ниже будет работать на всех версиях начиная с 11g.

create or replace package pack as
    type varchar2list is varray(32767) of varchar2(32);
    function plscsv2tab (csv varchar2) return varchar2List pipelined; 
    function sqlcsv2tab (csv varchar2) return sys.odciVarchar2List;
end;
/
create or replace package body pack as
    function split (str varchar2) return sys.odcivarchar2list is
        s int := 1; e int := 0;
        r sys.odcivarchar2list := sys.odcivarchar2list();
    begin <<split>> loop
            e := instr (str||',', ',', s);
            exit split when e = 0;
            r.extend; r(r.last) := trim (substr (str, s, e-s));
            s := e+1; 
        end loop;
        return r;
    end;    
    function plscsv2tab (csv varchar2) return varchar2List pipelined is
        tab sys.odcivarchar2list := split (csv); 
    begin
        for i in 1..tab.count loop pipe row (trim (tab(i))); end loop;
        return;
    end;
    function sqlcsv2tab (csv varchar2) return sys.odciVarchar2List is
    begin return (split (csv)); 
    end;
end;
/

select * from table (pack.plscsv2tab ('one, two, three'))
/
select * from table (pack.sqlcsv2tab ('one, two, three'))
/

Оба запроса вернут ожидаемый результат:

COLUMN_VALUE
--------------------------------
one
two
three

Answer 2

Прим.ред.: принятый как полезное решение ответ по ассоциации, привожу как "антипаттерн" и не рекомендую как пример для реализации.

Следущее будет работать, если вызвать так:

select * from table(splitter('a,b,c,d'))

Реализация функции:

create or replace function splitter(p_str in varchar2) return sys.odcivarchar2list
is
  v_tab sys.odcivarchar2list:=new sys.odcivarchar2list();
begin
  with cte as (
    select level  ind from dual
    connect by level <=regexp_count(p_str,',') +1
  )
  select regexp_substr(p_str,'[^,]+',1,ind)
  bulk collect into v_tab
  from cte;
  return v_tab;
end;
/

_{Свободный перевод ответа от участника @Shailesh}

Заметки ред.: Код в ответе рабочий. Но в функциях, которые используются в SQL запросах, стоит избегать вызова других SQL запросов. Это приводит к напрасному переключению контекста выполнения. Тот же самый результат может быть достигнут:

прямо в запросе, например, как в этом ответе
или оставаясь в PL/SQL контексте, ф-ии regexp_* доступны также в нём:
```
begin dbms_output.put_line ('found tokens '||(regexp_count('a,b,c,d',',') +1));
end;
/
found tokens 4
```

Чем больше будет сет данных, тем более будет заметна потеря производительности:

declare 
    t1 number; 
    cnt constant number := 10000;
    str varchar2 (32767) := 'aaa, bbb, ccc, ddd, eee, fff, ggg, iii, jjj, kkk, lll, mmm, nnn';
    tab sys.odcivarchar2list;
begin 
    t1 := dbms_utility.get_time; 
    for ix in 1..cnt loop
        select * bulk collect into tab from table (splitter (str)); 
    end loop;
    dbms_output.put_line ('splitter elapsed='||(dbms_utility.get_time - t1)/100||chr(9)||
        ' (tab.count='||tab.count||',cnt='||cnt||')');
    t1 := dbms_utility.get_time; 
    for it in 1..cnt loop
        select * bulk collect into tab from table (pack.sqlcsv2tab (str)); 
    end loop;
    dbms_output.put_line ('csv2tab  elapsed='||(dbms_utility.get_time - t1)/100||chr(9)||
        ' (tab.count='||tab.count||',cnt='||cnt||')');
end;
/

splitter elapsed=9,05    (tab.count=13,cnt=10000)
csv2tab  elapsed=1,83    (tab.count=13,cnt=10000)

БЛОГ НА HUSL

Как создать пакетную функцию с параметром CSV строки и возвращающую результат в формате таблицы?

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