10апр
Можно ли сделать выборку из функции? Конвейерные (PIPELINED) табличные функции. Оператор PIPE ROW.
Довелось столкнуться с таким понятием как конвейерные функции. Хочу поделиться своими поверхностными знаниями по этому вопросу.
Сначала приведу пример:
Создаем табличный тип:
CREATE TYPE datalist_type
AS OBJECT (zDate DATE)
/
CREATE OR REPLACE
TYPE datalist_TABLE
AS TABLE OF DataList_Type
/
Создаем функцию:
CREATE OR REPLACE FUNCTION MY_FUNCTION (table_name varchar2)
RETURN datalist_TABLE PIPELINED
IS
out_record DataList_Type:=DataList_Type(null);
TYPE MyType IS REF CURSOR ;
Date_ref MyType;
Date_m Date;
BEGIN
OPEN Date_ref FOR 'select distinct my_data from '||TABLE_NAME||' ';
LOOP
FETCH Date_ref INTO Date_m;
EXIT WHEN Date_ref%NOTFOUND;
out_record.zDate:=Date_m;
PIPE ROW(out_record);
END LOOP;
CLOSE Date_ref;
RETURN;
END;
/
Теперь вызываем созданную функцию:
SELECT * FROM TABLE (MY_FUNCTION('MY_TABLE'))
Что мы получаем в результате приведенных манипуляций? На входе имеем набор строк (ref cursor). На выходе – также набор строк (nested table), который является результатом выполнения некой функции.
Важно:
- использование в определении функции обязательного ключевого слова PIPELINED;
- The following example shows declarations of pipelined table functions implemented using the interface approach. The interface routines for functions GetBooks and StockPivot have been implemented in the types BookMethods and StockPivotImpl, respectively.
CREATE FUNCTION GetBooks(cat CLOB) RETURN BookSet_t PIPELINED USING BookMethods;
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t)
RETURN TickerTypeSet PIPELINED USING StockPivotImpl;
CREATE FUNCTION GetBooks(cat CLOB) RETURN BookSet_t PIPELINED IS ...;
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED IS...;
- использование в коде PL/SQL директивы pipe row , которая немедленно возвращает данные клиенту. То есть клиент получает данные из этой функции до того, как функция сгенерирует последнюю строку данных.
- В предложении RETURN определить тип возвращаемых функцией данных как коллекцию (вложенную таблицу или VARRAY)
- Необходимо указывать типы SQL, а не типы PLSQL. PLSQL - это надстройка над SQL, поэтому SQL и "не видит" типы PLSQL. Поэтому нужно создать типы SQL с помощью оператора create or replace type. (Том Кайт)
- Поместить вызовы функции в операторы TABLE и CAST
Начиная с Oracle 8i, существуют так называемые "табличные функции"(в том числе и конвейерные) возвращающие набор данных, который можно рассматривать, как реляционную таблицу в предложении FROM. Проще говоря, конвейерные функции – это просто код, с которым вы можете обращаться как с таблицей базы данных. Конвейерные функции позволяют вам использовать конструкции типа SELECT * FROM <ФУНКЦИЯ_PLSQL>.
Pipelined функции возвращают результат построчно (результат выдается не целиком, а как только получена первая запись, что иногда является преимуществом). Это свойство позволяет выстроить план, обрабатывающий полученный результат по частям. Конвейерную функцию можно распараллелить для обработки партиционированных данных. Табличные функции всегда требуют создания типа в SQL-подсистеме.
Посмотрите документацию «Pipelined and Parallel Table Functions»