oracle 使用具有多个相同绑定参数的 EXECUTE IMMEDIATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21899011/
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
using EXECUTE IMMEDIATE with multiple same bind arguments
提问by ethan
When I create the following procedure
当我创建以下过程时
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
When I give the bind argument col_name again as below, the procedure is running fine.
当我如下再次给出绑定参数 col_name 时，程序运行良好。
EXECUTE IMMEDIATE lv_query INTO col_value USING col_name, col_name ;
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 在这个过程中表现不同。既然是同一个绑定变量，一个绑定参数应该就足够了吧..！！？请解释我的逻辑错误的地方。
回答by Wernfried Domscheit
There is "special" behaviour in Oracle: Repeated Placeholder Names in Dynamic SQL Statements
Oracle 中存在“特殊”行为：动态 SQL 语句中的重复占位符名称
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 Blockis working:
在匿名块或 CALL 语句中，如果名称相同，则不需要重复绑定值。例如这个匿名块正在工作：
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;
/
But this EXECUTE IMMEDIATE plsql_block USING a, b;does not work inside a Procedure.
但这在 Procedure 中EXECUTE IMMEDIATE plsql_block USING a, b;不起作用。
回答by SriniV
The way you have referenced the column name through bind variable is not a preferred method as Nichoas pointed out. What you tried is called as native dynamic SQL using 'cleverer' bind variables.
您通过绑定变量引用列名的方式不是 Nichoas 指出的首选方法。您尝试过的称为使用“更聪明”绑定变量的本机动态 SQL。
In this method, you need to bind every parameter X times since you use it X times because they are all treated as separate variables.
在此方法中，您需要将每个参数绑定 X 次，因为您使用了 X 次，因为它们都被视为单独的变量。
Read more on binding to dynamic SQL.
阅读有关绑定到动态 SQL 的更多信息。
