oracle 如何使用 EXECUTE IMMEDIATE 获取记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/891919/
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
How to get a record using EXECUTE IMMEDIATE?
提问by engro
I have a bunch of functions with signatures like:
我有一堆带有签名的函数，例如：
FUNCTION func1 (par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec;
and I have a function for retrieving data from this bunch of functions:
我有一个从这组函数中检索数据的函数：
FUNCTION get_result (func_name IN VARCHAR2, par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec;
IS
rec1 my_rec;
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || func_name || '(:par1, :par2) FROM DUAL'
INTO rec1
USING IN par1, IN par2;
RETURN rec1;
END;
but this code fails with ORA-01007 'variable not in select list'.
How can I rewrite statement?
但此代码因 ORA-01007 '变量不在选择列表中'而失败。
我怎样才能重写语句？
采纳答案by Tony Andrews
It all works fine for me (Oracle 10G), one I correct the syntax error in your function definition (the unwanted semi-colon on the first line):
这一切对我来说都很好（Oracle 10G），我更正了函数定义中的语法错误（第一行不需要的分号）：
SQL> create type my_rec is object (id integer, name varchar2(30))
2 /
Type created.
SQL> create FUNCTION func1 (par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec
2 is
3 l_rec my_rec := my_rec (1, 'x');
4 begin
5 return l_rec;
6 end;
7 /
Function created.
SQL> CREATE OR REPLACE
2 FUNCTION get_result (func_name IN VARCHAR2, par1 IN VARCHAR2, par2 IN NUMBER) RETURN my_rec
3 IS
4 rec1 my_rec;
5 BEGIN
6 EXECUTE IMMEDIATE 'SELECT ' || func_name || '(:par1, :par2) FROM DUAL'
7 INTO rec1
8 USING IN par1, IN par2;
9 RETURN rec1;
10 END;
11 /
Function created.
SQL> select get_result ('func1',1,2) from dual;
GET_RESULT('FUNC1',1,2)(ID, NAME)
-------------------------------------------------
MY_REC(1, 'x')
回答by ATorras
I think you can concatenate to the query like you did with func_name.
我认为您可以像使用func_name一样连接到查询。
