Как получить горизонтальную строку из вертикальной?

Question

Есть вертикальный список вида «поле» — «значение». Требуется преобразовать его в единичную запись определённого ROWTYPE. Целевая запись, естественно, содержит поля числового и календарного типа.

Пока использую case, и для каждого поля отдельно делаю преобразование примерно так:

create or replace procedure horizontalize(p_field_name in varchar2, p_field_value in varchar2, p_rec in out TABLE%ROWTYPE) as
begin
  case p_field_name
    when 'NUMBER_FIELD' then p_rec.number_field := to_number(p_field_value);
    when 'DATE_FIELD' then p_rec.date_field := to_date(p_field_value, 'DD.MM.RR HH24:MI:SS');
    when 'VARCHAR2_FIELD' then p_rec.varchar2_field := p_field_value;
  end case;
end;

К сожалению, pivot использовать нельзя, т.к. входные данные должны быть максимально произвольными. Напрашивается использование execute immediate, но похоже, что туда нельзя передать (и получить) параметром запись. Единственное, в чём можно быть относительно уверенным — в рамках одной записи каждое поле будет передано один раз.

Answer 1

Допустим, есть таблица:

create table tab (id int, name varchar2(16), created date)

Сделайте примерно так:

create or replace package pack as
    type tabrow is table of tab%rowtype;
    type colvallist is table of varchar2 (32) index by varchar2 (16);
    procedure horizontalize (cols colvallist, r out tab%rowtype);
end;    
/
create or replace package body pack as
    procedure horizontalize (cols colvallist, r out tab%rowtype) is
    begin
        r.id      := to_number (cols('id')); 
        r.name    := cols('name'); 
        r.created := to_date (cols('created'),'yyyy-mm-dd');
    end;
end;    
/

Как это работает:

var rc refcursor
declare
    r tab%rowtype;
    tr pack.tabrow;
    cols  pack.colvallist := pack.colvallist (
        'id'   =>               '111', 
        'name' =>               'aaa',
        'created' =>     '2021-05-07');
begin
    pack.horizontalize (cols, r);
    tr := pack.tabrow (r);
    open :rc for select * from table (tr);
end;
/

Результат:

        ID NAME             CREATED            
---------- ---------------- -------------------
       111 aaa              2021-05-07 00:00:00

Answer 2

Напрашивается использование execute immediate, но похоже, что туда нельзя передать (и получить) параметром запись.

В динамический запрос можно передавать и получать параметры с типом запись.
Решение с динамическим запросом конечно потеряет в производительности по сравнению с жёстким кодированием столбцов таблицы и полей записи. Но несравненно выиграет в гибкости и расширяемости, ведь при изменении/добавлении столбцов таблицы, в процедуре horizontalize не потребуется делать никаких изменений.

Воспроизводимый пример:

drop table tab purge;
create table tab (id int, name varchar2(16), code varchar2(8), created date)
/
create or replace package pack as
    type tabrow is table of tab%rowtype;
    type colvallist is table of varchar2 (32) index by varchar2 (16);
    procedure horizontalize (cols colvallist, rec out tab%rowtype);
end;    
/
create or replace package body pack as
    procedure horizontalize (cols colvallist, rec out tab%rowtype) is
        qry varchar2 (32767) := 'select '; 
    begin
        <<dict>> for r in (
            select lower (column_name) column_name, data_type 
            from user_tab_cols where table_name = 'TAB'
        ) loop
            qry := qry||case when cols.exists(r.column_name) then 
                case r.data_type
                    when 'NUMBER' then 'to_number('''||cols(r.column_name)||''')'
                    when 'DATE'   then 'to_date('''||cols(r.column_name)||''', ''yyyy-mm-dd'')' 
                    else ''''||cols(r.column_name)||'''' end
                else 'null' end||',';  
        end loop;
        qry := rtrim (qry, ',')||' from dual';
        dbms_output.put_line ('qry='||qry); 
        execute immediate qry into rec;
    end;
end;    
/

Вызов с одной "неверной" парой ключ-значение:

var rc refcursor
declare
    rec tab%rowtype;
    trw pack.tabrow;
    cols  pack.colvallist := pack.colvallist (
        'id'      =>        '111', 
        'xname'   =>        'aaa',
        'code'    =>        'c99',
        'created' => '2021-05-15');
begin
    pack.horizontalize (cols, rec);
    trw := pack.tabrow (rec);
    open :rc for select * from table (trw);
end;
/

Результат:

        ID NAME             CODE     CREATED            
---------- ---------------- -------- -------------------
       111                  c99      2021-05-15 00:00:00

БЛОГ НА HUSL

Как получить горизонтальную строку из вертикальной?

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