PL/SQL - выполнить немедленно в конвейерной функции

Я хочу выполнить динамический запрос в моей конвейерной функции и вернуть результаты этого запроса. Можно ли сделать это? Конвейерная функция удобна для меня, чтобы получить хороший интерфейс для моего приложения, потому что он ведет себя как таблица.

Функция:

CREATE OR REPLACE FUNCTION MyFunction(p_schema VARCHAR2) RETURN MyTableType Pipelined IS
v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT * FROM TABLE ('||p_schema||'.somepackage.SomeFunction)'; --SomeFunction is another pipelined function
  EXECUTE IMMEDIATE v_query;
  --Results of the v_query are compatible with MyTableType's row type. But how to return them from pipelined function?
END;

oracle plsql execute-immediate pipelined-function
person dzb    schedule 25.09.2012    source источник

Ответы (3)


arrow_upward
3
arrow_downward

Можно комбинировать динамический SQL и конвейерную функцию, но тип возвращаемого значения не будет динамическим: количество и тип возвращаемых столбцов будут фиксированными.

Вы можете использовать EXECUTE IMMEDIATE с BULK COLLECT (спасибо @быть здесь и сейчас), динамические курсоры или DBMS_SQL для возврата более одной строки. Вот пример с динамическим курсором:

SQL> CREATE OR REPLACE PACKAGE pkg AS
  2     TYPE test_tab IS TABLE OF test%ROWTYPE;
  3     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED;
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
  2     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED IS
  3        cc sys_refcursor;
  4        l_row test%ROWTYPE;
  5     BEGIN
  6        OPEN cc FOR 'SELECT * FROM test WHERE ' || l_where;
  7        LOOP
  8           FETCH cc INTO l_row;
  9           EXIT WHEN cc%NOTFOUND;
 10           PIPE ROW (l_row);
 11        END LOOP;
 12        RETURN;
 13     END;
 14  END;
 15  /

Package body created.

Назовем эту динамическую функцию:

SQL> SELECT *
  2    FROM TABLE(pkg.dynamic_cursor('id <= 2'));

        ID DAT
---------- ---
         1 xxx
         2 xxx

Как всегда с динамическим SQL, остерегайтесь внедрения SQL.

person Vincent Malgrat    schedule 25.09.2012
comment
@dzb: DBMS_SQL не ускорит запрос по волшебству :), на самом деле накладных расходов немного больше, чем в собственном динамическом SQL. Есть так много причин, по которым ваш запрос может быть медленным, что я не могу понять, как улучшить его производительность с таким небольшим контекстом. Единственная разница между динамическим и статическим SQL заключается в использовании переменных. Используете ли вы параметризованный динамический SQL? - person Vincent Malgrat; 25.09.2012
comment
Спасибо за этот пример. Работает, но ужасно медленно. Я думаю, это потому, что я вызываю другую конвейерную функцию в своем запросе (я обновил свой вопрос), и похоже, что она вызывается слишком много раз. Не могли бы вы опубликовать какое-нибудь предложение для dbms_sql? - person dzb; 25.09.2012
comment
@ВинсентМальграт execute immediate ... bulk collect into ...; for i in col.first .. col.last loop pipe row () end loop; ? - person Kirill Leontev; 25.09.2012
comment
@dzb: Являются ли исходные конвейерные функции быстрее, чем функция-оболочка? - person Vincent Malgrat; 25.09.2012
comment
@beherenow Ты прав, мой плохой. По какой-то причине я думал, что вы не можете использовать BULK COLLECT с таблицей %ROWTYPE. Но мало того, что работает, так еще и для маленьких наборов будет быстрее :) - person Vincent Malgrat; 25.09.2012
comment
@ Винсент Мальграт: да, оригинальная функция намного быстрее. - person dzb; 25.09.2012
comment
@dzb Вы можете попробовать BULK COLLECT вместо построчного цикла. Улучшает ли это производительность? - person Vincent Malgrat; 25.09.2012
comment
@VincentMalgrat: BULK COLLECT работает, но все еще очень медленно. Я хочу иметь пейджинг моих результатов, который реализован в моем приложении. Но это не будет работать с этим подходом BULK COLLECT, потому что для каждой страницы он собирает ВСЕ результаты из внутренней функции. - person dzb; 25.09.2012
comment
@dzb есть предложение LIMIT с BULK COLLECT, который можно использовать для получения строк в пакетном режиме вместо полного набора данных. - person Vincent Malgrat; 25.09.2012
comment
@VincentMalgrat: я пытался, но он все еще извлекает все строки внутренней функции (сотни тысяч), когда я вызываю свою внешнюю функцию с rownum‹100 (например). - person dzb; 25.09.2012

arrow_upward
1
arrow_downward

Я думаю примерно так:

CREATE OR REPLACE FUNCTION MyFunction(par1 VARCHAR2, ...) RETURN MyTableType Pipelined IS
v_query VARCHAR2(1000);
l_result MyTableType;
BEGIN
  v_query := --My query created based on parameters
  EXECUTE IMMEDIATE v_query into l_result;

  pipe row(l_result);
END;

Работает, только если v_query возвращает 1 строку.

person Rene    schedule 25.09.2012

arrow_upward
0
arrow_downward

Я не мог заставить ответ @VincentMalgrat работать. Но это было очень близко. Определенно большая помощь в правильном направлении для меня.

Вот что у меня есть для работы:

Упаковка

 CREATE OR REPLACE PACKAGE pkg AS
     TYPE test_row IS RECORD  ( test_name  VARCHAR2 (255), test_number number, test_date date );  
     TYPE test_tab IS TABLE OF test_row;
     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED;
   END;

Тело пакета

 CREATE OR REPLACE PACKAGE BODY pkg IS
     FUNCTION dynamic_cursor(l_where VARCHAR2) RETURN test_tab PIPELINED IS
        cc sys_refcursor;
        l_row test_row;
     BEGIN
        OPEN cc FOR 'select name_column, number_column, date_column FROM my_table where number_column ='||l_where;
        LOOP
           FETCH cc INTO l_row;
           EXIT WHEN cc%NOTFOUND;
           PIPE ROW (l_row);
        END LOOP;
        RETURN;
     END;
  END;
person EdHayes3    schedule 29.03.2019