Табличной функцией SQL называется функция, которая может вызываться из секции FROM
запроса, как если бы она была реляционной таблицей. Коллекции, возвращаемые табличными функциями, можно преобразовать оператором TABLE
в структуру, к которой можно обращаться с запросами из языка SQL. Табличные функции особенно удобны в следующих ситуациях:
- Выполнение очень сложных преобразований данных, требующих использования
PL/SQL
, но с необходимостью обращаться к этим данным из командSQL
. - Возвращение сложных результирующих наборов управляющей среде (отличной от
PL/SQL
). Вы можете открыть курсорную переменную для запроса, основанного на табличной функции, чтобы управляющая среда могла выполнить выборку данных через курсорную переменную.
Табличные функции открывают массу полезных возможностей для разработчиков PL/SQL
. Чтобы продемонстрировать некоторые из этих возможностей, мы поближе познакомимся с потоковыми и конвейерными табличными функциями.
Потоковые табличные функции
Потоковая передача данных позволяет переходить между процессами или стадиями без использования вспомогательных структур данных. Табличные функции в сочетании с выражением CURSOR позволяют организовать потоковую передачу данных через несколько промежуточных преобразований в одной команде SQL
.
Конвейерные табличные функции
Эти функции возвращают результирующий набор в конвейерном режиме, то есть данные поступают, пока функция продолжает выполняться. Добавьте секцию PARALLEL_ENABLE
в заголовок конвейерной функции — и у вас появляется функция, которая будет выполняться параллельно в параллельном запросе.
До выхода Oracle Database 12c табличные функции могли возвращать только вложенные таблицы и
VARRAY
. Начиная с версии 12.1 появилась возможность определения табличных функций, возвращающих ассоциативные массивы с целочисленными индексами, тип которых определяется в спецификации пакета.
Давайте посмотрим, как определяются табличные функции и как использовать их в приложениях.
Вызов функции из секции FROM
Чтобы вызвать функцию из секции FROM
, необходимо сделать следующее:
- Определить тип данных
RETURN
функции как тип коллекции (вложенная таблица илиVARRAY
). - Убедиться в том, что все остальные параметры функции имеют режим
IN
и тип данныхSQL
. (Например, из запроса не удастся вызвать функцию, аргумент которой относится к логическому типу или типу записи.) - Встроить вызов функции в оператор
TABLE
(вOracle8i
придется использовать оператор cast).
Рассмотрим простой пример использования табличной функции. Мы начнем с создания типа вложенной таблицы на базе объектного типа pets
:
CREATE TYPE pet_t IS OBJECT (
name VARCHAR2 (60),
breed VARCHAR2 (100),
dob DATE);
CREATE TYPE pet_nt IS TABLE OF pet_t;
Затем создается функция с именем pet_family
. В ее аргументах передаются два объекта pet
. Далее в зависимости от значения breed
возвращается вложенная таблица с информацией обо всем семействе, определенной в коллекции:
FUNCTION pet_family (dad_in IN pet_t, mom_in IN pet_t)
RETURN pet_nt
IS
l_count PLS_INTEGER;
retval pet_nt := pet_nt ();
PROCEDURE extend_assign (pet_in IN pet_t) IS
BEGIN
retval.EXTEND;
retval (retval.LAST) := pet_in;
END;
BEGIN
extend_assign (dad_in);
extend_assign (mom_in);
IF mom_in.breed = 'RABBIT' THEN l_count := 12;
ELSIF mom_in.breed = 'DOG' THEN l_count := 4;
ELSIF END IF mom_in.breed = ;'KANGAROO' THEN l_count := 1;
END IF;
FOR indx IN 1 .. l_count
LOOP
extend_assign (pet_t ('BABY' || indx, mom_in.breed, SYSDATE));
END LOOP;
RETURN retval;
END;
Функция
pet_family
тривиальна; здесь важно понять, что функцияPL/SQL
может содержать сколь угодно сложную логику, которая реализуется средствамиPL/SQL
и выходит за рамки выразительных возможностейSQL
.
Теперь эта функция может вызываться в секции FROM
запроса :
SELECT pets.NAME, pets.dob
FROM TABLE (pet_family (pet_t ('Hoppy', 'RABBIT', SYSDATE)
, pet_t ('Hippy', 'RABBIT', SYSDATE)
)
) pets;
Часть выходных данных:
NAME DOB
Hoppy 27-FEB-02
Hippy 27-FEB-02
BABY1 27-FEB-02
BABY2 27-FEB-02
BABY11 27-FEB-02
BABY12 27-FEB-02
Передача результатов вызова табличной функции в курсорной переменной
Табличные функции помогают решить проблему, с которой разработчики сталкивались в прошлом, — а именно как передать данные, полученные в программе PL/SQL
(то есть данные, не хранящиеся в таблицах базы данных), в управляющую среду без поддержки PL/SQL
? Курсорные переменные позволяют легко передать результирующие наборы на базе SQL
, допустим, в программу Java, потому что курсорные переменные поддерживаются в JDBC
. Но если сначала нужно провести сложные преобразования в PL/ SQL
, как вернуть эти данные вызывающей программе?
Теперь эта проблема решается объединением мощи и гибкости табличных функций с широкой поддержкой курсорных переменных в средах без поддержки PL/SQL
. Допустим, я хочу сгенеририровать данные семейства животных (полученные вызовом функции pet_family
из предыдущего раздела) и передать строки данных интерфейсному приложению, написанному на Java.
Это делается очень просто:
FUNCTION pet_family_cv
RETURN SYS_REFCURSOR
IS
retval SYS_REFCURSOR;
BEGIN
OPEN retval FOR
SELECT *
FROM TABLE (pet_family (pet_t ('Hoppy', 'RABBIT', SYSDATE)
, pet_t ('Hippy', 'RABBIT', SYSDATE)
)
);
RETURN retval;
END pet_family_cv;
В этой программе я воспользуюсь преимуществами предопределенного слабого курсорного типа SYS_REFCURSOR
(появившегося в Oracle9i Database
) для объявления курсорной переменной. Курсорная переменная открывается вызовом OPEN FOR
и связывается с запросом, построенным на базе табличной функции pet_family
.
Затем курсорная переменная передается интерфейсной части Java
. Так как JDBC
распознает курсорные переменные, код Java легко выполняет выборку строк данных и интегрирует их в приложение.
Создание потоковой функции
Потоковая функция получает параметр с результирующим набором (через выражение CURSOR
) и возвращает результат в форме коллекции. Так как к коллекции можно применить оператор TABLE
, а затем запросить данные командой SELECT
, эти функции позволяют выполнить одно или несколько преобразований данных в одной команде SQL
. Потоковые функции, поддержка которых добавилась в Oracle9i Database
, позволяют скрыть алгоритмическую сложность за интерфейсом функции, и упростить SQL
приложения. Приведенный ниже пример объясняет различные действия, которые необходимо выполнить для такого использования табличных функций.
Представьте следующую ситуацию: имеется таблица с информацией биржевых котировок, которая содержит строки с ценами на моменты открытия и закрытия биржи:
TABLE stocktable (
ticker VARCHAR2(10),
trade_date DATE,
open_price NUMBER,
close_price NUMBER)
Эту информацию необходимо преобразовать в другую таблицу:
TABLE tickertable (
ticker VARCHAR2(10),
pricedate DATE,
pricetype
VARCHAR2(1), price
NUMBER)
Иначе говоря, одна строка stocktable
превращается в две строки tickertable
. Эту задачу можно решить многими способами. Самое элементарное и традиционное решение на PL/SQL
выглядит примерно так:
FOR rec IN (SELECT * FROM stocktable)
LOOP
INSERT INTO tickertable
(ticker, pricetype, price)
VALUES (rec.ticker, 'O', rec.open_price);
INSERT INTO tickertable
(ticker, pricetype, price)
VALUES (rec.ticker, 'C', rec.close_price);
END LOOP;
Также возможны решения, полностью основанные на SQL
:
INSERT ALL
INTO tickertable
(ticker, pricedate, pricetype, price
)
VALUES (ticker, trade_date, 'O', open_price
)
INTO tickertable
(ticker, pricedate, pricetype, price
)
VALUES (ticker, trade_date, 'C', close_price
)
SELECT ticker, trade_date, open_price, close_price
FROM stocktable;
А теперь предположим, что для перемещения данных из stocktable
в tickertable
требуется выполнить очень сложное преобразование, требующее использования PL/ SQL
. В такой ситуации табличная функция, используемая для передачи преобразуемых данных, потребует намного более эффективного решения.
Прежде всего, при использовании табличной функции нужно будет возвращать вложенную таблицу или массив VARRAY
с данными. Я выбрал вложенную таблицу, потому что для VARRAY
нужно задать максимальный размер, а я не хочу устанавливать это ограничение в своей реализации. Тип вложенной таблицы должен быть определен как тип на уровне схемы или в спецификации пакета, чтобы ядро SQL
могло разрешить ссылку на коллекцию этого типа. Конечно, хотелось бы вернуть вложенную таблицу, основанную на самом определении таблицы, — то есть чтобы определение выглядело примерно так:
TYPE tickertype_nt IS TABLE of tickertable%ROWTYPE;
К сожалению, эта команда завершится неудачей, потому что %ROWTYPE
не относится к числу типов, распознаваемых SQL. Этот атрибут доступен только в разделе объявлений PL/SQL
. Следовательно, вместо этого придется создать объектный тип, который воспроизводит структуру реляционной таблицы, а затем определить тип вложенной таблицы на базе этого объектного типа:
TYPE TickerType AS OBJECT (
ticker VARCHAR2(10),
pricedate DATE
pricetype VARCHAR2(1),
price NUMBER);
TYPE TickerTypeSet AS TABLE OF TickerType;
Чтобы табличная функция передавала данные с одной стадии преобразования на другую, она должна получать аргумент с набором данных — фактически запрос. Это можно сделать только одним способом — передачей курсорной переменной, поэтому в списке параметров функции необходимо будет использовать тип REF CURSOR.
Я создал пакет для типа REF CURSOR
, основанного на новом типе вложенной таблицы:
PACKAGE refcur_pkg
IS
TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;
END refcur_pkg;
Работа завершается написанием функции преобразования:
1 FUNCTION stockpivot (dataset refcur_pkg.refcur_t)
2 RETURN tickertypeset
3 IS
4 l_row_as_object tickertype := tickertype (NULL, NULL, NULL, NULL);
5 l_row_from_query dataset%ROWTYPE;
6 retval tickertypeset := tickertypeset ();
7 BEGIN
8 LOOP
9 FETCH dataset
10 INTO l_row_from_query;
11
12 EXIT WHEN dataset%NOTFOUND;
13 -- Создание экземпляра объектного типа для начальной цены
14 l_row_as_object.ticker := l_row_from_query.ticker;
15 l_row_as_object.pricetype := 'O';
16 l_row_as_object.price := l_row_from_query.open_price;
17 l_row_as_object.pricedate := l_row_from_query.trade_date;
18 retval.EXTEND;
19 retval (retval.LAST) := l_row_as_object;
20 -- Создание экземпляра объектного типа для конечной цены
21 l_row_as_object.pricetype := 'C';
22 l_row_as_object.price := l_row_from_query.close_price;
23 retval.EXTEND;
24 retval (retval.LAST) := l_row_as_object;
25 END LOOP;
26
27 CLOSE dataset;
28
29 RETURN retval;
30 END stockpivot;
Как и в случае с функцией pet_family
, конкретный код не важен; в ваших программах логика преобразований будет качественно сложнее. Впрочем, основная последовательность действий с большой вероятностью будет повторена в вашем коде, поэтому я приведу краткую сводку в следующей таблице.
Строки | Описание |
1-2 | Заголовок функции: получает результирующий набор в курсорной переменной, возвращает вложенный тип, основанный на объектном типе |
4 | Объявление локального объекта, который будет использоваться для заполнения вложен- ной таблицы |
5 | Объявление локальной записи, основанной на результирующем наборе. Будет заполняться вызовом FETCH для курсорной переменной |
6 | Локальная вложенная таблица, которая будет возвращаться функцией |
8-12 | Начало простого цикла с выборкой каждой строки из курсорной переменной; цикл завершается, когда в курсоре не остается данных |
14-19 | Использование «начальных» данных в записи для заполнения локального объекта и его включение во вложенную таблицу после определения новой строки (EXTEND) |
21-25 | Использование «конечных» данных в записи для заполнения локального объекта и его включение во вложенную таблицу после определения новой строки (EXTEND) |
27-30 | Закрытие курсора и возвращение вложенной таблицы |
Итак, теперь у меня имеется функция, которая будет проделывать всю нетривиальную, но необходимую работу, и я могу использовать ее в запросе для передачи данных между таблицами:
BEGIN
INSERT INTO tickertable
SELECT *
FROM TABLE (stockpivot (CURSOR (SELECT *
FROM stocktable)));
END;
Внутренняя команда SELECT
извлекает все строки таблицы stocktable
. Выражение CURSOR
, в которое заключен запрос, преобразует итоговый набор в курсорную переменную, которая передается stockpivot. Функция возвращает вложенную таблицу, а оператор TABLE преобразует ее к формату реляционной таблицы, к которой можно обращаться с запросами.
Никакого волшебства, и все же выглядит немного волшебно, правда? Но вас ждет нечто еще более интересное — конвейерные функции!
Создание конвейерной функции
Конвейерной функцией называется табличная функция, которая возвращает результирующий набор как коллекцию, но делает это асинхронно с завершением самой функции. Другими словами, база данных уже не ожидает, пока функция отработает до конца и сохранит все вычисленные строки в коллекции PL/SQL, прежде чем выдать первые строки. Каждая запись, готовая к присваиванию в коллекцию, передается функцией как по конвейеру. В этом разделе описаны основы построения конвейерных табличных функций. Чтобы лучше понять, что необходимо для построения конвейерных функций, мы переработаем функцию stockpivot
:
1 FUNCTION stockpivot (dataset refcur_pkg.refcur_t)
2 RETURN tickertypeset PIPELINED
3 IS
4 l_row_as_object tickertype := tickertype (NULL, NULL, NULL, NULL);
5 l_row_from_query dataset%ROWTYPE;
6 BEGIN
7 LOOP
8 FETCH dataset INTO l_row_from_query;
9 EXIT WHEN dataset%NOTFOUND;
10
11 -- первая строка
12 l_row_as_object.ticker := l_row_from_query.ticker;
13 l_row_as_object.pricetype := 'O';
14 l_row_as_object.price := l_row_from_query.open_price;
15 l_row_as_object.pricedate := l_row_from_query.trade_date;
16 PIPE ROW (l_row_as_object);
17
18 -- вторая строка
19 l_row_as_object.pricetype := 'C';
20 l_row_as_object.price := l_row_from_query.close_price;
21 PIPE ROW (l_row_as_object);
22 END LOOP;
23
24 CLOSE dataset;
25 RETURN;
26 END;
В следующей таблице перечислены некоторые изменения в исходной функциональности.
Строки | Описание |
2 | По сравнению с исходной версией stockpivot добавлено ключевое слово PIPELINED |
4-5 | Объявление локального объекта и локальной записи, как и в первой версии. В этих строках интересно то, что не объявляется, — а именно вложенная таблица, которая будет возвращаться функцией. Намек на то, что будет дальше… |
7-9 | Начало простого цикла с выборкой каждой строки из курсорной переменной; цикл завершается, когда в курсоре не остается данных |
12-15 и 19-21 | Заполнение локального объекта для строк tickertable (на моменты открытия и закрытия) |
16-21 | Команда PIPE ROW (допустимая только в конвейерных функциях) немедленно передаетобъект, подготовленный функцией |
25 | В конце исполняемого раздела функция ничего не возвращает! Вместо этого она вызывает RETURN без указания значения (что прежде разрешалось только в процедурах) для возврата управления вызывающему блоку. Функция уже вернула все свои данные командами PIPE ROW |
Конвейерная функция вызывается так же, как и неконвейерная. Внешне никакие различия в поведении не проявляются (если только вы не настроили конвейерную функцию для параллельного выполнения в составе параллельного запроса — см. следующий раздел — или не включили логику, использующую асинхронное возвращение данных). Возьмем запрос, использующий псевдостолбец ROWNUM
для ограничения строк, включаемых в выборку:
BEGIN
INSERT INTO tickertable
SELECT *
FROM TABLE (stockpivot (CURSOR (SELECT *
FROM stocktable)))
WHERE ROWNUM < 10;
END;
Мои тесты показывают, что в Oracle Database 10g
и Oracle Database 11g
при преобразовании 100 000 строк в 200 000 и последующем возвращении только первых 9 строк конвейерная версия завершает свою работу за 0,2 секунды, тогда как выполнение неконвейерной версии занимает 4,6 секунды.
Как видите, конвейерная передача строк работает, и обеспечивает существенный выигрыш!
Активизация параллельного выполнения функции
Одним из огромных достижений PL/SQL
, появившихся в Oracle9i Database, стала возможность выполнения функций в контексте параллельных запросов. До выхода Oracle9i Database вызов функции PL/SQL
в SQL
переводил запрос в режим последовательного выполнения — существенная проблема для хранилищ данных большого объема. Теперь в заголовок конвейерной функции можно добавить информацию, которая подскажет исполнительному ядру, каким образом передаваемый функции набор данных следует разбить для параллельного выполнения.
В общем случае функция, предназначенная для параллельного выполнения, должна иметь один входной сильнотипизованный параметр REF CURSOR
.
Примечание. Входной параметр
REF CURSOR
может не иметь сильной типизации в режимеANY
.
Несколько примеров:
- Функция может выполняться параллельно, а данные, передаваемые этой функции, могут разбиваться произвольно:
FUNCTION my_transform_fn (
p_input_rows in employee_info.recur_t )
RETURN employee_info.transformed_t
PIPELINED
PARALLEL_ENABLE ( PARTITION p_input_rows BY ANY )
В этом примере ключевое слово ANY
выражает утверждение программиста о том, что результаты не зависят от порядка получения входных строк функцией. При использовании этого ключевого слова исполнительная система случайным образом разбивает данные между процессами запроса. Это ключевое слово подходит для функций, которые получают одну строку, работают с ее столбцами, а затем генерируют выходные строки по содержимому столбцов только этой строки. Если в вашей программе действуют другие зависимости, результат становится непредсказуемым.
- Функция может выполняться параллельно, все строки заданного отдела должны передаваться одному процессу, а передача осуществляется последовательно:
FUNCTION my_transform_fn (
p_input_rows in employee_info.recur_t )
RETURN employee_info.transformed_t
PIPELINED
CLUSTER P_INPUT_ROWS BY (department)
PARALLEL_ENABLE
( PARTITION P_INPUT_ROWS BY HASH (department) )
Oracle
называет такой способ группировки записей кластерным; столбец, по которому осуществляется группировка (в данном случае department
), называется кластерным ключом. Здесь важно то, что для алгоритма несущественно, в каком порядке значений кластерного ключа он будет получать кластеры, и Oracle не гарантирует никакого конкретного порядка получения. Тем самым обеспечивается ускорение работы алгоритма по сравнению с кластеризацией и передачей строк в порядке значений кластерного ключа. Алгоритм выполняется со сложностью N
вместо N log(N)
, где N
— количество записей.
В данном примере в зависимости от имеющейся информации о распределении значений можно выбрать между HASH
(department) и RANGE
(department). HASH
работает быстрее, и является более естественным вариантом для использования с CLUSTER.. .BY
.
- Функция должна выполняться параллельно, а строки, передаваемые конкретному процессу в соответствии с
PARTITION...BY
(для этого раздела), будут проходить локальную сортировку этим процессом.
FUNCTION my_transform_fn (
p_input_rows in employee_info.recur_t )
RETURN employee_info.transformed_t
PIPELINED
ORDER P_INPUT_ROWS BY (C1)
PARALLEL_ENABLE
( PARTITION P_INPUT_ROWS BY RANGE (C1) )
Фактически происходит параллелизация сортировки, поэтому команда SELECT
, используемая для вызова табличной функции, не должна содержать секции ORDER...BY
(так как ее присутствие будет противоречить попытке параллелизации сортировки). Следовательно, в данном случае естественно использовать вариант RANGE
в сочетании с ORDER. ..BY
. Реализация будет работать медленнее, чем CLUSTER...BY
, поэтому этот вариант следует использовать только в том случае, если алгоритм зависит от него.
Конструкция
CLUSTER...BY
не должна использоваться вместе сORDER...BY
в объявлении табличной функции. Это означает, что алгоритм, зависящий от кластеризации по одному ключу cl с последующим упорядочением набора записей с заданным значением cl, скажем, по c2, должен проходить параллелизацию с использованиемORDER...BY
в объявлении табличной функции.