PL/SQL包中Cursor可获取记录但无法读取值的问题求助

AI 小北

2026-6-21

问题描述

我有一个PL/SQL包，其中定义的游标单独执行时能正常获取符合条件的记录，但集成到包中后却无法读取对应值。减少游标的WHERE条件后功能恢复正常，但上级要求必须保留当前游标的结构，不能精简条件。

相关代码如下：

SET serveroutput ON;
CREATE OR REPLACE PACKAGE CUSTOM.FINPACK_WELDUMMY AS
PROCEDURE FINPROC_WELDUMMY(inp_str VARCHAR2,
                        out_retCode OUT NUMBER,
                        out_rec OUT VARCHAR2);
END FINPACK_WELDUMMY;
/
CREATE OR REPLACE PACKAGE BODY CUSTOM.FINPACK_WELDUMMY AS
-------------------------------------------------------------------
-- Declaring the variables to be used in the Procedure
-------------------------------------------------------------------
    outArr                      TBAADM.basp0099.ArrayType;
    v_bodDate                   DATE;
    v_bank_id                   TBAADM.GAM.BANK_ID%type;
    v_acid                      VARCHAR2(11 CHAR);
    v_lacid                     VARCHAR2(11 CHAR);
    v_cif_id                    VARCHAR2(50 CHAR);
    v_acct_cls_flg              VARCHAR2(1);
    v_schm_code                 VARCHAR2(20);
    v_address_id                NUMBER(38);
    v_count                     VARCHAR2(3 CHAR);
    v_advance_instl             NUMBER(20,4);
    v_agreement_no              VARCHAR2(200);
    v_amtfin                    NUMBER(20,4);
    v_tenure                    NUMBER(3);
    v_title                     NVARCHAR2(75);
    v_fname                     NVARCHAR2(120);
    v_mname                     NVARCHAR2(120);
    v_lname                     NVARCHAR2(150);
    v_address1                  NVARCHAR2(200 CHAR);
    v_address2                  NVARCHAR2(200 CHAR);
    v_address3                  NVARCHAR2(200 CHAR);
    v_address4                  VARCHAR2(80 CHAR);
    v_landmark                  VARCHAR2(100);
    v_city                      NVARCHAR2(200 CHAR);
    v_statedesc                 NVARCHAR2(200 CHAR);
    v_zipcode                   NVARCHAR2(100 CHAR);
    v_branchdesc                VARCHAR2(132 CHAR);
    v_instl_num                 NUMBER(3);
    v_due_date                  DATE;
    v_instl_amt                 NUMBER(20,4);
    v_dmd_amt                   NUMBER(20,2);
    v_dflt_principal_flow_id    VARCHAR2(100);
    v_acct_crncy_code           VARCHAR2(3 CHAR);
    v_princomp                  NUMBER(20,4);
    v_intcomp                   NUMBER(20,4);
    v_endbal                    NUMBER(20,2);
    v_repaymode                 CHAR(3);
    v_effrate                   VARCHAR2(100);
    v_promotionid               VARCHAR2(200);
    v_cust_segment_code         VARCHAR2(200);
    v_cust_service_code         VARCHAR2(200);
    v_product_pre_fix           VARCHAR2(200);
    v_dob                       VARCHAR2(200);
    v_product                   VARCHAR2(16 CHAR);
    v_vendor                    VARCHAR2(200);  
    v_pre_printed_stationary    VARCHAR2(200);
    v_file_attchment_name       VARCHAR2(200);
    v_psw                       VARCHAR2(200);  
    v_identifier                VARCHAR2(200);  
    v_identifier2               VARCHAR2(200);
    v_amort_printing            VARCHAR2(200);
    v_month                     VARCHAR2(200);
    v_premi1                    VARCHAR2(200);
    v_preemi2                   VARCHAR2(200);  
    v_disbursed_amount          VARCHAR2(200);
    v_awb_no                    VARCHAR2(200);
    v_email                     NVARCHAR2(75);
    v_area                      VARCHAR2(200);      
    v_sc_code                   VARCHAR2(200);
    v_mobile_no                 NVARCHAR2(37.5);
    v_sol_id                    VARCHAR2(8 CHAR);

