oracle PL/SQL - 在流水线函数中立即执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12578907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL - execute immediate in pipelined function
提问by dzb
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.
我想在我的流水线函数中执行动态查询并返回此查询的结果。是否有可能做到这一点？流水线函数方便我为我的应用程序实现良好的界面，因为它的行为就像一个表格。
The function:
功能：
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;
回答by Vincent Malgrat
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.
可以结合动态 SQL 和流水线函数，但返回类型不会是动态的：返回的列的数量和类型将是固定的。
You can use EXECUTE IMMEDIATEwith BULK COLLECT(thanks @be here now), dynamic cursorsor DBMS_SQLto return more than one row. Here's an example with a dynamic cursor:
您可以使用EXECUTE IMMEDIATEwith BULK COLLECT（感谢@be here now）、动态游标或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.
Let's call this dynamic function:
让我们调用这个动态函数：
SQL> SELECT *
2 FROM TABLE(pkg.dynamic_cursor('id <= 2'));
ID DAT
---------- ---
1 xxx
2 xxx
As always with dynamic SQL, beware of SQL Injection.
与动态 SQL 一样，请注意SQL 注入。
回答by Rene
I think something like this:
我认为是这样的：
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;
Works only if v_query returns 1 row.
仅当 v_query 返回 1 行时才有效。
回答by EdHayes3
I couldn't get @VincentMalgrat's answer to work. But it was very close. Definitely a big help in the right direction for me.
我无法得到@VincentMalgrat 的工作答案。但它非常接近。对我来说，在正确的方向上绝对是一个很大的帮助。
Here's what I got to work:
这是我的工作：
Package
包裹
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;
Package Body
包体
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;
