我有一個程序（GPU_DATA_EXTRACTOR），用于從多個表中提取資料并寫入一個表。其次，我有一個函式（GETSOURCEBILLINGACCOUNTID），用于查找正確的計費帳戶并使程式的作業更容易。它在程序中被呼叫。我想執行該程序，就像我插入一個日期一樣，它應該可以作業，但如果它也為空也應該可以作業。但是當我嘗試使用下面的腳本將程序作為空值執行時它不起作用。
begin
GPU_DATA_EXTRACTOR (null);
end;
錯誤是：ORA-1422 精確提取回傳超過請求的行數 ORA-6512 在“GETSOURCEBILLINGACCOUNTID”，第 19 行 ORA-1403 在“GPU_DATA_EXTRACTOR”，第 39 行 ORA-6512 在第 2 行沒有找到資料 ORA-6512
我在下面添加腳本，我愿意接受任何修復建議。從現在開始謝謝你。
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;
uj5u.com熱心網友回復：
首先編輯您的函式，因為我覺得在例外中放置一些邏輯來回傳結果并不好：
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
如果您的游標回傳多于一行，您的邏輯是什么？你也應該對它應用一些解決方案......
uj5u.com熱心網友回復：
它是引發錯誤的函式。
程序接受日期作為引數并呼叫函式：
如果引數存在，則使用它
如果它不存在（即您通過
NULL），則該NVL函式確保使用“默認”日期：nvl(pid_billdate, trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60))))無論如何，這個價值是什么？
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss'; Session altered. SQL> select trunc((to_date ('01'||to_char(sysdate,'MMYYYY'),'DDMMYYYY')-1/24/60/60)) res from dual; RES ------------------- 30.09.2021 00:00:00好吧，你可以用更簡單的方式完成它：
SQL> select last_day(add_months(trunc(sysdate), -1)) res from dual; RES ------------------- 30.09.2021 00:00:00
無論如何：以上意味著該函式將獲取一些日期作為其引數。
您處理了NO_DATA_FOUND例外，但這不是您的問題 - 函式引發TOO_MANY_ROWS，而您沒有處理。
我不知道在這種情況下你想做什么。如果你真的不在乎你會得到哪一個，拿任何一個。例如，使用MAX函式：
SELECT MAX (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);
或者，也許你有兩個或更多相同的值；然后DISTINCT會作業：
SELECT DISTINCT pth.from_intl_bill_acct_id
或者，您實際上想要處理這種情況 - 在這種情況下，添加
when too_many_rows then
...
進入例外處理部分。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/321335.html