-------------------------------------------------------------------
--Cursor declaration
--This cursor will fetch all the LOAN accounts.
-------------------------------------------------------------------

    CURSOR getCustDetail 
        (
            v_bodDate                   DATE,
            v_bank_id                   TBAADM.GAM.BANK_ID%TYPE
        ) IS
        SELECT  A.ACID, A.TRAN_DATE, B.SRL_NUM, B.INTEREST_AMOUNT, B.SHDL_BALANCE, C.DFLT_PRINCIPAL_FLOW_ID, D.SOL_ID, D.ACCT_CLS_FLG, D.SCHM_CODE, D.ACID, D.ACCT_CRNCY_CODE, D.CIF_ID
        FROM    TBAADM.LTD A, TBAADM.LSBT B, TBAADM.LSP C, TBAADM.GAM D
        WHERE   A.TRAN_DATE = TO_DATE(v_bodDate,'DD-MM-YYYY') - 1
        AND     D.ACID = A.ACID 
        AND     B.ACID = A.ACID     
        AND     A.FLOW_ID = C.DFLT_DISBMNT_FLOW_ID
        AND     D.SCHM_CODE = C.SCHM_CODE
        AND     A.BANK_ID = D.BANK_ID AND B.BANK_ID = D.BANK_ID
        AND     C.BANK_ID = D.BANK_ID
        AND     A.DEL_FLG = 'N' AND C.DEL_FLG = 'N'
        AND     D.DEL_FLG = 'N'
        AND     D.SCHM_TYPE = 'LAA'
        AND     A.ENTITY_CRE_FLG = 'Y'
        AND     D.ENTITY_CRE_FLG = 'Y';

-----------------------------------------------------------
------------------ Procedure Body 1------------------------
-----------------------------------------------------------
PROCEDURE FINPROC_WELDUMMY(inp_str IN VARCHAR2,
                            out_retCode OUT NUMBER,
                            out_rec OUT VARCHAR2) AS

  BEGIN
    -- Your existing code goes here
    out_rec := NULL;
    out_retCode := 0;

    TBAADM.basp0099.formInputArr (inp_str, OutArr);
    v_bodDate  := TO_DATE(OutArr(0),'DD-MM-YYYY');
    v_bank_id  := OutArr(1);
    
        IF(NOT getCustDetail%ISOPEN) THEN
      --{
          OPEN getCustDetail(v_bodDate,v_bank_id);
      --}
        END IF;  

    IF getCustDetail%ISOPEN THEN
      --{
      FETCH getCustDetail INTO v_lacid, v_due_date, v_count, v_intcomp, v_princomp, v_dflt_principal_flow_id, v_sol_id, v_acct_cls_flg, v_schm_code, v_acid, v_acct_crncy_code, v_cif_id;
      --}
    END IF;

    IF getCustDetail%NOTFOUND THEN
      --{
      CLOSE getCustDetail;
      out_retCode  := 1;
      DBMS_OUTPUT.PUT_LINE('out_retCode: ' || out_retCode);
      RETURN;
      --}
    END IF;
--------------------------------------------------------
    -- FECTHING ACCOUNT DETAILS AFTER CURSOR
