更改循环以选择语句并超过CPU使用率的调用限制

所以我在执行以下代码时遇到了这个错误:

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;
评论