PL/SQL函数异常块声明游标报错：Encountered symbol 'CFCE'

我写的PMISHR.SYS_ENCASH_FC函数编译时碰到这个错误：

Encountered the symbol "CFCE" when expecting one of the following:= . ( @ % ;

注：如果把游标放在函数的DECLARE部分声明是没问题的，但为了性能，我想在异常处理块里声明游标。完整函数代码如下：

CREATE OR REPLACE FUNCTION PMISHR.SYS_ENCASH_FC
(
    PRM_POLICY_KEY VARCHAR2,
    PRM_POLDEBTOR_SEQ NUMBER,
    PRM_SYSTEMIND VARCHAR2 DEFAULT 'G',
    PRM_AGTBDX_DATE DATE
)
   RETURN VARCHAR2 IS
   --V_FC_PREMIUM  VARCHAR2(3000 BYTE);
   V_FC_PREMIUM  CLOB :='';
   
BEGIN
    
    IF PRM_SYSTEMIND = 'G' THEN
        BEGIN
            /* AUTO RECONCILIATION */
            
        
            SELECT  AGB_COMP_REF_NO
            INTO    V_FC_PREMIUM
            FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, GENPOLDEBTOR
            WHERE   CDR_RECONCILE_SOURCE = 'GENPOLICY'
            AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
            AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
            AND     CDN_SOURCE_CDE = POL_POLICY_CDE
            AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
            AND     POL_POLICY_CDE = PRM_POLICY_KEY
            AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
            AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
            AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
            AND     ((CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE AND AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX)
                      OR (CDR_CDN_NUMBER_VO_BRX IS NULL AND CDR_PREMIUMS_VO_CDE = AVO_PREMIUMSVO_CDE)
                    )
            AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
            AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
            AND     ROWNUM<=1; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                /* MANUAL RECONCILIATION */

                CURSOR cFCE(PRM_POLICY_KEY VARCHAR2,PRM_POLDEBTOR_SEQ NUMBER) IS
                SELECT AGB_COMP_REF_NO
                FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
                WHERE   CDNOTE.CDN_SOURCE_TABLE = 'GENPOLICY'
                AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
                AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
                AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
                AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
                AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
                AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
                AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
                AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
                AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
                AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
                AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
                AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
                AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
                AND     AGB_VALID_IND = 'Y'
;   
rFCE cFCE%ROWTYPE; 
                 OPEN cFCE(PRM_POLICY_KEY,PRM_POLDEBTOR_SEQ);
                 LOOP
                    FETCH cFCE INTO rFCE;
                    EXIT WHEN cFCE%NOTFOUND;
                    V_FC_PREMIUM := V_FC_PREMIUM || rFCE.AGB_COMP_REF_NO || ' ; ';
                 END LOOP;
                 CLOSE cFCE;
                 
                 if length(V_FC_PREMIUM)>4000 then V_FC_PREMIUM:= substr(V_FC_PREMIUM,1,4000); end if;
                 
            END; 
        RETURN V_FC_PREMIUM;

    ELSE
        BEGIN
            /* AUTO RECONCILIATION */
            SELECT  AGB_COMP_REF_NO
            INTO    V_FC_PREMIUM
            FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, LIFEPOLDEBTOR
            WHERE   CDR_RECONCILE_SOURCE = 'LIFE_POLICY'
            AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
            AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
            AND     CDN_SOURCE_CDE = POL_POLICY_CDE
            AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
            AND     POL_POLICY_CDE = PRM_POLICY_KEY
            AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
            AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
            AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
            AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
            AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
            AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
            AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
            AND     ROWNUM<=1; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN   
                /* MANUAL RECONCILIATION */
                SELECT  AGB_COMP_REF_NO 
                INTO    V_FC_PREMIUM
                FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
                WHERE   CDNOTE.CDN_SOURCE_TABLE = 'LIFE_POLICY'
                AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
                AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
                AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
                AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
                AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
                AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
                AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
                AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
                AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
                AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
                AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
                AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
                AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
                AND     AGB_VALID_IND = 'Y'
                AND     ROWNUM<=1;
            END; 
        RETURN V_FC_PREMIUM;
    END IF;
    
END;
/

问题原因 #

PL/SQL语法规则明确：异常处理块属于执行部分，不能直接在里面声明游标、变量等元素，所有声明语句必须放在块的DECLARE段中。你直接在WHEN NO_DATA_FOUND THEN后面声明游标cFCE，违反了这个规则，所以编译报错。

修复方案 #

要在异常触发时使用游标，最直接的办法是在异常块里嵌套一个子块，把游标声明放在子块的DECLARE部分，这样既符合语法，又能保证只有异常触发时才执行游标逻辑，不影响性能。

修复后的函数代码如下：

CREATE OR REPLACE FUNCTION PMISHR.SYS_ENCASH_FC
(
    PRM_POLICY_KEY VARCHAR2,
    PRM_POLDEBTOR_SEQ NUMBER,
    PRM_SYSTEMIND VARCHAR2 DEFAULT 'G',
    PRM_AGTBDX_DATE DATE
)
   RETURN VARCHAR2 IS
   --V_FC_PREMIUM  VARCHAR2(3000 BYTE);
   V_FC_PREMIUM  CLOB :='';
   
BEGIN
    
    IF PRM_SYSTEMIND = 'G' THEN
        BEGIN
            /* AUTO RECONCILIATION */
            
        
            SELECT  AGB_COMP_REF_NO
            INTO    V_FC_PREMIUM
            FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, GENPOLDEBTOR
            WHERE   CDR_RECONCILE_SOURCE = 'GENPOLICY'
            AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
            AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
            AND     CDN_SOURCE_CDE = POL_POLICY_CDE
            AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
            AND     POL_POLICY_CDE = PRM_POLICY_KEY
            AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
            AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
            AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
            AND     ((CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE AND AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX)
                      OR (CDR_CDN_NUMBER_VO_BRX IS NULL AND CDR_PREMIUMS_VO_CDE = AVO_PREMIUMSVO_CDE)
                    )
            AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
            AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
            AND     ROWNUM<=1; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                /* MANUAL RECONCILIATION - 嵌套子块存放游标声明 */
                DECLARE
                    CURSOR cFCE(p_policy_key VARCHAR2, p_poldebtor_seq NUMBER) IS
                    SELECT AGB_COMP_REF_NO
                    FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
                    WHERE   CDNOTE.CDN_SOURCE_TABLE = 'GENPOLICY'
                    AND     CDNOTE.CDN_SOURCE_CDE = p_policy_key
                    AND     CDNOTE.CDN_SOURCE_SEQ = p_poldebtor_seq
                    AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
                    AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
                    AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
                    AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
                    AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
                    AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
                    AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
                    AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
                    AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
                    AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
                    AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
                    AND     AGB_VALID_IND = 'Y';
                    rFCE cFCE%ROWTYPE;
                BEGIN
                    OPEN cFCE(PRM_POLICY_KEY, PRM_POLDEBTOR_SEQ);
                    LOOP
                        FETCH cFCE INTO rFCE;
                        EXIT WHEN cFCE%NOTFOUND;
                        V_FC_PREMIUM := V_FC_PREMIUM || rFCE.AGB_COMP_REF_NO || ' ; ';
                    END LOOP;
                    CLOSE cFCE;
                    
                    IF LENGTH(V_FC_PREMIUM) > 4000 THEN 
                        V_FC_PREMIUM := SUBSTR(V_FC_PREMIUM,1,4000); 
                    END IF;
                END;
        END; 
        RETURN V_FC_PREMIUM;

    ELSE
        BEGIN
            /* AUTO RECONCILIATION */
            SELECT  AGB_COMP_REF_NO
            INTO    V_FC_PREMIUM
            FROM    CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX, LIFEPOLDEBTOR
            WHERE   CDR_RECONCILE_SOURCE = 'LIFE_POLICY'
            AND     CDR_RECONCILE_SOURCE = CDN_SOURCE_TABLE
            AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'N'
            AND     CDN_SOURCE_CDE = POL_POLICY_CDE
            AND     CDN_SOURCE_SEQ = POL_POLDEBTOR_SEQ
            AND     POL_POLICY_CDE = PRM_POLICY_KEY
            AND     POL_POLDEBTOR_SEQ = PRM_POLDEBTOR_SEQ
            AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
            AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
            AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
            AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
            AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
            AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
            AND     ROWNUM<=1; 
        EXCEPTION
            WHEN NO_DATA_FOUND THEN   
                /* MANUAL RECONCILIATION */
                SELECT  AGB_COMP_REF_NO 
                INTO    V_FC_PREMIUM
                FROM    MANUALPREMIUM_RECONCILE, CDNOTE_RECONCILE_DATA, CDNOTE, AGENTVOBRX, AGENTBORDEREAUX
                WHERE   CDNOTE.CDN_SOURCE_TABLE = 'LIFE_POLICY'
                AND     CDNOTE.CDN_SOURCE_CDE = PRM_POLICY_KEY
                AND     CDNOTE.CDN_SOURCE_SEQ = PRM_POLDEBTOR_SEQ
                AND     CDR_CDNOTE_TYPE = CDNOTE_TYPE
                AND     CDR_CDN_NUMBER_SYSTEM = CDNOTE_NUMBER
                AND     CDR_RECONCILE_WITH = 'AGENTVOBRX'
                AND     NVL(CDR_MANUALRECONCILE_IND,'N') = 'Y'
                AND     CDR_MANUALRECONCILE_BATCH_CDE = MPR_RECONCILE_BATCH_ID
                AND     CDR_VO_BRX_PREM_CDN_TYPE = AVO_CDNOTE_TYPE
                AND     AVO_CDNOTE_NUMBER = CDR_CDN_NUMBER_VO_BRX
                AND     AVO_AGENTBORDEREAUX_CDE = AGENTBORDEREAUX_KEY
                AND     MPR_CDNOTE_TYPE = CDR_CDNOTE_TYPE
                AND     MPR_CDNOTE_NUMBER = CDR_CDN_NUMBER_SYSTEM
                AND     TRUNC(AGENTBORDEREAUX.A_DATE_TIME_CREATED) <= TRUNC(PRM_AGTBDX_DATE)
                AND     AGB_VALID_IND = 'Y'
                AND     ROWNUM<=1;
        END; 
        RETURN V_FC_PREMIUM;
    END IF;
    
END;
/

内容的提问来源于stack exchange，提问作者moez ns