我有一个程序SET_SUCCESSOR_KPI_STATUS
,当我尝试运行此程序时,出现了异常Exception NO_DATA_FOUND in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: 1000206
。由于这个例外,整个流程都被打乱了。该过程SET_SUCCESSOR_KPI_STATUS
正在由另一个过程调用,如果该过程引发异常,则另一个过程将产生问题。我不确定是否有任何方法可以捕获此异常。当尝试使用KPI_DEF_ID
此过程进行调用时,可能会返回值,或者它可能包含null,因为我找不到错误信息,因为未找到数据。如果我从此过程中删除异常处理并运行它,则它在其他一些过程中显示错误,但主要原因是此过程中。
PROCEDURE SET_SUCCESSOR_KPI_STATUS
-- PUBLIC
(
IN_KPI_DEF_ID IN NUMBER DEFAULT 0
, IN_KPI_STATUS IN CHAR DEFAULT 'N'
, RET OUT Number
) IS
EV Number := 0;
SUCC Number := 0;
PARENTS_GREEN Number := 1;
SUCC_KPI_ACTIVE_INITIAL CHAR;
SUCC_KPI_ACTIVE_CURRENT CHAR;
BEGIN
SELECT KD.EVENT_ID INTO EV FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.KPI_DEF_ID = IN_KPI_DEF_ID;
BEGIN
SELECT E.EVENT_SUCCESSOR_ID INTO SUCC FROM RATOR_MONITORING_CONFIGURATION.EVENT_SUCCESSOR E JOIN RATOR_MONITORING_CONFIGURATION.EVENT IN_EVENT ON E.EVENT_ID = IN_EVENT.EVENT_ID WHERE E.EVENT_ID = EV;
EXCEPTION WHEN NO_DATA_FOUND THEN
SUCC := 0;
END;
WHILE SUCC > 0
LOOP
SELECT KPI_ACTIVE_CURRENT INTO SUCC_KPI_ACTIVE_CURRENT from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.EVENT_ID = SUCC;
SELECT KPI_ACTIVE_INITIAL INTO SUCC_KPI_ACTIVE_INITIAL from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.EVENT_ID = SUCC;
UPDATE RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD
SET KD.KPI_ACTIVE_CURRENT = CASE WHEN IN_KPI_STATUS = 'Y' AND (SELECT MONITORING.ARE_PARENTS_GREEN(KD.KPI_DEF_ID) FROM DUAL) = 1 AND SUCC_KPI_ACTIVE_CURRENT <> SUCC_KPI_ACTIVE_INITIAL THEN KD.KPI_ACTIVE_INITIAL WHEN IN_KPI_STATUS = 'N' AND (SUCC_KPI_ACTIVE_CURRENT <> SUCC_KPI_ACTIVE_INITIAL) THEN IN_KPI_STATUS ELSE KD.KPI_ACTIVE_CURRENT END,
KD.LAST_UPDATED_BY = 115,
KD.LAST_UPDATED_DATE = CURRENT_DATE
WHERE KD.EVENT_ID = SUCC;
BEGIN
SELECT E.EVENT_SUCCESSOR_ID INTO SUCC FROM RATOR_MONITORING_CONFIGURATION.EVENT_SUCCESSOR E JOIN RATOR_MONITORING_CONFIGURATION.EVENT IN_EVENT ON E.EVENT_ID = IN_EVENT.EVENT_ID WHERE E.EVENT_ID = SUCC;
EXCEPTION WHEN NO_DATA_FOUND THEN
SUCC := 0;
END;
END LOOP;
RET := 1;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Exception NO_DATA_FOUND in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: '||TO_CHAR(IN_KPI_DEF_ID));
RET := 1;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception OTHERS in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: '||TO_CHAR(IN_KPI_DEF_ID));
RET := 0;
RAISE;
END SET_SUCCESSOR_KPI_STATUS;
您的某些select into
语句已经NO_DATA_FOUND
很好地处理了:
BEGIN
SELECT E.EVENT_SUCCESSOR_ID INTO SUCC FROM RATOR_MONITORING_CONFIGURATION.EVENT_SUCCESSOR E JOIN RATOR_MONITORING_CONFIGURATION.EVENT IN_EVENT ON E.EVENT_ID = IN_EVENT.EVENT_ID WHERE E.EVENT_ID = EV;
EXCEPTION WHEN NO_DATA_FOUND THEN
SUCC := 0;
END;
但是其他人不是:
SELECT KPI_ACTIVE_CURRENT INTO SUCC_KPI_ACTIVE_CURRENT from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.EVENT_ID = SUCC;
SELECT KPI_ACTIVE_INITIAL INTO SUCC_KPI_ACTIVE_INITIAL from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.EVENT_ID = SUCC;
如果这些选择不能合法地返回任何行,那么您也需要处理NO_DATA_FOUND
这些行:
BEGIN
SELECT KPI_ACTIVE_CURRENT INTO SUCC_KPI_ACTIVE_CURRENT
from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD
WHERE KD.EVENT_ID = SUCC;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Do what?
NULL;
END;
BEGIN
SELECT KPI_ACTIVE_INITIAL INTO SUCC_KPI_ACTIVE_INITIAL
from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD
WHERE KD.EVENT_ID = SUCC;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Do what?
NULL;
END;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句