--------------------------------------------------------
    BEGIN
            SELECT  SOL_DESC
            INTO    v_branchdesc
            FROM    TBAADM.SOL
            WHERE   SOL_ID = v_sol_id
            AND     DEL_FLG = 'N'
            AND     BANK_ID = v_bank_id;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                v_branchdesc := NULL;
    END;
       
    BEGIN
        SELECT
            nvl(
                abs(INTEREST_RATE), 0
            )
        INTO 
            v_effrate
        FROM
            TBAADM.EIT
        WHERE
            ENTITY_ID = v_acid
            AND BANK_ID = v_bank_id
            AND ENTITY_TYPE = 'ACCNT';

        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                v_effrate := NULL;
    END;
    
    BEGIN
            SELECT
                nvl(
                        SUM(DMD_AMT), 0
                    )
            INTO v_dmd_amt
            FROM
                    TBAADM.LDT
            WHERE
                ACID = v_acid
                AND BANK_ID = v_bank_id
                AND DMD_FLOW_ID = v_dflt_principal_flow_id;

            EXCEPTION
            WHEN NO_DATA_FOUND THEN
                    v_dmd_amt := NULL;
    END;       
            
    BEGIN
        SELECT  UPFRONT_INSTL_INT_AMT, DIS_AMT
        INTO    v_advance_instl, v_amtfin
        FROM    TBAADM.LAM
        WHERE   ACID = v_acid
        AND     DEL_FLG = 'N'
        AND     ENTITY_CRE_FLG = 'Y'
        AND     BANK_ID = v_bank_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
            v_advance_instl := NULL;
            v_amtfin := NULL;
    END;   

    BEGIN
    SELECT  NUM_OF_DMDS, FLOW_AMT, NUM_OF_FLOWS
    INTO    v_instl_num, v_instl_amt, v_tenure
    FROM    TBAADM.LRS
    WHERE   ACID = v_acid
    AND     DEL_FLG = 'N'
    AND     BANK_ID = v_bank_id
    AND     FLOW_ID = v_dflt_principal_flow_id
    AND     ENTITY_CRE_FLG = 'Y';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_instl_num := NULL;
        v_instl_amt := NULL;
        v_tenure := NULL;
    END; 

    BEGIN
        SELECT A.SEGMENTATION_CLASS, A.SUBSEGMENT, B.SALUTATION, B.CUST_FIRST_NAME, B.CUST_MIDDLE_NAME, B.CUST_LAST_NAME, B.EMAIL, B.PHONE_CELL, B.CUST_DOB
        INTO v_cust_segment_code,v_cust_service_code, v_title, v_fname, v_mname, v_lname, v_email, v_mobile_no, v_dob
        FROM CRMUSER.PSYCHOGRAPHIC A,CRMUSER.ACCOUNTS B
        WHERE A.ACCOUNTID = B.ACCOUNTID
        AND A.BANK_ID = v_bank_id AND B.BANK_ID = v_bank_id
        AND A.ORGKEY = v_cif_id AND B.ORGKEY = v_cif_id;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_cust_segment_code := 'NA';
        v_cust_service_code := 'NA';
        v_title := 'NA';
        v_fname := 'NA';
        v_mname := 'NA';
        v_lname := 'NA';
        v_email := 'NA';
        v_mobile_no := 'NA';
        v_dob := 'NA';
    END;       
    
    BEGIN
        SELECT ADDRESS_LINE1,ADDRESS_LINE2, ADDRESS_LINE3, CITY, STATE, ZIP
        INTO v_address1, v_address2, v_address3, v_city, v_statedesc, v_zipcode
        FROM CRMUSER.ADDRESS
        WHERE ACCOUNTID = v_acid
        AND BANK_ID = v_bank_id;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_address1 := 'NA';
        v_address2 := 'NA';
        v_address3 := 'NA';
        v_city := 'NA';
        v_statedesc := NULL;
        v_zipcode := NULL;
    END;

    BEGIN
        v_address4                  := NULL;
        v_landmark                  := NULL;
        v_vendor                    := NULL;
        v_pre_printed_stationary    := NULL;
        v_file_attchment_name       := NULL;
        v_psw                       := NULL;
        v_identifier                := NULL;
        v_month                     := NULL;
        v_amort_printing            := 'Y'; 
        v_premi1                    := 'NA';
        v_preemi2                   :='NA';
        v_disbursed_amount          :='NA';
        v_awb_no                    :='NA';
        v_area                      :='NA';
        v_sc_code                   :='NA';
    END;

    out_rec := v_count || '|' || v_advance_instl || '|' || v_agreement_no || '|' || v_amtfin || '|' || v_tenure || '|' || v_title || '|' || v_fname || '|' || v_mname || '|' || v_lname || '|' || v_address1 || '|' || v_address2 || '|' || v_address3 || '|' || v_address4 || '|' || v_landmark || '|' || v_city || '|' || v_statedesc || '|' || v_zipcode || '|' || v_branchdesc || '|' || v_instl_num || '|' || v_due_date || '|' || v_instl_amt || '|' || v_dmd_amt || '|' || v_dflt_principal_flow_id || '|' || v_princomp || '|' || v_intcomp || '|' || v_endbal || '|' || v_repaymode || '|' || v_effrate || '|' || v_promotionid || '|' || v_cust_segment_code || '|' || v_cust_service_code || '|' || v_product_pre_fix || '|' || v_dob || '|' || v_product|| '|' || v_vendor || '|' || v_pre_printed_stationary || '|' || v_file_attchment_name || '|' || v_psw || '|' || v_identifier || '|' || v_identifier2 || '|' || v_amort_printing || '|' || v_month || '|' || v_premi1 || '|' || v_preemi2 || '|' || v_disbursed_amount || '|' || v_awb_no || '|' || v_email
    || '|' || v_area || '|' || v_sc_code || '|' || v_mobile_no || '|' || v_sol_id;


