如何在Oracle PL/SQL过程的开始部分之后声明游标 [英] How to declare a cursor after the begin section of an Oracle PL/SQL procedure

问题描述

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

I am new in Oracle PL/SQL. I trying to write a procedure where I would first get data from a column in a table and assign to a variable. Then for each row/value in the result, I perform another query and loop through the result of this new query and perform various inserts and updates. After which I would go back to the outer loop and continue the sequence. My attempt is as follows:

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循环之后，我还有另一个光标.可以吗?该脚本无法正常工作.可能是什么问题呢?感谢任何帮助

As you can see I have another cursor after the for loop inside BEGIN. Is this allowed? The script doesn't work as expected. What could be the problem? Appreciate any help

推荐答案

为此，您需要声明一个新块:

To do this, you'd need to declare a new block:

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一起声明:

However, I don't quite see why you can't just declare this cursor along with cursor y, defined with a parameter:

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)
            ); 

然后可以这样引用光标:

You could then reference the cursor thusly:

FOR met IN x(t.FCODE) LOOP

这篇关于如何在Oracle PL/SQL过程的开始部分之后声明游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！