为什么我不能在立即执行语句中使用绑定变量? [英] Why I can't use a bind variable in an execute immediate statement?

问题描述

当我为execute immediate构建动态SQL语句时，我想使用绑定变量而不是字符串连接.

I'd like to use bind variables instead of a string concatenation when I build up a dynamic SQL statement for execute immediate.

在下面的示例中，我可以为a，b和ret使用绑定变量，但是当我尝试为f绑定时，得到ORA-06502: PL/SQL: numeric or value error: character to number conversion error.为什么也可以绑定f?

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 ?

我正在使用11.2.0.1.0.

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;

推荐答案

绑定变量用于绑定变量，而不用于绑定代码段.这个想法是Oracle可以编译和缓存查询或代码块，并使用不同的参数多次执行它.

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.

此外，它不能用当前语法表示.如果Oracle看到tmp := :f，则认为您只是想将参数f分配给变量tmp.它不需要评估一个函数.

Furthermore, it cannot be expressed with the current syntax. If Oracle sees tmp := :f it thinks that you simple want to assign the parameter f to the variable tmp. It doesn't expect to have to evaluate a function.

只需使用可行的解决方案即可.毕竟可以.

Just go with the working solution. It works after all.

这篇关于为什么我不能在立即执行语句中使用绑定变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！