PL/SQL-在流水线函数中立即执行 [英] PL/SQL - execute immediate in pipelined function

问题描述

我想在流水线函数中执行动态查询并返回此查询的结果. 是否有可能做到这一点? 流水线函数对于我的应用程序来说很方便，因为它的行为就像表一样，可以为我的应用程序提供良好的界面.

I want to execute dynamic query in my pipelined function and return results of this query. Is it possible to do this? Pipelined function is convenient for me to achieve good interface for my application cause it behaves like a table.

功能:

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;

推荐答案

可以将动态SQL和流水线函数结合在一起，但是返回类型不是动态的:返回的列的数量和类型将是固定的.

It is possible to combine dynamic SQL and pipelined function but the return type will not be dynamic: the number and type of columns returned will be fixed.

您可以使用 EXECUTE IMMEDIATE 使用BULK COLLECT(感谢 @be here now )，

You can use EXECUTE IMMEDIATE with BULK COLLECT (thanks @be here now), dynamic cursors or DBMS_SQL to return more than one row. Here's an example with a dynamic cursor:

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.

我们将此动态函数称为:

Let's call this dynamic function:

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

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

与动态SQL一样，要当心 SQL注入.

As always with dynamic SQL, beware of SQL Injection.

这篇关于PL/SQL-在流水线函数中立即执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！