问题描述

我有一个关于在 PL/SQL 中的动态 SQL 语句中可以在哪里使用绑定变量的问题.

I have a question regarding where bind variables can be used in a dynamic SQL statement in PL/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

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;
/

注意，我使用 SELECT INTO 语句作为动态字符串，并在 INTO 子句中使用了绑定变量.

Note I used a SELECT INTO statement as my dyamic string and used a bind variable in the INTO clause.

我目前正在旅行中，有几天不能在家里使用我的电脑，但这一直困扰着我一段时间.尝试阅读 PL/SQL 参考，但他们没有这样的选择示例.

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.

谢谢

推荐答案

不，你不能那样使用绑定变量.在您的第二个示例中，v_query_str 中的 :into_bind 只是变量 v_num_of_employees 的值的 placeholder.你的 select into 语句会变成这样:

No you can't use bind variables that way. In your second example :into_bind in v_query_str is just a placeholder for value of variable v_num_of_employees. Your select into statement will turn into something like:

SELECT COUNT(*) INTO  FROM emp_...

因为 v_num_of_employees 的值在 EXECUTE IMMEDIATE 处为 null.

because the value of v_num_of_employees is null at EXECUTE IMMEDIATE.

您的第一个示例展示了将返回值绑定到变量的正确方法.

Your first example presents the correct way to bind the return value to a variable.

编辑

原始海报编辑了我在回答中提到的第二个代码块，为 v_num_of_employees 使用 OUT 参数模式而不是默认的 IN 模式.此修改使两个示例在功能上等效.