Табличные функции SQL для разработчиков PL/SQL на примере

Табличные функции SQL и PL/SQLТабличной функцией 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. (Например, из запроса не удастся вызвать функцию, аргумент которой относится к логическому типу или типу записи.)
  •  Встроить вызов функции в оператор TABLEOracle8i придется использовать опе­ратор 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 в объявлении табличной функции.

 

Вас заинтересует / Intresting for you:

Назначение языка SQL и необход...
Назначение языка SQL и необход... 1434 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:40:06
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3066 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6916 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5667 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать

Fasenger аватар
Fasenger ответил в теме #9298 25 нояб 2018 05:58
Табличные функции дают отличные перспективы для разработки кода на PL/SQL. Эту тематику должно отлично знать опытному программисту и грамотно применять на практике.