CftClub.ru
Клуб специалистов ЦФТ-Банк

Табличные и pipelined функции

 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle
Предыдущая тема :: Следующая тема  
Автор Сообщение
De Mian
Профи


Вступление в Клуб: 26.09.2008
СообщениеЧт Авг 04, 2011 18:12   Табличные и pipelined функции Ответить с цитатой
Полезность: Нет оценки
Есть табличная функция возвращающая коллекцию. У функции например 1 параметр
Как далее эту функцию вызвать в SQL-конструкции в PL/Plus ?
Если функция не имеет параметров, то работает. С параметрами выдаёт ошибку компиляции.
Примечательно то, что если всё же функция имеет параметр, но в запросе к ней идёт обращение без параметров то PL\PLUS компилируется успешно – разумеется PL\SQL это уже не пропускает.
Вот код который хочется получить:

Спецификация функции:
Код:
FUNCTION GET_DATA_BY_TABLE1(P#STR STRING) RETURN TYPE_MEMO_TABLE pipelined ;

Вызов функции:
Код:
FOR (SELECT y(y) IN GET_DATA_BY_TABLE1('asd,sdf,ghf'))
LOOP
   null;
END loop;

Ошибка:
PLP-NOT_COLLECTION:[GET_DATA_BY_TABLE1] не коллекция
PLP-INDEX:[GET_DATA_BY_TABLE1] не может иметь индексов
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеПт Авг 05, 2011 08:53    Ответить с цитатой
Полезность: Нет оценки
Небольшой, но полезный примерчик использования pipelined.
Функция парсинга маски счетов для подстановки в запрос с использованием индексированного доступа к счетам:

Глобальные описания:
Код:

type tVarchar2_varr is varray(0) of varchar2(4000);
function split_pl(p_str   varchar2, p_Delimiter varchar2, p_Range_Delimiter varchar2 default '-') return tVarchar2_varr pipelined;


Локальные описания:
Код:

function split_pl(p_str   varchar2, p_Delimiter varchar2, p_Range_Delimiter varchar2 default '-') return tVarchar2_varr is

v_Tmp_Arr   rtl.string_table;
begin
      
   v_Tmp_Arr := [RUNTIME]::[STR_2].split(p_str, p_Delimiter);
   for i in 1..v_Tmp_Arr.count
   loop
      -- Проверим ни интервал (Значения могут быть заданы следующим образом: 42301-42315. В таком случае генерим промежуточные значения)
      if instr(v_Tmp_Arr(i), p_Range_Delimiter) > 0  then
         declare
            v_Start_Value    number;
            v_End_Value    number;         
            --v_Range_Arr      tVarchar2;
            v_Range_Arr      rtl.string_table;
         begin
            -- Парсинг строки диапазона
            v_Range_Arr   := [RUNTIME]::[STR_2].split(trim_all(v_Tmp_Arr(i)), p_Range_Delimiter);
            
            -- Определин начальное и конечное значения
            v_Start_Value := to_number(regexp_substr(v_Range_Arr(1), '[[:digit:]]+'));
            v_End_Value := to_number(regexp_substr(v_Range_Arr(2), '[[:digit:]]+'));
            
            -- Цикл по диапазону
            for j in v_Start_Value..v_End_Value
            loop
               return j;
            end loop;
         end;
      else
         return trim_all(v_Tmp_Arr(i));
      end if;         
   end loop;
end;



Пример использования на PL+
Код:

begin
   for(
      select acc (acc.[MAIN_V_ID]  )
      in 
         ::[AC_FIN],
         ([RUNTIME]::[NOM_LIB].SPLIT_PL('40817, 42301-42303', ',', '-') : mask)
      where
          acc.[MAIN_V_ID] like mask.COLUMN_VALUE||'%'
   ) loop
      
   null;
   
   end loop;
end;


Пример использования pipelined-функции в PL/SQL Developer:
Код:

   1. Получение простого списка масок:
      select * from table(ibs.Z$RUNTIME_NOM_LIB.SPLIT_PL('45001-45009', ',', '-')) m
   
   2. Поиск счетов по маскам:
      select acc.*
      from   ibs.z#ac_fin acc,
             table(ibs.Z$RUNTIME_NOM_LIB.SPLIT_PL('42301-42307', ',', '-')) m
      where acc.c_main_v_id like m.column_value||'%'


Последний раз редактировалось: maestro (Пт Авг 05, 2011 09:00), всего редактировалось 1 раз
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеПт Авг 05, 2011 08:57    Ответить с цитатой
Полезность: Нет оценки
Пардон, De Mian, не посмотрел какой у вас Банк!
В Уралсибе на Ритейле точно работать не будет, т.к. версия ТЯ не позволяет использовать pipelined. На ибсятине, соответственно, тоже.

На ЦФТ-Банке можно попробовать!
De Mian
Профи


Вступление в Клуб: 26.09.2008
СообщениеПт Авг 05, 2011 09:36    Ответить с цитатой
Полезность: Нет оценки
maestro пишет:
Пардон, De Mian, не посмотрел какой у вас Банк!
В Уралсибе на Ритейле точно работать не будет, т.к. версия ТЯ не позволяет использовать pipelined. На ибсятине, соответственно, тоже.

На ЦФТ-Банке можно попробовать!


Да на ЦФТ-БАНК и пробую. но вопрос собственно не в pipeline,а в том как вызвать табличную функцию с параметрами в PL\PLUS.

Как писал без параметров всё работает. С параметрами не компилится
ТЯ 7.1.1.2
De Mian
Профи


Вступление в Клуб: 26.09.2008
СообщениеПт Авг 05, 2011 09:37    Ответить с цитатой
Полезность: Нет оценки
Обнаружил интересную вещь - если табличная функция с параметрами вызывается в том же пакете в котором эта функция описана то получаем такую ошибку.
Если же табличную функцию с параметрами описать в пакете A1, а использовать в A2, то всё компилируется нормально.
De Mian
Профи


Вступление в Клуб: 26.09.2008
СообщениеПт Авг 05, 2011 09:45    Ответить с цитатой
Полезность: Нет оценки
Пока нашёл такой выход как использовать табличную функцию с параметрами в том же пакете в котором она определена:
При расширенном синтаксисе вместо
Код:
in GET_DATA_BY_TABLE1('asd,sdf,ghf')

использовать
Код:
CAST({GET_DATA_BY_TABLE1('asd,sdf,ghf')},TYPE_MEMO_TABLE)
)

