如何在Oracle PL/SQL过程的开始段之后声明游标

Question

我是Oracle PL/SQL的新手。我试图写一个过程，我将首先从表中的一列获取数据，并分配给一个变量。然后，对于结果中的每一行/值，我执行另一个查询，循环遍历这个新查询的结果，并执行各种插入和更新。之后，我将回到外部循环并继续这个序列。我的尝试如下:

CREATE OR REPLACE PROCEDURE CMSADMIN.Proc_RFC_UPD_NEW_MRSP
IS
    ecode NUMBER;
    emesg VARCHAR2(200);
    cursor y IS (select distinct cod_unicom FCODE from RFC_UPD_NEW_MRSP_POOL);
BEGIN
for t in y loop 
    cursor X IS ( 
                SELECT DISTINCT s.NIF PREMISE,a.COD_UNICOM READING, A.COD_LECT_AREA AREA
                FROM SUMCON s, LECT_AREAS a 
                WHERE a.COD_UNICOM  = t.FCODE
                AND s.NUM_SUM IN (select num_sum from RFC_UPD_NEW_MRSP_POOL where cod_unicom = t.FCODE)
                ); 

        for met in x loop 
            /* Store record trace*/
            INSERT into RFC_UPD_NEW_MRSP(NIF, COD_UNICOM,COD_LECT_AREA, USUARIO, F_ACTUAL, PROGRAMA) 
            values (met.PREMISE, met.READING, met.AREA, USER, SYSDATE,'RFC_MRSP_FPL'); 
            UPDATE  fincas_per_lect fp
            SET 
             FP.NUM_MRSP = MET.READING,
             fp.AOL_FIN=0,
             fp.NUM_ITIN =0,
             fp.USUARIO = user, fp.PROGRAMA = 'RFC_MRSP_FPL', fp.F_ACTUAL = sysdate
             WHERE Fp.NIF=met.PREMISE;
            UPDATE   apmedida_ap  fp
            SET 
             FP.NUM_MRSP = MET.READING,
             fp.USUARIO = user, fp.PROGRAMA = 'RFC_MRSP_FPL', fp.F_ACTUAL = sysdate
              WHERE Fp.NIF_apa = met.PREMISE;
            UPDATE FINCAS fp 
             SET AREA_LECT = MET.AREA,
                fp.USUARIO = user, fp.PROGRAMA = 'RFC_MRSP_FPL', fp.F_ACTUAL = sysdate
                WHERE NIF = met.PREMISE;
        end loop;
end loop;
    COMMIT;
    dbms_output.put_line('The procedure Proc_RFC_UPD_NEW_MRSP executed successfully');
EXCEPTION
    WHEN OTHERS THEN
        ecode := SQLCODE;
        emesg := SQLERRM;
        dbms_output.put_line('The procedure Proc_RFC_UPD_NEW_MRSP fail with folowing error '|| TO_CHAR(ecode) || ' and error message: ' || emesg);
        NULL;
end Proc_RFC_UPD_NEW_MRSP; 

正如你所看到的，在BEGIN的for循环之后，我有另一个游标。允许这样做吗?脚本没有像预期的那样工作。有什么问题吗?感谢您的帮助

Answer 1

要做到这一点，您需要声明一个新的块:

for t in y loop 
    DECLARE
    cursor X IS ( 
                SELECT DISTINCT s.NIF PREMISE,a.COD_UNICOM READING, A.COD_LECT_AREA AREA
                FROM SUMCON s, LECT_AREAS a 
                WHERE a.COD_UNICOM  = t.FCODE
                AND s.NUM_SUM IN (select num_sum from RFC_UPD_NEW_MRSP_POOL where cod_unicom = t.FCODE)
                ); 
     BEGIN
        for met in x loop 
...
        end loop;
     END;

但是，我不太明白为什么不能将这个游标与用参数定义的游标y一起声明:

cursor X (p_FCODE RFC_UPD_NEW_MRSP_POOL.cod_unicom%TYPE) IS ( 
            SELECT DISTINCT s.NIF PREMISE,a.COD_UNICOM READING, A.COD_LECT_AREA AREA
            FROM SUMCON s, LECT_AREAS a 
            WHERE a.COD_UNICOM  = p_FCODE
            AND s.NUM_SUM IN (select num_sum from RFC_UPD_NEW_MRSP_POOL where cod_unicom = p_FCODE)
            ); 

然后可以这样引用游标:

FOR met IN x(t.FCODE) LOOP

Answer 2

declare
  cursor cur1 is 
  ...
begin
  ...
  declare
    cursor cur2 is
    ...
  begin
  end;
end;