oracle - 在 Oracle PL/SQL 中使用 OPEN...FOR 动态 SQL 结构时多次重复相同的绑定(bind)变量

Question

这是对 Vincent Malgrat 的 answer to this question 的后续问题。 .当您在使用 OPEN...FOR 时需要多次使用同一个绑定(bind)变量时，我找不到要使用的正确语法动态 SQL。您可以看到 EXECUTE IMMEDIATE 的语法。 here (参见“在动态 SQL 中使用重复占位符”)……但不适用于 OPEN...FOR .使用 OPEN...FOR 时语法是否与重复的占位符不同？ ?我正在使用 Oracle 12c。这是在 PL/SQL 包中，而不是匿名 block 。

例如，Oracle 自己的文档中的这个示例可以正常工作:

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多次绑定(bind)变量，在这种情况下怎么做 :j被引用两次？

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;

在这两种情况下，它都会出现此错误:

ORA-01008: not all variables bound

Answer 1

您需要在 USING 子句中包含两次参数:

 OPEN emp_cv FOR sql_stmt USING my_job, my_job;

这是您的示例，但已简化:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  varchar2(10);
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'X';
BEGIN

   OPEN emp_cv FOR 'select * from dual where dummy = :j or dummy = :j' 
    USING my_job, my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
   END LOOP;
   CLOSE emp_cv;
END;