将EXECUTE IMMEDIATE与多个相同的绑定参数一起使用 [英] using EXECUTE IMMEDIATE with multiple same bind arguments

问题描述

当我创建以下过程时

create or replace procedure check_exec_imm(
tab IN VARCHAR2,
col IN VARCHAR2,
col_name IN VARCHAR2

)

IS

 cv  SYS_REFCURSOR;
 col_value  VARCHAR2(32767);
 lv_query VARCHAR2(32767); 

 BEGIN
   lv_query := 'SELECT ' ||col||
           ' FROM ' ||tab||
           ' WHERE (:1 = ''EUR'' OR :1 = ''USD'') and rownum <=1';


    EXECUTE IMMEDIATE lv_query INTO col_value USING  col_name ;


DBMS_OUTPUT.PUT_LINE('COLUMN VALUE : ' || col_value);

END;

执行该过程时，出现以下错误

When the procedure is executed, I'm getting the following error

ORA-01008: not all variables bound
ORA-06512: at "GRM_IV.CHECK_EXEC_IMM", line 18
ORA-06512: at line 2

当我再次按如下所示给绑定参数col_name时，过程运行正常.

When I give the bind argument col_name again as below, the procedure is running fine.

EXECUTE IMMEDIATE lv_query INTO col_value USING  col_name, col_name ;

为什么oracle在此过程中的行为有所不同.因为它是相同的绑定变量，所以一个绑定参数就足够了.请说明我在哪里弄错了逻辑.

Why oracle is behaving differently in this procedure. Since, it is the same bind variable, one bind argument should be sufficient right..!!? Please explain where I'm getting my logic wrong.

推荐答案

Oracle中存在特殊"行为:

There is "special" behaviour in Oracle: Repeated Placeholder Names in Dynamic SQL Statements

在匿名块或CALL语句中，如果名称相等，则不需要重复绑定值. 例如，此匿名块正在运行:

In an Anonymous Block or CALL Statement it is not required to repeat the bind values if the names are equal. For example this Anonymous Block is working:

DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
BEGIN
  plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b;  -- calc_stats(a, a, b, a)
END;
/

但是，此EXECUTE IMMEDIATE plsql_block USING a, b;在程序内部中不起作用.

But this EXECUTE IMMEDIATE plsql_block USING a, b; does not work inside a Procedure.

这篇关于将EXECUTE IMMEDIATE与多个相同的绑定参数一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！