Привет! Меня зовут Зураб Диаконашвили, я разработчик в компании SM Lab. Сегодня поговорим об использовании полиморфных табличных функций в Oracle и рассмотрим их работу на примерах.
При работе с Oracle используются SQL-запросы – они помогают управлять базами данных, представленными в виде таблиц. Мы получили задачу на динамическое добавление полей в стационарный набор полей сущности. Для этого мы решили попробовать PTF-функцию (пользовательскую табличную функцию, которая вызывается в предложении FROM) и сравнить результат её работы с обычным SQL PIVOT и JavaScript.
Полиморфные табличные функции (PTF) являются частью стандарта SQL: 2016. В Oracle они представлены в версии 18c.
Что необходимо знать при работе с PTF:
PTF – это определяемые пользователем табличные функции, которые могут быть вызваны в предложении FROM;
тип строки результата (набор колонок) не объявляется при создании функции;
тип строки результата может зависеть от аргументов функции и, следовательно, от точного синтаксиса вызова;
PTF может использовать произвольное количество параметров, Oracle же требует один обязательный параметр – исходная таблица. Это может быть как таблица в БД, так и CTE (with-выражение).
С помощью PTF мы можем манипулировать как набором колонок итоговой таблицы, так и набором строк, т.е. можем добавлять/удалять колонки и добавлять/удалять строки.
Также следует обратить внимание, что в PTF-пакете может быть объявлено три процедуры и одна функция:
DESCRIBE function(обязательно);FETCH_ROWSprocedure (опционально);OPEN procedure(опционально);CLOSE procedure(опционально).
В обязательной функции DESCRIBE (описательная часть) указываем, какие колонки мы добавляем и исключаем из исходного набора данных, а также какие колонки должны быть доступны для чтения в процедуре FETCH_ROWS.
Из процедур основной является FETCH_ROWS, в которой мы получаем вычисленные отображаемые данные при выполнении SQL-запроса. OPEN и CLOSE – это необязательные процедуры. Их смысл в инициализации данных, которые хранятся в store для дальнейшего использования, и финализации расчетов (при необходимости) соответственно.
Время выполнения процедур/функций PTF-пакета:
функция
DESCRIBEвыполняется при компиляции SQL-запроса, т.е. для каждогоSQL_IDфункцияDESCRIBEвыполняется один единственный раз;остальные процедуры вызываются в процессе выполнения SQL-запроса.
Рассмотрим на примерах.
Пример 1. Самый простой пример
/* Скрипт генерации тестовых данных */ create table t ( a number, b number, c number ); insert into t values (1,2,3); insert into t values (4,5,6); insert into t values (7,8,9); commit; /********************************************************************/ /* Пакет для работы с PTF*/ create or replace package ptf_package_1 as function my_ptf ( -- исходная таблица - это может быть как таблица БД, так и CTE (with t as () ...) p_tab in out table ) return table pipelined row polymorphic using ptf_package_1; function describe ( -- исходная таблица p_tab in out dbms_tf.table_t ) return dbms_tf.describe_t; end ptf_package_1; / create or replace package body ptf_package_1 as function describe ( -- исходная таблица p_tab in out dbms_tf.table_t ) return dbms_tf.describe_t is begin return null; end; end ptf_package_1; /
Это примитивный, вырожденный пример, в нем PTF ничего не изменяет, а возвращает исходные данные как есть.
В этом пакете объявлена функция my_ptf, которую мы будем вызывать в SQL-запросе. У этой функции есть только заголовок и нет тела.
Мы указываем в объявлении функции:
table pipelined;row|table polymorphic;using <имя пакета>(пакет, в котором описаны процедуры, необходимые для работы этой функции).
Тут можно указать одну из двух возможных семантик – строковая семантика (row polymorphic) или табличная семантика (table polymorphic).
В данном случае функция объявлена внутри пакета, но совершенно необязательно объявлять ее именно так – это может быть самостоятельная функция, может быть функция в составе другого пакета.
Здесь функция DESCRIBE возвращает null и итогом работы PTF будут данные, которые ничем не отличаются от данных в исходной таблице, в чем мы и можем убедиться, выполнив SQL-запрос:
select * from ptf_package_1.my_ptf(t);
Пример 2. Добавление и удаление строк и колонок.
create or replace package ptf_package_2 as -------------------------------------------------------------------------------- function my_ptf ( p_tab in out table, -- исходная таблица p_hide_cols in columns default null, -- колонки к удалению p_new_col_name in varchar2 default null, -- новая колонка p_new_col_val in varchar2 default null -- значение для новой колонки ) return table pipelined row polymorphic using ptf_package_2; -------------------------------------------------------------------------------- function describe ( p_tab in out dbms_tf.table_t, -- исходная таблица p_hide_cols in dbms_tf.columns_t default null, -- колонки к удалению p_new_col_name in varchar2 default null, -- новая колонка p_new_col_val in varchar2 default null -- значение для новой колонки ) return DBMS_TF.DESCRIBE_T; -------------------------------------------------------------------------------- procedure fetch_rows ( p_new_col_name in varchar2 default null, -- новая колонка p_new_col_val in varchar2 default null -- значение для новой колонки ); end ptf_package_2; / create or replace package body ptf_package_2 as -------------------------------------------------------------------------------- function describe ( p_tab in out dbms_tf.table_t, -- исходная таблица p_hide_cols in dbms_tf.columns_t default null, -- колонки к удалению p_new_col_name in varchar2 default null, -- новая колонка p_new_col_val in varchar2 default null -- значение для новой колонки ) return dbms_tf.describe_t is v_new_columns dbms_tf.columns_new_t; begin -- пометим колонки которые нужно убрать: PASS_THROUGH := false for i in 1 .. p_tab.column.count loop if p_tab.column(i).description.name member of p_hide_cols then p_tab.column(i).pass_through := false; p_tab.column(i).for_read := false; -- значение по умолчанию, здесь для наглядности end if; end loop; -- добавим новую колонку v_new_columns(1) := dbms_tf.column_metadata_t ( name => p_new_col_name, type => dbms_tf.type_varchar2 ); -- вернем список добавляемых колонок return dbms_tf.describe_t( new_columns => v_new_columns ); end; -------------------------------------------------------------------------------- procedure fetch_rows ( p_new_col_name in varchar2 default null, -- новая колонка p_new_col_val in varchar2 default null -- значение для новой колонки ) as v_column_values dbms_tf.tab_varchar2_t; v_env dbms_tf.env_t := dbms_tf.get_env(); begin DBMS_TF.Trace(v_env); -- Для всех строк задаем одно и тоже значение для новой колонки for i in 1..v_env.row_count loop v_column_values(nvl(v_column_values.last + 1, 1)) := p_new_col_val; end loop; -- вставим коллекцию значений новой колонки в итоговую таблицу dbms_tf.put_col(1, v_column_values); end; end ptf_package_2; / /* SQL-запрос для вызова PTF */ select * from ptf_package_2.my_ptf(t, columns(c), 'D', 'Hello world!');
Этот пример гораздо интереснее первого. Как было указано выше, с помощью PTF мы можем добавлять строки и колонки и удалять их из исходной таблицы. С исходной таблицей ничего не происходит, меняется только результирующий набор данных.
В функции DESCRIBE задается:
какие колонки следует скрыть из результата;
какие новые колонки следует добавить;
к��кие колонки следует прокидывать в результат;
значения каких колонок должны быть доступны в процедуре
FETCH_ROWS.
Функция DESCRIBE возвращает тип DBMS_TF.DESCRIBE_T – список новых полей, которые должна вернуть PTF.
TYPE column_t IS RECORD ( description COLUMN_METADATA_T, pass_through BOOLEAN, -- прокидывать ли колонку в результат for_read BOOLEAN -- доступна ли колонка в FETCH_ROWS );
При работе с PTF важно помнить о единственном ограничении: параметры и их наименования, указанные в этой функции, должны совпадать с параметрами, которые мы указываем в функции DESCRIBE; но при этом есть разница. В DESCRIBE мы указываем специальные типы, которые описаны в пакете DBMS_TF – это специальный пакет под полиморфные табличные функции. В PTF-функции первый параметр указан как table – это так называемый псевдооператор, который был создан специально под технологию PTF. Второй псевдооператор – это columns. Эти псевдооператоры мы указываем только в описании FTF-функции, которая будет вызываться в SQL-запросе.
Можно было бы решить, что с этими параметрами следует работать как с обычными коллекциями, но на самом деле это не совсем так – они частично ведут себя как коллекции, но это не коллекции. Позже вернемся к этому нюансу.
Снова обратимся к примеру. Мы исключаем колонки, которые перечислили в параметре hide_cols, и указываем, что параметр pass_through = false.
За что отвечает параметр PASS_THROUGH? Его смысл прост: если PASS_THROUGH имеет значение true, то это означает, что колонка прокидывается из первоначальной таблицы в выходную таблицу, выходные данные. Второй параметр FOR_READ отвечает за то, данные каких колонок будут доступны внутри FETCH_ROWS.
По умолчанию PASS_THROUGH = true , а FOR_READ = false;
Теперь добавление новой колонки. Новые колонки мы добавляем следующим образом: есть объект, который возвращает функция DESCRIBE, и одним из его атрибутов является коллекция, содержащая список новых колонок.
В этом примере в процедуре FETCH_ROWS мы задаем для всех строк одно и то же значение, которое заполняется в коллекцию, и эта колонка вставляется в итоговые, результирующие данные. Здесь с помощью процедуры DBMS_TF.put_col вставляется новая колонка с указанными данными. Единичка тут означает первую колонку из вставляемых.
Важный момент: параметры в FETCH_ROWS должны быть правильными. Что значит правильными: по документации мы обязаны в FETCH_ROWS передать все параметры, которые указаны в PTF-функции, кроме тех параметров, которые указаны с помощью псевдооператоров, т.е. с помощью table и columns.
Давайте посмотрим на результат работы.
Исходные данные:
Результат работы PTF:
Пример 3. Добавление реальных данных в новую колонку
create or replace package ptf_package_3 as -------------------------------------------------------------------------------- function my_ptf ( p_tab in out table, -- исходная таблица p_new_col_name in varchar2 default null -- новая колонка ) return table pipelined row polymorphic using ptf_package_3; -------------------------------------------------------------------------------- function describe ( p_tab in out dbms_tf.table_t, -- исходная таблица p_new_col_name in varchar2 default null -- новая колонка ) return DBMS_TF.DESCRIBE_T; -------------------------------------------------------------------------------- procedure fetch_rows ( p_new_col_name in varchar2 default null -- новая колонка ); end ptf_package_3; / create or replace package body ptf_package_3 as -------------------------------------------------------------------------------- function describe ( p_tab in out dbms_tf.table_t, -- исходная таблица p_new_col_name in varchar2 default null -- новая колонка ) return dbms_tf.describe_t is v_new_columns dbms_tf.columns_new_t; begin -- пометим все колонки доступными для чтения в процедуре FETCH_ROWS for i in 1 .. p_tab.column.count loop p_tab.column(i).for_read := true; end loop; -- добавим новую колонку v_new_columns(1) := dbms_tf.column_metadata_t ( name => p_new_col_name, type => dbms_tf.type_varchar2 ); -- вернем список НОВЫХ, добавляемых колонок return dbms_tf.describe_t( new_columns => v_new_columns ); end; -------------------------------------------------------------------------------- procedure fetch_rows ( p_new_col_name in varchar2 default null -- новая колонка ) as v_row_set dbms_tf.row_set_t; v_new_column dbms_tf.tab_varchar2_t; v_row_count pls_integer; v_env dbms_tf.env_t := dbms_tf.get_env(); begin -- получаем данные из исходной таблицы DBMS_TF.Get_Row_Set ( rowset => v_row_set, row_count => v_row_count ); -- выводим трассировку dbms_output.put_line('v_row_count = ' || v_row_count); dbms_tf.trace(v_row_set); dbms_tf.Trace(v_env); -- вычисляем значение для новой колонки - входные данные в JSON формате for i in 1..v_env.row_count loop v_new_column(i) := dbms_tf.row_to_char(v_row_set, i); end loop; -- вставим коллекцию значений новой колонки в строку итоговой таблицы DBMS_TF.Put_Col(1, v_new_column); end; end ptf_package_3; /
Давайте сразу посмотрим на результат её работы:
Исходная таблица та же самая. Мы добавляем новую колонку, которую мы заполняем данными в формате JSON, т.е. здесь мы уже работаем с реальными данными.
А как же мы это делаем?
В функции DESCRIBE указываем, что данные всех колонок нам нужны для чтения в процедуре FETCH_ROWS, т.е. в той процедуре, которая выполняется при SQL-запросе. Соответственно, мы указываем FOR_READ = true и добавляем новую колонку, указываем первым элементом коллекции эту колонку и передаем ее в результат.
В процедуре FETCH_ROWS нам сперва нужно получить все данные из исходной таблицы с помощью DBMS_TF.Get_Row_Set. Мы получаем их в переменную v_row_set и в v_row_count коли��ество строк, которые эта функция вернула.
Далее в этой процедуре мы получили данные и в специальную коллекцию вносим значения для всех новых колонок. В данном случае у нас одна единственная новая колонка, но я для наглядности здесь организовал цикл.
Также в этой процедуре мы выводим трассировку как для набора данных, так и для так называемого окружения.
Давайте рассмотрим результат трассировки. В разделе Get Columns перечисляется информация по тем колонкам, которые мы указали FOR_READ = true (в данном случае мы везде указали, что FOR_READ = true, поэтому здесь указаны все колонки).
И в разделе трассировки Put Columns выводится информация по новой колонке.
Пример 4. Разворачивание полей из структуры EAV (Entity Attribute Value) в обычный плоский табличный вид – главная цель тестирования PTF.
Скрипт генерации данных для примера:
-- create mock data -- drop table ptf_100; create table ptf_100 ( code number, field_name varchar2(100 char), value varchar2(100 char)); create unique index ptf_100_ui on ptf_100 (code, field_name); declare c_rows constant number := 100; v_cur_count number(30) := 1; v_check number(30); begin for i in ( select rownum as code from dual t connect by level <= c_rows ) loop -- dbms_output.put_line(i.code); insert into ptf_100 (code, field_name, value) values (i.code, 'FIRST_NAME', to_char(dbms_random.string('U', trunc(dbms_random.value(5, 10))))); insert into ptf_100 (code, field_name, value) values (i.code, 'LAST_NAME', to_char(dbms_random.string('U', trunc(dbms_random.value(5, 20))))); insert into ptf_100 (code, field_name, value) values (i.code, 'GENDER', to_char(decode(round(dbms_random.value(1, 2)), 1, 'MALE', 2, 'FEMALE'))); insert into ptf_100 (code, field_name, value) values (i.code, 'PET', to_char(decode(round(dbms_random.value(1, 4)), 1, 'Dog', 2, 'Cat', 3, 'Bird', 4, 'Reptile'))); insert into ptf_100 (code, field_name, value) values (i.code, 'CITY', to_char(decode(round(dbms_random.value(1, 10)), 1, 'Moscow', 2, 'Tokyo', 3, 'Osaka', 4, 'Cairo', 5, 'New York', 6, 'Mexico City', 7, 'Shanghai', 8, 'Dhaka', 9, 'Delhi', 10, 'Sao Paulo'))); insert into ptf_100 (code, field_name, value) values (i.code, 'POSTCODE', round(dbms_random.value(100000, 999999))); v_cur_count := v_cur_count + 1; end loop; -- dbms_output.put_line(v_cur_count); commit; end; /
Данные подготовлены, смотрим, как выглядит исходная таблица:
Это обычная EAV-структура, всем знакомая: у нас есть код сущности и атрибуты этой сущности, в данном случае имя, фамилия, пол, питомец, город, почтовый индекс. Они у нас даны в вертикальной структуре, а цель нашей задачи развернуть их, сделать аналог стандартной операции PIVOT.
create or replace package ptf_package_4 is -------------------------------------------------------------------------------- function pivot_props ( -- исходная таблица p_tab in out table, -- заранее вычисленный список наименований пользовательских полей p_pivot_fields in columns, -- наименование ключевого поля в исходной таблице p_key_field in varchar2 := 'CODE', -- наименование поля в исходной таблице, содержащего наименования полей в итоговой таблице p_name_field in varchar2 := 'FIELD_NAME', -- наименование поля в исходной таблице, содержащего значения полей в итоговой таблице p_value_field in varchar2 := 'VALUE' ) return table pipelined table polymorphic using ptf_package_4; -------------------------------------------------------------------------------- function describe ( -- исходная таблица p_tab in out dbms_tf.table_t, -- заранее вычисленный список наименований пользовательских полей p_pivot_fields in dbms_tf.columns_t, -- наименование ключевого поля в исходной таблице p_key_field in varchar2 := 'CODE', -- наименование поля в исходной таблице, содержащего наименования полей в итоговой таблице p_name_field in varchar2 := 'FIELD_NAME', -- наименование поля в исходной таблице, содержащего значения полей в итоговой таблице p_value_field in varchar2 := 'VALUE' ) return dbms_tf.describe_t; -------------------------------------------------------------------------------- procedure fetch_rows ( -- наименование ключевого поля в исходной таблице p_key_field in varchar2 := 'CODE', -- наименование поля в исходной таблице, содержащего наименования полей в итоговой таблице p_name_field in varchar2 := 'FIELD_NAME', -- наименование поля в исходной таблице, содержащего значения полей в итоговой таблице p_value_field in varchar2 := 'VALUE' ); end ptf_package_4; / create or replace package body ptf_package_4 is -------------------------------------------------------------------------------- /* В функции DESCRIBE определяюм описание итогового набора полей - можно добавлять/удалять поля. Т.е. это описательная часть PTF, про метаданные */ function describe ( -- исходная таблица p_tab in out dbms_tf.table_t, -- заранее вычисленный список наименований пользовательских полей p_pivot_fields in dbms_tf.columns_t, -- наименование ключевого поля в исходной таблице p_key_field in varchar2 := 'CODE', -- наименование поля в исходной таблице, содержащего наименования полей в итоговой таблице p_name_field in varchar2 := 'FIELD_NAME', -- наименование поля в исходной таблице, содержащего значения полей в итоговой таблице p_value_field in varchar2 := 'VALUE' ) return dbms_tf.describe_t is v_key_idx pls_integer := 1; v_new_columns dbms_tf.columns_new_t; v_descr dbms_tf.describe_t; v_name_field_idx pls_integer; v_value_field_idx pls_integer; v_key_field varchar2(255 char) := upper(p_key_field); v_name_field varchar2(255 char) := upper(p_name_field); v_value_field varchar2(255 char) := upper(p_value_field); v_column_name varchar2(255 char); begin -- dbms_tf.trace(p_tab); -- Помечаем все колонки: -- PASS_THROUGH := false -- колонки НЕ прокидываются из исходных данных в выходящие -- FOR_READ := true -- чтобы их значения были доступны в процедуре FETCH_ROWS for i in 1 .. p_tab.column.count loop -- ни одну колонку исходной таблицы мы НЕ прокидываем в конечную, ключевая колонка в итоговой таблице будет заполняться вручную p_tab.column(i).pass_through := false; -- и соответственно каждую колонку помечаем for_read, чтобы их значения были доступны в FETCH_ROWS p_tab.column(i).for_read := true; -- получаем имя текущей колонки v_column_name := upper(replace(p_tab.column(i).description.name, '"')); -- вычисляем порядковые номера полей с наименованиями и со значениями разворачиваемых полей if v_column_name = v_name_field then -- порядковый номер поля с наименованиями v_name_field_idx := i; elsif v_column_name = v_value_field then -- порядковый номер поля со значениями v_value_field_idx := i; end if; -- ключевое поле указываем как вновь добавляемое if v_column_name = v_key_field then v_new_columns(v_key_idx) := dbms_tf.column_metadata_t ( name => p_tab.column(i).description.name, type => p_tab.column(i).description.type ); end if; end loop; -- Добавляем новые поля (пользовательские, в данном случае) /* помним, что функция DESCRIBE выполняется при компиляции SQL запроса, а не при каждом выполнении SQL, соотв-но, если текст запроса не изменился, то и процедура DESCRIBE не будет вызвана и список ноых полей останется таким каким он был вычислен при последней компиляции SQL */ if p_pivot_fields is not null then for i in 1 .. p_pivot_fields.count loop v_new_columns(i + 1/* учитываем ключевое поле */) := dbms_tf.column_metadata_t ( name => p_pivot_fields(i), type => dbms_tf.type_varchar2 ); end loop; end if; -- сохраняем в стор порядковые номера колонок с наименованиями атрибутов и их значениями v_descr.cstore_num('name_field_idx') := v_name_field_idx; v_descr.cstore_num('value_field_idx') := v_value_field_idx; -- добавим новые поля в результат v_descr.new_columns := v_new_columns; -- взводим флаг репликации, чтобы мы могли манипулировать количеством строк в итоговой таблице v_descr.row_replication := true; -- Вернем описание новых полей return v_descr; end; -------------------------------------------------------------------------------- /* Процедура FETCH_ROWS вызывается при выполнении SQL она отвечает за данные, выводимые в полях, заданных в функции DESCRIBE */ procedure fetch_rows ( -- наименование ключевого поля в исходной таблице p_key_field in varchar2 := 'CODE', -- наименование поля в исходной таблице, содержащего наименования полей в итоговой таблице p_name_field in varchar2 := 'FIELD_NAME', -- наименование поля в исходной таблице, содержащего значения полей в итоговой таблице p_value_field in varchar2 := 'VALUE' ) is v_inp_rs dbms_tf.row_set_t; v_out_rs dbms_tf.row_set_t; v_env dbms_tf.env_t := dbms_tf.get_env(); v_colcnt_in pls_integer; v_rowcnt_in pls_integer; repfac dbms_tf.tab_naturaln_t; -- массив флагов удален/неуадлен для всех строк v_name_field_idx pls_integer; v_value_field_idx pls_integer; v_key_idx pls_integer := 1; v_col_name varchar2(256 char); v_cur_row_name varchar2(256 char); v_found boolean; begin -- получаем значения из стора - используем DBMS_TF.CStore_Get для чтения данных, записанных в функции DESCRIBE DBMS_TF.CStore_Get('name_field_idx', v_name_field_idx); DBMS_TF.CStore_Get('value_field_idx', v_value_field_idx); /* DBMS_TF.XStore_Get - если потребуется хранение и передача данных между разными вызовами FETCH_ROWS */ -- получаем исходные данные для тех полей, которые помечены как for_read dbms_tf.get_row_set(v_inp_rs, v_rowcnt_in, v_colcnt_in); -- по умолчанию все строки помечаем удаленными for i in 1 .. v_rowcnt_in loop repfac(i) := 0; end loop; -- и только первую строку сохраняем repfac(1) := 1; -- переносим значение ключевой колонки из входных данных в выходные (первая колонка: v_key_idx = 1) if v_env.put_columns(v_key_idx).type = dbms_tf.type_number then v_out_rs(v_key_idx).tab_number(1) := v_inp_rs(v_key_idx).tab_number(1); elsif v_env.put_columns(v_key_idx).type = dbms_tf.type_varchar2 then v_out_rs(v_key_idx).tab_varchar2(1) := v_inp_rs(v_key_idx).tab_varchar2(1); end if; -- формируем строку в итоговой таблице (начиная со следующей после ключевой колонки) for c in v_key_idx + 1 .. v_env.put_columns.count loop v_found := false; v_col_name := upper(trim(replace(v_env.put_columns(c).name, '"'))); for row_idx in 1 .. v_rowcnt_in loop exit when v_found; v_cur_row_name := upper(trim(v_inp_rs(v_name_field_idx).tab_varchar2(row_idx))); -- заполняем значения для новых полей if v_cur_row_name = v_col_name then v_found := true; v_out_rs(c).tab_varchar2(1) := v_inp_rs(v_value_field_idx).tab_varchar2(row_idx); end if; end loop; if not v_found then -- значение для новых полей обязательно заполняем, иначе PTF заглючит, но ошибку не выдаст!! -- это связано с тем, что где-то внутри механизма PTF выскакивет ошибка NO_DATA_FOUND, но наверх ошибка не райзится v_out_rs(c).tab_varchar2(1):= null; end if; end loop; -- вставляем строку в набор выходных данных DBMS_TF.Put_Row_Set(v_out_rs, repfac); end; end ptf_package_4; /
И результат работы функции:
По-прежнему самое интересное здесь в DESCRIBE и FETCH_ROWS. Важно помнить: DESCRIBE у нас выполняется один раз при компиляции SQL-запроса, т.е. для каждого SQL_ID функция DESCRIBE выполняется один единственный раз, в то время как FETCH_ROWS выполняется при каждом SQL-запросе с участием данной PTF-функции.
Небольшая ремарка: до сих пор мы рассматривали только строковые семантики, а в данном случае, обратите внимание, параметр table не row_polymorphic как в предыдущих случаях, а table_polymorphic – это как раз означает, что это табличная семантика и если посмотреть на запрос, то там указано PARTITION BY CODE.
Для PTF с табличной семантикой нам обязательно надо это учитывать, т.к. если мы указываем, что это табличная семантика, но при этом не указываем PARTITION BY CODE или ORDER BY, она будет работать неправильно и выдавать некорректную информацию.
Каким образом мы добились того, что из четырех строк исходной таблицы мы получили одну единственную строчку?
Рассмотрим детально как это сделано и как это работает.
Мы должны указать системе, что в процессе выполнения процедуры FETCH_ROWS из той пачки строк, которая нам пришла (данные нам приходят пачками, т.к. мы здесь указали PARTITION BY CODE), все строки, кроме первой, удаляем и все колонки удаляем и добавляем новые колонки со всеми нужными атрибутами.
Сначала мы указываем, что значения всех колонок нам нужны для чтения без автоматического прокидывания из исходной таблицы в финальный набор (мы руками будем формировать значения всех колонок).
Далее мы запоминаем порядковые номера полей с названием и значением атрибута, т.е. мы записываем их в отдельные переменные, которые в дальнейшем с помощью механизма работы со store передаем в процедуру FETCH_ROWS.
Соответственно, мы все колонки из этого списка добавляем в результат работы функции DESCRIBE как новые колонки.
Итого DESCRIBE у нас выполнил свои задачи: мы указали FOR_READ = true для каждой колонки, т.е. не прокидываем ни одну колонку, зато все колонки открываем для чтения в FETCH_ROWS и указываем, какие новые колонки у нас будут в нашей результирующей таблице.
Идем в процедуру FETCH_ROWS.
Что здесь у нас происходит? Во-первых, мы получаем из store сохраненные значения, индексы полей с названием и значением ��трибутов. Хочу заметить, что есть также возможность передачи данных не только между DESCRIBE и FETCH_ROWS, но и между несколькими вызовами этого FETCH_ROWS, т.е. если нам необходимо сделать какие-то накопительные вычисления, агрегации, то с помощью XStore_Get мы можем передавать значения между разными вызовами FETCH_ROWS.
После прочтения номеров полей из store начинается самое интересное: мы все строки, кроме первой, пометили к удалению, т.е. у нас есть специальная переменная – это массив флагов “удален / не удален” для строк. По сути в этой коллекции будет столько элементов, сколько у нас строк обработки в данном вызове FETCH_ROWS.
Далее мы сначала заполняем значение ключевой колонки, т.е. значение поля CODE, а потом в цикле значения всех атрибутов записываем соответствующие поля строки.
Важный момент. Нам обязательно нужно присвоить новым колонкам либо какие-то вычисленные значения, либо, если мы не смогли вычислить ничего, обязательно указать null. Иначе это приведет к проблемам. Я это сейчас продемонстрирую.
Исключаем код, который заполняет колонку с невычисленными значениями:
И получаем результат:
То есть, если мы исключим из логики код, который заполняет колонку с невычисленными значениями, получаем ерунду. Это связано со следующим: где-то внутри логики, внутри реализации самого PTF выскакивает ошибка NO_DATA_FOUND, но до нас этот NO_DATA_FOUND, этот exception не доходит. Он выскакивает внутри и там же перехватывается, причем перехватывается так, что мы никаких ошибок не видим, видим только печальный результат.
Вернемся к примеру. Мы заполнили все поля и далее с помощью DBMS_TF.Put_Row_Set вставляем набор строк и вторым параметром указываем нашу коллекцию, в которой перечислены флаги с удаленными/неудаленными строками. В нашем случае, напоминаю еще раз, у нас пришла пачка строк. Мы фактически всем этим строкам указываем, что они удаленные, оставляем только первую строчку. Её наполняем уже своими новыми колонками и в результате получаем одну строчку вместо всей пачки строк.
Несколько слов про возможные ошибки при выполнении SQL с PTF:
нужно быть внимательными с параметрами, т.к. при выполнении SQL могут возникать самые неожиданные ошибки: например, я несколько раз получал ошибки типа
“ORA-03 113: end‑of‑file on communication channel”;если внутри
FETCH_ROWSвозникнетNO_DATA_FOUND, то есть высокая вероятность, что ошибка будет подавлена и вы получите некорректные данные;если параметры процедур не соответствуют правилам, вылезет ошибка
"ORA-62573: new column (…) is not allowed with describe only polymorphic table function".
Сравнение производительности по сравнению с обычным PIVOT и JavaScript
Я тестировал PTF на следующих данных:
И что мы получили? PTF сильно уступает. Значительные потери производительности возникают на уровне большого объема данных.
В целом можно сказать, что у PTF есть какая-то своя ниша, но нужно хорошо думать нужно вам это использовать или нет.
Где ее можно реально использовать? В таких задачах, где мы обязаны на стороне базы выполнить динамический разворот из вертикальной структуры в горизонтальную (именно на стороне базы). Результирующий набор колонок нам при этом заранее неизвестен – это динамический момент. Только в таком случае есть смысл использовать PTF, в других он себя совершенно не оправдывает, учитывая, что есть разные неожиданные ошибки.
На нескольких форумах я также встречал описания бредовых не только ошибок, но и результатов, связанных с какой-то хитрой внутренней логикой, до понимания которой никто не доходил. Из всего этого можно сделать вывод, что технология пока сырая. Следовательно, без острой нужды к PTF можно не обращаться.