Можно ли сделать выборку из функции? Конвейерные (PIPELINED) табличные функции. Оператор PIPE ROW

dbstalker, 10 апреля

Довелось столкнуться с таким понятием как конвейерные функции. Хочу поделиться своими поверхностными знаниями по этому вопросу.

Сначала приведу пример:

Создаем табличный тип:

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;
    
    The following examples show declarations of the same table functions implemented using the native PL/SQL approach:
    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»

2 комментария

Прокоментировать

Донцов Денис
22 февраля 2009 г. в 14:19

Это скорее не комментарий а вопрос. Подскажите как вызвать конвейерную функцию в PL/SQL коде.

dbstalker
26 февраля 2009 г. в 16:55

begin
insert into temp_table SELECT * FROM TABLE (MY_FUNCTION('MY_TABLE')) where 1=1;
end;

 

Новый комментарий

Я не спамер: введите суму 9+4



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Микрофон
19 августа, 2 ответа
Сумочка
19 августа, 2 ответа
средства для рук
17 августа, 3 ответа