为什么我不能在立即执行语句中使用绑定变量？
当我为立即执行构建动态 SQL 语句时，我想使用绑定变量而不是字符串连接。
在下面的示例中，我可以对 a、b 和 ret 使用绑定变量，但是当我尝试绑定 f 我收到 ORA-06502：PL/SQL：数字或值错误：字符到数字转换错误。为什么以及如何绑定 f ？
我正在使用 11.2.0.1.0。
create or replace function so4fun (
a in number,
b in number,
f in varchar2
) return number as
decl constant varchar2(32767) :=
'declare a constant number := :a; b constant number := :b;';
stmt varchar2(32676);
ret number;
begin
/* This one works: */
stmt := decl || ' begin :result := ' || f || '; end;';
execute immediate stmt using in a, in b, out ret;
/* But why this one doesn't ?
stmt := decl || ' begin :result := :f; end;';
execute immediate stmt using in a, in b, out ret, in f;
This doesn't work either:
stmt := decl || ' tmp number; begin tmp := :f; :result := tmp; end;';
execute immediate stmt using in a, in b, in f, out ret;
Both are giving me ORA-06502: PL/SQL: numeric or value error: character to
number conversion error */
return ret;
end;
/
show errors
/* expected result when a = 1, b = 2 is 1.5 */
select so4fun(1, 2, '(a + b) / b') from dual;
drop function so4fun;
I'd like to use bind variables instead of a string concatenation when I build up a dynamic SQL statement for execute immediate.
In the example below I can use bind variables for a, b and ret, but when I try to bind for f I get a ORA-06502: PL/SQL: numeric or value error: character to number conversion error. Why and how I can bind also f ?
I'm using 11.2.0.1.0.
create or replace function so4fun (
a in number,
b in number,
f in varchar2
) return number as
decl constant varchar2(32767) :=
'declare a constant number := :a; b constant number := :b;';
stmt varchar2(32676);
ret number;
begin
/* This one works: */
stmt := decl || ' begin :result := ' || f || '; end;';
execute immediate stmt using in a, in b, out ret;
/* But why this one doesn't ?
stmt := decl || ' begin :result := :f; end;';
execute immediate stmt using in a, in b, out ret, in f;
This doesn't work either:
stmt := decl || ' tmp number; begin tmp := :f; :result := tmp; end;';
execute immediate stmt using in a, in b, in f, out ret;
Both are giving me ORA-06502: PL/SQL: numeric or value error: character to
number conversion error */
return ret;
end;
/
show errors
/* expected result when a = 1, b = 2 is 1.5 */
select so4fun(1, 2, '(a + b) / b') from dual;
drop function so4fun;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
绑定变量是为了绑定变量，而不是为了绑定代码片段。这个想法是，Oracle 可以编译和缓存查询或代码块，并使用不同的参数多次执行它。
但是，您尝试使用参数绑定来替换计算公式。它会阻止编译和缓存代码块，因此不受支持。
此外，它无法用当前语法来表达。如果 Oracle 看到
tmp := :f，它会认为您只是想将参数f分配给变量tmp。它不需要评估函数。只需使用可行的解决方案即可。毕竟它有效。
Bind variables are for binding variables, not for binding pieces of code. The idea is that Oracle can compile and cache a query or block of code and execute it several times with different parameters.
However, you try to use parameter binding for replacing the computed formula. It would prevent compiling and caching the block of code and is thus not supported.
Furthermore, it cannot be expressed with the current syntax. If Oracle sees
tmp := :fit thinks that you simple want to assign the parameterfto the variabletmp. It doesn't expect to have to evaluate a function.Just go with the working solution. It works after all.
发生错误的原因是您将
f声明为varchar2但您在注释掉的部分中写入了以下内容：您尝试将“character”值分配给需要数字的变量。您还尝试将
f分配给函数结果，该函数结果再次需要一个数字。使用
||一切正常，因为这是字符串连接。您需要以某种方式将 varchar2 转换为数字 (
TO_NUMBER(f))，或者编写您的过程以接受参数f作为数字而不是 varchar2。The error occurs because you declared
fas avarchar2but you have written in your commented-out section, the following:in which you try to assign the "character" value to a variable that expects a number. You also tried to assign
fto the function result which again expects a number.Things work fine with
||because that is string concatenation.You somehow need to convert your varchar2 to a number (
TO_NUMBER(f)), or perhaps, write your proc to accept the parameterfas a number rather than varchar2.