END FINPROC_WELDUMMY;


END FINPACK_WELDUMMY;
/
-------------------------------------------------------
-- Execution grants are given to Tbacust Tbautil Tbagen
-------------------------------------------------------
/
DROP SYNONYM CUSTOM.C_FINPACK_WELDUMMY;
/
CREATE SYNONYM CUSTOM.C_FINPACK_WELDUMMY FOR CUSTOM.FINPACK_WELDUMMY;
/
GRANT EXECUTE ON CUSTOM.FINPACK_WELDUMMY TO TBAGEN,TBAUTIL,TBAADM;
/
SHOW ERRORS;

排查及修复方案

1. 修复游标日期转换错误

游标参数v_bodDate本身是DATE类型，无需再用TO_DATE转换，这会导致隐式转换逻辑错误，直接修改WHERE条件：

-- 原条件
A.TRAN_DATE = TO_DATE(v_bodDate,'DD-MM-YYYY') - 1
-- 修改后
A.TRAN_DATE = v_bodDate - 1

2. 修正游标打开逻辑的隐患

当前代码仅在游标未打开时才打开，若过程被重复调用，游标可能处于打开状态导致读取异常。建议改用更安全的FOR循环遍历游标（自动处理打开/关闭）：

-- 替换原打开、FETCH逻辑
FOR rec IN getCustDetail(v_bodDate, v_bank_id) LOOP
  v_lacid := rec.ACID;
  v_due_date := rec.TRAN_DATE;
  v_count := TO_CHAR(rec.SRL_NUM); -- 类型匹配转换
  v_intcomp := rec.INTEREST_AMOUNT;
  v_princomp := rec.SHDL_BALANCE;
  v_dflt_principal_flow_id := rec.DFLT_PRINCIPAL_FLOW_ID;
  v_sol_id := rec.SOL_ID;
  v_acct_cls_flg := rec.ACCT_CLS_FLG;
  v_schm_code := rec.SCHM_CODE;
  v_acid := rec.ACID;
  v_acct_crncy_code := rec.ACCT_CRNCY_CODE;
  v_cif_id := rec.CIF_ID;
  EXIT; -- 仅取第一条记录时添加
END LOOP;

3. 验证变量类型匹配

游标中B.SRL_NUM是数字类型，但v_count定义为VARCHAR2(3 CHAR)，隐式转换可能导致赋值失败。修改v_count为NUMBER类型，或在游标中显式转换：

-- 游标SELECT中修改
B.SRL_NUM AS SRL_NUM -> TO_CHAR(B.SRL_NUM) AS SRL_NUM

4. 验证参数传递正确性

在过程中添加调试输出，确认传入的v_bodDate和v_bank_id值符合预期：

DBMS_OUTPUT.PUT_LINE('v_bodDate: ' || TO_CHAR(v_bodDate, 'DD-MM

PL/SQL包中Cursor可获取记录但无法读取值的问题求助

排查及修复方案 #

1. 修复游标日期转换错误 #

2. 修正游标打开逻辑的隐患 #

3. 验证变量类型匹配 #

4. 验证参数传递正确性 #

排查及修复方案

1. 修复游标日期转换错误

2. 修正游标打开逻辑的隐患

3. 验证变量类型匹配

4. 验证参数传递正确性