在 PL/SQL 中使用带有动态 SELECT INTO 子句的绑定变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7816402/
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 bind variables with dynamic SELECT INTO clause in PL/SQL
提问by BYS2
I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/SQL.
我有一个关于在 PL/SQL 中的动态 SQL 语句中可以在哪里使用绑定变量的问题。
For example, I know that this is valid:
例如，我知道这是有效的：
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'SELECT COUNT(*) FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job';
EXECUTE IMMEDIATE v_query_str
INTO v_num_of_employees
USING p_job;
RETURN v_num_of_employees;
END;
/
I was wondering if you could use a bind variables in a select statement like this
我想知道你是否可以在这样的 select 语句中使用绑定变量
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'SELECT COUNT(*) INTO :into_bind FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job';
EXECUTE IMMEDIATE v_query_str
USING out v_num_of_employees, p_job;
RETURN v_num_of_employees;
END;
/
Note I used a SELECT INTO statement as my dyamic string and used a bind variable in the INTO clause.
注意我使用 SELECT INTO 语句作为动态字符串，并在 INTO 子句中使用了绑定变量。
I am currently travelling right now and won't have access to my computer back at home for a few days, but this has been nagging me for a bit. Tried reading the PL/SQL reference but they don't have an example of a select like this.
我现在正在旅行，几天后不能在家里使用我的电脑，但这一直困扰着我。尝试阅读 PL/SQL 参考，但他们没有这样的选择示例。
Thanks
谢谢
采纳答案by user272735
No you can't use bind variables that way. In your second example :into_bindin v_query_stris just a placeholderfor value of variable v_num_of_employees. Your select into statement will turn into something like:
不，您不能那样使用绑定变量。在您的第二个示例中，:into_bindinv_query_str只是变量值的占位符v_num_of_employees。您的 select into 语句将变成如下内容：
SELECT COUNT(*) INTO FROM emp_...
because the value of v_num_of_employeesis nullat EXECUTE IMMEDIATE.
因为价值v_num_of_employees就是null在EXECUTE IMMEDIATE。
Your first example presents the correct way to bind the return value to a variable.
您的第一个示例展示了将返回值绑定到变量的正确方法。
Edit
编辑
The original poster has edited the second code block that I'm referring in my answer to use OUTparameter mode for v_num_of_employeesinstead of the default INmode. This modification makes the both examples functionally equivalent.
原始海报编辑了我在回答中提到的第二个代码块，以使用OUT参数模式v_num_of_employees而不是默认IN模式。此修改使两个示例在功能上等效。
回答by Aitor
In my opinion, a dynamic PL/SQL block is somewhat obscure. While is very flexible, is also hard to tune, hard to debug and hard to figure out what's up. My vote goes to your first option,
在我看来，动态 PL/SQL 块有些晦涩。虽然非常灵活，但也很难调整，难以调试，也很难弄清楚发生了什么。我的票投给了你的第一个选项，
EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job;
Both uses bind variables, but first, for me, is more redeable and tuneable than @jonearles option.
两者都使用绑定变量，但首先，对我来说，它比 @jonearles 选项更可重用和可调。
回答by Jon Heller
Put the select statement in a dynamic PL/SQL block.
将 select 语句放在动态 PL/SQL 块中。
CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
v_query_str := 'begin SELECT COUNT(*) INTO :into_bind FROM emp_'
|| p_loc
|| ' WHERE job = :bind_job; end;';
EXECUTE IMMEDIATE v_query_str
USING out v_num_of_employees, p_job;
RETURN v_num_of_employees;
END;
/
回答by Kat
Bind variable can be used in Oracle SQL query with "in" clause.
绑定变量可以在带有“in”子句的 Oracle SQL 查询中使用。
Works in 10g; I don't know about other versions.
在 10g 中工作；我不知道其他版本。
Bind variable is varchar up to 4000 characters.
绑定变量是最多 4000 个字符的 varchar。
Example: Bind variable containing comma-separated list of values, e.g.
示例：绑定包含逗号分隔值列表的变量，例如
:bindvar = 1,2,3,4,5
:bindvar = 1,2,3,4,5
select * from mytable
where myfield in
(
SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
FROM dual
CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
);
(Same info as I posted here: How do you specify IN clause in a dynamic query using a variable?)
（与我在此处发布的信息相同：如何使用变量在动态查询中指定 IN 子句？）
回答by Khilan Shah
Select Into functionality only works for PL/SQL Block, when you use Execute immediate , oracle interprets v_query_str as a SQL Query string so you can not use into .will get keyword missing Exception. in example 2 ,we are using begin end; so it became pl/sql block and its legal.
Select Into 功能仅适用于 PL/SQL Block，当您使用 Execute instant 时，oracle 将 v_query_str 解释为 SQL 查询字符串，因此您不能使用 into .will get keyword missing Exception。在示例 2 中，我们使用了开始结束；所以它变成了 pl/sql 块并且它是合法的。
