Блог

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

Пример:

производная таблица:

Теперь мне нужно вставить эти данные в новую/существующую таблицу. Я создал и тип объекта/пакет, в котором есть все столбцы из производной таблицы, однако я не уверен, как добавить/ссылаться на эти данные, чтобы их можно было вставить в новую/существующую таблицу.

Функции, возвращающие вложенную таблицу, могут быть преобразованы в обычные строки и столбцы путем выбора из функции таблицы TABLE с помощью функции. (И в последних версиях Oracle TABLE оператор необязателен.)

Например:

 SQL> create or replace function get_columns return sys.odcivarchar2list is
  2  begin
  3      return sys.odcivarchar2list('355352', 'Yes');
  4  end;
  5  /

Function created.

SQL> select column_value from table(get_columns);

COLUMN_VALUE
--------------------------------------------------------------------------------
355352
Yes
 

Теперь эту функцию можно использовать где угодно, например, для вставки: insert into some_table select column_value from table(get_columns);

Ваше конкретное решение может быть более сложным, если результаты содержат несколько столбцов, или у вас несколько наборов результатов и т. Д. Отредактируйте свой вопрос с более подробной информацией о функциях и типах, если вам все еще нужна помощь.

Изменить: Ниже приведен полный пример хранения значений CSV в столбце, создания функции для преобразования CSV в VARRAY, вызова этой функции для поворота, а затем повторного поворота в несколько столбцов:

 create table table_1
(
    request_id number,
    service varchar2(100),
    method varchar2(100),
    request_date date,
    process_date date,
    te_suid number,
    status varchar2(100),
    params varchar2(4000)
);

create or replace type params_varray is varray(48) of varchar2(4000);

--Example function that converts comma-separated lists into VARRAYS.
--(Not tested for nulls, consecutive commas, etc.)
create or replace function get_columns(p_params varchar2, p_delimiter varchar2) return params_varray is
    v_array params_varray := params_varray();
begin
    for i in 1 .. regexp_count(p_params, p_delimiter)   1 loop
        v_array.extend;
        v_array(v_array.count) := regexp_substr(p_params, '[^'||p_delimiter||'] ', 1, i);
    end loop;

    return v_array;
end;
/

insert into table_1 values(1,1,1,sysdate,sysdate,1,1,'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48');
insert into table_1 values(2,2,2,sysdate,sysdate,2,2,'49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96');


--Convert CSV into multiple columns.
--
--Convert rows back into multiple columns.
select request_id, service, method, request_date, process_date, te_suid, status
    ,max(case when param_index = 1 then param_value else null end) value1
    ,max(case when param_index = 2 then param_value else null end) value2
    ,max(case when param_index = 3 then param_value else null end) value3
    --Repeat for 4 through 48.
from
(
    --Convert CSV to rows.
    select request_id, service, method, request_date, process_date, te_suid, status,
        row_number() over (partition by table_1.rowid order by rownum) param_index,
        column_value param_value
    from table_1
    cross join get_columns(table_1.params, ',')
)
group by request_id, service, method, request_date, process_date, te_suid, status;
 

Вы можете запустить пример в db<>fiddle<>.

Но если у вас есть какой-либо выбор в этом вопросе, я настоятельно рекомендую вам денормализовать свою таблицу и никогда не хранить значения с разделителями в базе данных.