ORA-01422/ORA-01403报错：传入null执行GPU_DATA_EXTRACTOR失败

错误根因分析 #

• 第一个报错ORA-1422 Exact fetch returns more than requested number of rows出现在getSourceBillingAccountId函数的异常分支：当第一个SELECT触发NO_DATA_FOUND后，执行SELECT INTL_BILL_ACCT_ID INTO tempBillAccount FROM BILL_ACCT_PROD BAP WHERE BAP.INTL_PROD_ID = pin_intl_prod_id; 这个语句没有限制返回行数，同一个INTL_PROD_ID在BILL_ACCT_PROD表中存在多条记录时就会触发该错误。
• 第二个报错ORA-1403 no data found有两种可能：一是第一个SELECT查不到数据后，异常分支的SELECT也查不到对应INTL_PROD_ID的记录；二是存储过程的INSERT语句关联条件过滤后没有匹配的记录。

修复方案 #

第一步：修复getSourceBillingAccountId函数 #

修改函数逻辑，给所有SELECT INTO语句增加行数限制、补充全量异常捕获，示例代码如下：

CREATE OR REPLACE FUNCTION getSourceBillingAccountId (pin_intl_prod_id   IN NUMBER,
                                         pin_bill_date      IN DATE)
  RETURN NUMBER
AS
  tempBillAccount   NUMBER (14);
BEGIN
  -- 第一个查询增加ROWNUM=1，避免同sdate下多条记录导致返回多行
  SELECT pth.from_intl_bill_acct_id
    INTO tempBillAccount
    FROM prod_to_hstr pth
   WHERE pth.sdate =
            (SELECT MIN (pth2.sdate)
               FROM prod_to_hstr pth2
              WHERE     pth2.intl_prod_id = pin_intl_prod_id
                    AND pth2.sdate >= pin_bill_date)
     AND ROWNUM = 1;
                    
  RETURN tempBillAccount;
EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    -- 异常分支查询也增加ROWNUM=1，避免同一个prod_id对应多个账户的问题
    SELECT INTL_BILL_ACCT_ID 
      INTO tempBillAccount 
      FROM BILL_ACCT_PROD BAP 
     WHERE BAP.INTL_PROD_ID = pin_intl_prod_id
       AND ROWNUM = 1;        
    RETURN tempBillAccount;
  -- 补充捕获返回多行的异常，也走兜底逻辑，或者根据业务需要返回默认值比如null
  WHEN TOO_MANY_ROWS THEN
    SELECT INTL_BILL_ACCT_ID 
      INTO tempBillAccount 
      FROM BILL_ACCT_PROD BAP 
     WHERE BAP.INTL_PROD_ID = pin_intl_prod_id
       AND ROWNUM = 1;        
    RETURN tempBillAccount;
  -- 其他异常返回null避免存储过程直接报错
  WHEN OTHERS THEN
    RETURN NULL;
END;
/

第二步（可选）：优化存储过程冗余逻辑 #

你当前存储过程中日期默认值的逻辑重复了多次，可以先声明变量统一处理，避免多次计算出错，同时把隐式连接改成显式连接提升可读性：

CREATE OR REPLACE PROCEDURE GPU_DATA_EXTRACTOR (
  pid_billdate DATE)
IS
  c_limit   CONSTANT PLS_INTEGER DEFAULT 10000;
  -- 统一声明计算后的账单日期，原逻辑是取上月最后一天，该写法更简洁
  v_bill_date DATE := NVL(pid_billdate, TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))));

  CURSOR c1
  IS
    SELECT DISTINCT intl_prod_id
      FROM apld_bill_rt abr
      JOIN acct_bill ab ON abr.acct_bill_id = ab.acct_bill_id
     WHERE abr.CHRG_TP = 'INSTALLMENT'
       AND abr.TAX_CATG_ID = 'NOTAX'
       AND ab.bill_date = v_bill_date;

  TYPE prod_ids_t IS TABLE OF apld_bill_rt.intl_prod_id%TYPE INDEX BY PLS_INTEGER;
  l_prod_ids         prod_ids_t;
  
BEGIN
  EXECUTE IMMEDIATE 'truncate table GPU_INV';

  OPEN c1;
  LOOP
    FETCH c1
    BULK COLLECT INTO l_prod_ids
    LIMIT c_limit;

    EXIT WHEN l_prod_ids.COUNT = 0;

    FORALL indx IN 1 .. l_prod_ids.COUNT
    INSERT INTO GPU_INV
       SELECT AB.ACCT_BILL_ID,
              AB.BILL_NO,
              AB.INV_ID,
              AB.BILL_DATE,
              ba2.bill_acct_id,
              ba1.bill_acct_id parent_bill_acct_id,
              AB.DUE_DATE,
              PG.CMPG_ID,
              ABR.NET_AMT,
              AB.DUE_AMT,
              P.PROD_NUM,
              pds.DST_ID,
              ABR.DESCR,
              p.intl_prod_id
         FROM apld_bill_rt abr
         JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
         JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id
         JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
         JOIN FCBSADM.PROD_DST pds ON bap.intl_prod_id = pds.intl_prod_id
         JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id
         LEFT JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
         LEFT JOIN bill_acct ba2 ON ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
        WHERE ab.bill_date = v_bill_date
          AND ba2.intl_bill_acct_id = getSourceBillingAccountId(l_prod_ids(indx), v_bill_date)
          AND ABR.CHRG_TP = 'INSTALLMENT'
          AND (bap.edate is null or ab.bill_Date <= bap.edate)
          AND ABR.intl_prod_id = l_prod_ids(indx)
      UNION ALL -- 原UNION可以换成UNION ALL如果不需要去重，性能更好
      SELECT AB.ACCT_BILL_ID,
              AB.BILL_NO,
              AB.INV_ID,
              AB.BILL_DATE,
              ba1.bill_acct_id,
              ba1.bill_acct_id parent_bill_acct_id,
              AB.DUE_DATE,
              PG.CMPG_ID,
              ABR.NET_AMT,
              AB.DUE_AMT,
              P.PROD_NUM,
              pds.DST_ID,
              ABR.DESCR,
              p.intl_prod_id
         FROM apld_bill_rt abr
         JOIN acct_bill ab ON AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
         JOIN bill_acct_prod bap ON bap.intl_prod_id = abr.intl_prod_id
         JOIN prod p ON bap.intl_prod_id = p.intl_prod_id
         JOIN FCBSADM.PROD_DST pds ON bap.intl_prod_id = pds.intl_prod_id
         JOIN bill_acct ba1 ON ab.intl_bill_acct_id = ba1.intl_bill_acct_id
         LEFT JOIN prod_cmpg pg ON p.intl_prod_id = pg.intl_prod_id
        WHERE ab.bill_date = v_bill_date
          AND ba1.intl_bill_acct_id = getSourceBillingAccountId(l_prod_ids(indx), v_bill_date)
          AND ABR.CHRG_TP = 'INSTALLMENT'
          AND (bap.edate is null or ab.bill_Date <= bap.edate)
          AND ABR.intl_prod_id = l_prod_ids(indx);

  COMMIT;
  END LOOP;
  CLOSE c1;
END;
/

如果业务上同一个产品ID允许对应多个计费账户，建议先确认数据规则，调整ROWNUM=1的排序逻辑，比如按照生效日期倒序取最新的账户即可。

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