所以我在执行以下代码时遇到了这个错误：
ORA-02393: exceeded call limit on CPU usage
假定该过程将以半小时为间隔，在接下来的14天内针对每个tni，frmp和lr组合生成从现有表到新表（local_rm16）的预测。预测的每个半小时间隔具有不同的值。例如，在星期一，hh = 1，它将仅根据现有数据计算星期一的平均值，其中hh = 1。然后，它将持续一天的其余时间，直到hh = 48。
有人告诉我，用select语句替换循环将解决此错误并提高代码的性能，但是我不知道该怎么做。
任何形式的帮助将不胜感激。谢谢。
PROCEDURE FORECAST_TO_LOCAL_RM16 (P_RUN_DATE IN DATE)
IS
CURSOR C_TNI_FRMP_LR IS SELECT DISTINCT TNI, FRMP, LR FROM v_nem_rm16;
V_START_DATE DATE := TRUNC(P_RUN_DATE + 1); --FORECASTING WILL START FROM MIDNIGHT
V_DAY DATE;
V_AVERAGE_VALUE FLOAT;
V_IS_HOLIDAY BOOLEAN;
V_COUNTER NUMBER;
V_NO_OF_DAYS NUMBER := GET_PARAMETER('RM16_FORECAST','NUMBER_OF_DAYS');
BEGIN
IF CHECK_EXISTING_RECORD(V_START_DATE) THEN
DELETE FROM LOCAL_RM16 WHERE TRUNC(DAY) >= V_START_DATE; --DELETE DATA THAT IS IN THE FUTURE (AFTER V_START_DATE)
COMMIT;
PRINT_MESSAGE('EXISTING ROWS HAVE BEEN DELETED');
END IF;
FOR COMBINATIONS IN C_TNI_FRMP_LR
LOOP
V_DAY:= V_START_DATE;
V_COUNTER := 1;
WHILE V_COUNTER <= V_NO_OF_DAYS
LOOP
V_IS_HOLIDAY := CHECK_HOLIDAY(V_DAY);
FOR HALF_HOUR_INTERVAL IN 1..48
LOOP
IF V_IS_HOLIDAY THEN
VOLUME_ON_HOLIDAY(HALF_HOUR_INTERVAL, V_AVERAGE_VALUE);
ELSE
AVERAGE_VOLUME(V_DAY, HALF_HOUR_INTERVAL, V_AVERAGE_VALUE);
END IF;
--INSERT INTO LOCAL_RM16
INSERT INTO LOCAL_RM16
(COMPANY_CODE,
SETTLEMENT_CASE_ID,
SETTLEMENT_RUN_ID,
STATEMENT_TYPE,
TNI,
METERTYPE,
FRMP,
LR,
MDP,
CHANGE_DATE,
DAY,
TRANSACTION_ID,
HH,
VOLUME)
VALUES
(NULL,
NULL,
NULL,
'FORECAST',
COMBINATIONS.TNI,
NULL,
COMBINATIONS.FRMP,
COMBINATIONS.LR,
NULL,
SYSDATE,
V_DAY,
NULL,
HALF_HOUR_INTERVAL,
V_AVERAGE_VALUE);
COMMIT;
END LOOP;
V_DAY := V_DAY + 1;
V_COUNTER := V_COUNTER + 1
END LOOP;
END LOOP;
PRINT_MESSAGE('ELECTRICITY CONSUMPTION FORECAST VALUES HAVE BEEN ADDED TO LOCAL_RM16');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
PRINT_MESSAGE('TOO MANY ROWS: '|| SQLERRM);
WHEN NO_DATA_FOUND THEN
PRINT_MESSAGE('NO DATA FOUND: '||SQLERRM);
END;
以下是与主要代码相关的其他代码
FUNCTION CHECK_HOLIDAY (P_RUN_DATE IN DATE)
--PURPOSE:
--THIS FUNCTION IS TO CHECK IF THE INPUT DATE IS HOLIDAY
--IT COMPARES THE DATE IN THE DBP_HOLIDAY TABLE AND RETURN TRUE IF IT IS HOLIDAY
--IF IT IS NOT A HOLIDAY, IT WILL RETURN FALSE
RETURN BOOLEAN
IS
V_ROWS_FOUND NUMBER;
BEGIN
SELECT COUNT(*)
INTO V_ROWS_FOUND
FROM DBP_HOLIDAY
WHERE TRUNC(HOLIDAY_DATE) = TRUNC(P_RUN_DATE);
IF V_ROWS_FOUND=1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
EXCEPTION
WHEN OTHERS THEN
PRINT_MESSAGE(SQLERRM);
END;
PROCEDURE VOLUME_ON_HOLIDAY(P_HALF_HOUR IN NUMBER, P_AVG_HOL_VOL OUT FLOAT)
IS
--PURPOSE:
--THIS PROCEDURE CALCULATE THE AVERAGE VOLUME OF ELECTRICITY CONSUMPTION WHEN THE DAY IS HOLIDAY
--IT CALCULATES THE AVERAGE OF HALF HOUR VALUES FOR EACH DAY IN THE FUTURE
--IT WILL WHETHER THERE ARE PAST HOLIDAY CONSUMPTION DATA IN THE V_NEM_RM16 TABLE
--OTHERWISE IT WILL USE THE PAST SUNDAYS CONSUMPTION DATA
BEGIN
--
SELECT AVG(nem.volume) INTO P_AVG_HOL_VOL
FROM v_nem_rm16 NEM, dbp_holiday HOL
WHERE TO_DATE (NEM.DAY) = TO_DATE(HOL.HOLIDAY_DATE) AND
NEM.HH = P_HALF_HOUR;
EXCEPTION
WHEN NO_DATA_FOUND THEN
SELECT AVG(VOLUME) INTO P_AVG_HOL_VOL
FROM V_NEM_RM16
WHERE UPPER(TO_CHAR(TRUNC(DAY), 'DAY')) LIKE '%SUNDAY%' AND
HH = P_HALF_HOUR;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
--
END;
PROCEDURE AVERAGE_VOLUME (P_RUN_DATE IN DATE, P_HALF_HOUR IN NUMBER, P_AVG_VOL OUT FLOAT)
IS
--PURPOSE:
--THIS PROCEDURE WILL CALCULATE THE AVERAGE VOLUME OF ELECTRICITY FOR THE PREVIOUS SAME DAY
--IT CALCULATES THE AVERAGE OF HALF HOUR VALUES FOR EACH DAY IN THE FUTURE
BEGIN
--
SELECT AVG(VOLUME) INTO P_AVG_VOL
FROM V_NEM_RM16
WHERE TO_CHAR(TRUNC(DAY),'DAY') = TO_CHAR(TRUNC(P_RUN_DATE),'DAY') AND
HH = P_HALF_HOUR;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND:' || SQLERRM);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY ROWS:' || SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
--
END;
