本文介绍了在PL/SQL中将绑定变量与动态SELECT INTO子句一起使用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个问题,关于在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;
/

我想知道您是否可以在这样的select语句中使用绑定变量

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的值的占位符.您的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.

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

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

修改

原始张贴者已经编辑了我在答案中引用的第二个代码块,以对v_num_of_employees使用OUT参数模式,而不是默认的IN模式.此修改使两个示例在功能上都等效.

The original poster has edited the second code block that I'm referring in my answer to use OUT parameter mode for v_num_of_employees instead of the default IN mode. This modification makes the both examples functionally equivalent.

这篇关于在PL/SQL中将绑定变量与动态SELECT INTO子句一起使用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 22:16
查看更多