如何在PLSQL-Oracle中创建动态游标

3zwtqj6y 于 2023-01-25 发布在 Oracle

关注(0)|答案(2)|浏览(174)

我不能动态地创建这个游标，我只需要在语句中修改表的名称，但是它返回错误。
在创建动态游标时，我做错了什么或遗漏了什么？
动态语句位于Lv_SQL变量中，我调用游标C_DATOS，但它无法识别它。

PROCEDURE PROC_CAB_DET(Pv_corte VARCHAR2, Pv_MsjError IN OUT VARCHAR2) IS
    Lv_Table VARCHAR2(100);
    Lv_SQL   VARCHAR2(5000);
    C_DATOS  SYS_REFCURSOR;
  BEGIN
    Lv_Table := NULL;
    IF (Pv_corte IN ('02', '03')) THEN
        Lv_Table := 'TABLE_TMP_MOV';
    ELSIF (Pv_corte IN ('14', '15')) THEN
        Lv_Table := 'TABLE_TMP_FIX';
    ELSE
        Lv_Table := 'TABLE_TMP_CMF';
    END IF;

    Lv_SQL := 'SELECT cuenta, campo_2 RUBRO 
                 FROM ' || Lv_Table || ' 
                WHERE codigo = 1 
                  AND CAMPO_3 != "000" 
                  AND (campo_2 NOT IN (SELECT RUBRO FROM GSI_QC_RUBROS_CABECERA) 
                  AND upper(campo_2) NOT LIKE "NAN%") 
                MINUS 
               SELECT cuenta, campo_2 RUBRO 
                 FROM ' || Lv_Table || ' 
                WHERE codigo=4 
                  AND campo_2 != "ICE (12%)"';

    OPEN C_DATOS FOR Lv_SQL;

    FOR I IN C_DATOS LOOP      
      INSERT INTO GSI_QC_CBS_CASOS_ERROR(CUENTA, ID_ESCENARIO, DATO_TMP_1) 
      VALUES(I.CUENTA, 'IdEscenario', 'DATA');      
    END LOOP;
    COMMIT;

    CLOSE C_DATOS;
                 
    EXCEPTION
      WHEN OTHERS THEN
        Pv_MsjError := SQLERRM;
  END PROC_CAB_DET;

PLS-00221: C_DATOS is not a procedure or is undefined

oracle

来源：https://stackoverflow.com/questions/75188017/how-to-create-dynamic-cursor-in-plsql-oracle

2条答案

按热度按时间

irlmq6kh1#

不能用双引号将字符串括起来;必须是单个的。为了简化，使用q引号机制。另外，您错误地循环了refcursor。
我创建了 dummy 表来编译这个过程;我不知道代码是否如你所愿。

SQL> create or replace
  2  PROCEDURE PROC_CAB_DET(Pv_corte VARCHAR2, Pv_MsjError IN OUT VARCHAR2) IS
  3      Lv_Table VARCHAR2(100);
  4      Lv_SQL   VARCHAR2(5000);
  5      C_DATOS  SYS_REFCURSOR;
  6      --
  7      l_cuenta table_tmp_mov.cuenta%type;
  8      l_rubro  table_tmp_mov.campo_2%type;
  9  BEGIN
 10      Lv_Table := NULL;
 11      IF (Pv_corte IN ('02', '03')) THEN
 12          Lv_Table := 'TABLE_TMP_MOV';
 13      ELSIF (Pv_corte IN ('14', '15')) THEN
 14          Lv_Table := 'TABLE_TMP_FIX';
 15      ELSE
 16          Lv_Table := 'TABLE_TMP_CMF';
 17      END IF;
 18
 19      Lv_SQL := 'SELECT cuenta, campo_2 RUBRO
 20                   FROM ' || Lv_Table || q'[
 21                  WHERE codigo = 1
 22                    AND CAMPO_3 != '000'
 23                    AND (campo_2 NOT IN (SELECT RUBRO FROM GSI_QC_RUBROS_CABECERA)
 24                    AND upper(campo_2) NOT LIKE 'NAN%')
 25                  MINUS
 26                 SELECT cuenta, campo_2 RUBRO
 27                   FROM ]' || Lv_Table || q'[
 28                  WHERE codigo=4
 29                    AND campo_2 != 'ICE (12%)']';
 30
 31      OPEN C_DATOS FOR Lv_SQL;
 32
 33      loop
 34        fetch c_datos into l_cuenta, l_rubro;
 35        exit when c_datos%notfound;
 36
 37        INSERT INTO GSI_QC_CBS_CASOS_ERROR(CUENTA, ID_ESCENARIO, DATO_TMP_1)
 38        VALUES(l_CUENTA, 'IdEscenario', 'DATA');
 39      END LOOP;
 40      COMMIT;
 41
 42      CLOSE C_DATOS;
 43
 44  EXCEPTION
 45        WHEN OTHERS THEN
 46          Pv_MsjError := SQLERRM;
 47  END PROC_CAB_DET;
 48  /

Procedure created.

让我们运行它：

SQL> set serveroutput on;
SQL> declare
  2    l_err varchar2(200);
  3  begin
  4    proc_cab_det('02', l_err);
  5    dbms_output.put_line('error = ' || l_err);
  6  end;
  7  /
error =

PL/SQL procedure successfully completed.

SQL>

赞(0）回复(0）举报 2023-01-25

qni6mghb2#

将lv_SQL字符串中的"替换为双单引号''

赞(0）回复(0）举报 2023-01-25