PLS-00201:标识符"R_CUR"必须在动态sql中声明 [英] PLS-00201: identifier 'R_CUR' must be declared in dynamic sql

问题描述

要求:我希望从此代码中动态执行游标并根据列值将变量var设置为true或false.

Requirement: what I want from this code is to execute the cursor dynamically and set variable var true or false based on column values.

问题:，但是当我运行以下代码时，它向我显示错误:

Problem: But while I am running the below code its showing me error:

第4行出现错误
ORA-06550:第1行，第10列:
PLS-00201:必须声明标识符"R_CUR"
ORA-06550:第1行，第7列:
PL/SQL:语句被忽略
ORA-06512:在第61行上

Error at line 4
ORA-06550: line 1, column 10:
PLS-00201: identifier 'R_CUR' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at line 61`

set serveroutput on;

DECLARE
   CURSOR cur
   IS
      SELECT   EMPNO,
               ENAME,
               JOB,
               MGR
        FROM   emp
        WHERE   EMPNO = 7839;

   TYPE t_cur IS TABLE OF cur%ROWTYPE;

   r_cur t_cur;

   TYPE t IS TABLE OF VARCHAR2 (20);

   r t
     := t ('EMPNO',
           'ENAME',
           'JOB',
           'MGR') ;



   v_if_statement VARCHAR2 (1000);
   v_sql_statement VARCHAR2 (1000);
   var VARCHAR2 (10) := 'false';
   v VARCHAR2 (10) := 'r';
   rc VARCHAR2 (10) := 'r(j)';
   vr VARCHAR2 (10) := 'v';
   r1 VARCHAR2 (10);
BEGIN
    OPEN cur;

    LOOP
       FETCH cur BULK COLLECT INTO   r_cur;

       EXIT WHEN cur%NOTFOUND;
    END LOOP;

    CLOSE cur;

   FOR i IN r_cur.FIRST .. r_cur.LAST
   LOOP
      FOR j IN r.FIRST .. r.LAST
      LOOP
         v_if_statement :=
                'IF r_cur('||i||').'
             || r (j)
             || ' '
             || 'IS NOT NULL'
             || ' '
             || 'THEN :var:=''true'';'
             || ' '
             || 'dbms_output.put_line(''inside stmt'');'
             || 'END IF;';
             v_sql_statement := 'BEGIN ' || v_if_statement || ' END;';

            EXECUTE IMMEDIATE v_sql_statement USING OUT var;

        DBMS_OUTPUT.put_line ('var : ' || var);
     END LOOP;
   END LOOP;
END;

推荐答案

该错误在此处有效.如果查看execute immediate语句，则在解析并执行该语句时，它会以begin块开头.在该开始块中，r_cur的声明超出范围，因此您遇到了问题.您还需要在开始块中输入r_cur声明.看到我的评论部分.

The error is valid here. If you look at the execute immediate statement, when its resolved and getting executed, it starts with a begin block. In that begin block the declaration of r_cur is out of scope and hence you get the issue. Yo need to make the r_cur declaration in you begin block as well. See my commented part.

FOR j IN r.FIRST .. r.LAST
      LOOP
         v_if_statement :=
                'IF r_cur('||i||').'
             || r (j)
             || ' '
             || 'IS NOT NULL'
             || ' '
             || 'THEN :var:=''true'';'
             || ' '
             || 'dbms_output.put_line(''inside stmt'');'
             || 'END IF;';

           ----**Here when the begin block gets resolved the r_cur decalration is needed.**     
            v_sql_statement := 'BEGIN ' || v_if_statement || ' END;';

            EXECUTE IMMEDIATE v_sql_statement USING OUT var;

        DBMS_OUTPUT.put_line ('var : ' || var);

这篇关于PLS-00201:标识符"R_CUR"必须在动态sql中声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！