在Oracle PL/SQL中使用OPEN ... FOR动态SQL结构时，多次重复相同的绑定变量 [英] Repeating the same bind variable multiple times when using the OPEN...FOR dynamic SQL structure in Oracle PL/SQL

问题描述

这是对Vincent Malgrat的的质疑这个问题的答案.当使用OPEN...FOR动态SQL时需要多次使用相同的绑定变量时，我找不到要使用的正确语法.您可以在EXECUTE IMMEDIATE 此处中看到语法(请参见(请参见在动态SQL中使用重复的占位符")…，但不用于OPEN...FOR.使用OPEN...FOR时，语法与重复的占位符是否有所不同?我正在使用Oracle 12c.这是在PL/SQL包中，而不是匿名块.

This is a follow on question to Vincent Malgrat's answer to this question. I can't find the correct syntax to use when you need to use the same bind variable multiple times when using OPEN...FOR dynamic SQL. You can see the syntax for EXECUTE IMMEDIATE here (see "Using Duplicate Placeholders with Dynamic SQL") … but not for OPEN...FOR. Does the syntax differ with duplicate placeholders when using OPEN...FOR? I'm using Oracle 12c. This is in a PL/SQL package not an anonymous block.

例如，Oracle自己的文档中的以下示例很好用:

For example, this example from Oracle's own documentation works fine:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;
/

但是如果您需要多次引用:j绑定变量，那么在两次引用:j的情况下如何处理?

But if you need to reference the :j bind variable more than once, how do you do it in a case like this where :j is referenced twice?

sql_stmt := 'SELECT * FROM emp WHERE (job = :j AND name = :n) OR (job = :j AND age = :a)' ;

我尝试过

OPEN emp_cv FOR sql_stmt USING my_job, my_name, my_age;

和

OPEN emp_cv FOR sql_stmt USING my_job, my_name, my_age, my_job;

，在两种情况下均会出现此错误:

and in both cases it gives this error:

ORA-01008: not all variables bound

推荐答案

此处重复描述了占位符主题 https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS631 .简而言之，在 USING 子句中，参数按位置进行处理，因此您可以在查询中多次使用变量，但是对于每次出现，必须在 USING 语句中提供单独的值

Repeated placeholders topic is well described here https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS631 . In a word, in USING clause parameters are treated by positions, so you can use variable several times in a query but for each occurrence a separate value must be provided in USING statement.

这篇关于在Oracle PL/SQL中使用OPEN ... FOR动态SQL结构时，多次重复相同的绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！