Код:
FOR (SELECT y(y) IN
CAST({GET_DATA_BY_TABLE1('asd,sdf,ghf')},TYPE_MEMO_TABLE))
LOOP
      null;
END loop;


в PL\SQL конечно получается избыточное и бессмысленное преобразование TYPE_MEMO_TABLE->TYPE_MEMO_TABLE, но для моей небольшой задачки(отчёт) это лучше чем плодить библиотеку к этому отчёту.
Код:

select  a1.COLUMN_VALUE COLUMN_VALUE
from table(
CAST(GET_DATA_BY_TABLE1('asd,sdf,ghf') as TYPE_MEMO_TABLE)
) a1;
maestro
Профи


Вступление в Клуб: 12.10.2010
СообщениеПт Авг 05, 2011 09:51    Ответить с цитатой
Полезность: Нет оценки
Цитата:
но вопрос собственно не в pipeline,а в том как вызвать табличную функцию с параметрами в PL\PLUS


Ну вобщем-то табличная функция и pipelined - одно и то же. С вызовом из PL+ никаких проблем, приведенной мною пример работает.

Возможно, проблема в типе TYPE_MEMO_TABLE. У меня на схеме такого нет. Как он задекларирован?
De Mian
Профи


Вступление в Клуб: 26.09.2008
СообщениеСр Авг 10, 2011 11:57    Ответить с цитатой
Полезность: Нет оценки
maestro пишет:
Возможно, проблема в типе TYPE_MEMO_TABLE. У меня на схеме такого нет. Как он задекларирован?

нет проблема не в TYPE_MEMO_TABLE
Про TYPE_MEMO_TABLE описанно в PLPHINTS.TXT :
3.3. SQL-типы для NESTED TABLES скалярных типов:
type TYPE_MEMO_TABLE is table of varchar2(4000)
3.4. Автоматическое переопределение типов NESTED TABLE в PL/Plus на SQL-типы:
TYPE name IS VARRAY(0) OF STRING{(size)} <--> TYPE_MEMO_TABLE
так что тип TYPE_MEMO_TABLE есть, только не в словаре ЦФТ-БАНК, а в словаре ORACLE в схеме IBS
De Mian
Профи


Вступление в Клуб: 26.09.2008
СообщениеСр Авг 10, 2011 11:59    Ответить с цитатой
Полезность: Нет оценки
В ЦФТ ответили:
Это действительно баг: на этапе компиляции в оракловом словаре нет информации о наличии данной операции и ее параметрах(некорректно анализируется параметры функции в ин листе), потому возникает такая ошибка.
Эта проблема ЦФТ-ой разработке уже известна и даже поправлена. Исправление выйдет в ТЯ 7.2.
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle Часовой пояс: GMT + 3
Страница 1 из 1

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru