oracle - ORA-1422精确提取返回超过请求的行数问题

Question

我有一个过程（GPU_DATA_EXTRACTOR），用于从多个表中提取数据并写入一个表。其次，我有一个函数（GETSOURCEBILLINGACCOUNTID），用于查找正确的计费帐户并简化程序的工作。它在过程内部被调用。我想执行该过程，就像我插入一个日期一样，它应该可以工作，但如果它也为空，也应该可以工作。但是，当我尝试使用下面的此脚本将过程作为 null 执行时，它不起作用。

begin
       GPU_DATA_EXTRACTOR (null);
end;

错误是： ORA-1422 精确提取返回的行数超过了请求的行数 ORA-6512 在“GETSOURCEBILLINGACCOUNTID”，第 19 行 ORA-1403 没有找到数据 ORA-6512 在“GPU_DATA_EXTRACTOR”，第 39 行 ORA-6512 在第 2 行

我在下面添加脚本，我愿意接受任何修复建议。从现在开始谢谢你。

    CREATE OR REPLACE FUNCTION getSourceBillingAccountId (pin_intl_prod_id   IN NUMBER,
                                             pin_bill_date      IN DATE)
      RETURN NUMBER
   AS
      tempBillAccount   NUMBER (14);
   BEGIN
      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 pth.intl_prod_id = pth2.intl_prod_id
                        AND pth2.sdate >= pin_bill_date);
                        
    RETURN tempBillAccount;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        SELECT INTL_BILL_ACCT_ID INTO tempBillAccount FROM BILL_ACCT_PROD BAP WHERE BAP.INTL_PROD_ID = pin_intl_prod_id;        
        
        RETURN tempBillAccount;
   END;

步骤

CREATE OR REPLACE PROCEDURE GPU_DATA_EXTRACTOR (
   pid_billdate DATE)
IS
   c_limit   CONSTANT PLS_INTEGER DEFAULT 10000;

   CURSOR c1
   IS
      SELECT DISTINCT intl_prod_id
        FROM apld_bill_rt abr, acct_bill ab
       WHERE     abr.CHRG_TP = 'INSTALLMENT'
             AND abr.TAX_CATG_ID = 'NOTAX'
             AND abr.acct_bill_id = ab.acct_bill_id
             AND ab.bill_date =
                    NVL (
                       pid_billdate,
                       TRUNC (
                          (  TO_DATE ('01' || TO_CHAR (SYSDATE, 'MMYYYY'),
                                      'DDMMYYYY')
                           - 1 / 24 / 60 / 60)));

   --ab.chrg_date = (select max(l_billdate) from bill_acct_billcycle bab where bab.intl_bill_acct_id = ab.intl_bill_acct_id);

   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,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                bill_acct ba2,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND ab.bill_date = nvl(pid_billdate,trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60)))
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba2.intl_bill_acct_id = getSourceBillingAccountId( l_prod_ids(indx), nvl(pid_billdate, trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60))))
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND (bap.edate is null or ab.bill_Date <= bap.edate)
                AND p.intl_prod_id = pg.intl_prod_id(+)
                AND ABR.intl_prod_id = l_prod_ids(indx)

UNION
    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,
                acct_bill ab,
                prod p,
                FCBSADM.PROD_DST pds,
                bill_acct_prod bap,
                bill_acct ba1,
                prod_cmpg pg
          WHERE ab.intl_bill_acct_id = ba1.intl_bill_acct_id
                AND ab.bill_date = nvl(pid_billdate,trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60)))
                AND AB.ACCT_BILL_ID = ABR.ACCT_BILL_ID
                --AND ba1.intl_bill_acct_id = ba2.parent_bill_acct_id
                AND ba1.intl_bill_acct_id = getSourceBillingAccountId( l_prod_ids(indx), nvl(pid_billdate, trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60))))
                AND bap.intl_prod_id = abr.intl_prod_id
                AND ABR.CHRG_TP = 'INSTALLMENT'
                AND bap.intl_prod_id = pds.intl_prod_id
                AND bap.intl_prod_id = p.intl_prod_id
                AND p.intl_prod_id = pg.intl_prod_id(+)
                AND ABR.intl_prod_id = l_prod_ids(indx)
                AND (bap.edate is null or ab.bill_Date <= bap.edate);



    COMMIT;

    END LOOP;
   CLOSE c1;
end;

标签： oracleplsql

Answer 1

首先编辑您的函数，因为我觉得在异常中放置一些逻辑来返回结果是不好的：

CREATE OR REPLACE FUNCTION getSourceBillingAccountId(pin_intl_prod_id IN NUMBER,
                                                     pin_bill_date    IN DATE)
  RETURN NUMBER AS
  tempBillAccount NUMBER(14);
BEGIN

  SELECT DECODE(COUNT(*),
                0,
                (SELECT INTL_BILL_ACCT_ID
                   INTO tempBillAccount
                   FROM BILL_ACCT_PROD BAP
                  WHERE BAP.INTL_PROD_ID = pin_intl_prod_id),
                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 pth.intl_prod_id = pth2.intl_prod_id
                         AND pth2.sdate >= pin_bill_date);
  RETURN tempBillAccount;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

---

可以肯定的是，您的函数将返回一个值，如果不返回任何数据，则返回 NULL。

我认为它会解决你的问题

注意：您还有一个问题：Exact fetch returns more than requested number of rows

如果您的光标返回多行，您的逻辑是什么？你也应该在上面应用一些解决方案......