Oracle存储过程首次调用报no_data_found错误的优化方案咨询

针对你遇到的问题，这里提供两种优化方案，解决首次调用时elec_prod和elec_social表为空导致的NO_DATA_FOUND错误：

方案一：完善异常处理逻辑 #

你之前的异常处理仅对变量赋值，但未执行插入操作，因此效果未达预期。正确做法是捕获异常后直接执行首次插入逻辑（初始读数为0加上计算值）。修改后的存储过程代码如下：

create or replace PACKAGE BODY pkg_meter_reading IS

    PROCEDURE add_reading_e_prod IS
        v_random_e_prod                    elec_prod.power_kwh%TYPE := dbms_random.value(0, 2000);
        v_current_elec_prod_reading        elec_prod.meter_reading_kwh%TYPE;
    BEGIN
        SELECT meter_reading_kwh
        INTO v_current_elec_prod_reading
        FROM elec_prod
        ORDER BY date_measurement DESC
        FETCH FIRST 1 ROW ONLY;

        dbms_output.put_line(v_current_elec_prod_reading);
        INSERT INTO elec_prod (
            id_meter,
            date_measurement,
            meter_reading_kwh,
            power_kwh
        ) VALUES (
            11,
            sysdate,
            v_current_elec_prod_reading + v_random_e_prod / 12,
            v_random_e_prod
        );
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- 表为空时执行首次插入，初始读数为0加上计算值
            INSERT INTO elec_prod (
                id_meter,
                date_measurement,
                meter_reading_kwh,
                power_kwh
            ) VALUES (
                11,
                sysdate,
                0 + v_random_e_prod / 12,
                v_random_e_prod
            );
            dbms_output.put_line('首次插入生产电表读数');
    END add_reading_e_prod;

    PROCEDURE add_reading_e_social IS
        v_random_e_social                       elec_social.power_kwh%TYPE := dbms_random.value(0, 2000);
        v_current_elec_social_reading           elec_social.meter_reading_kwh%TYPE;
    BEGIN
        SELECT meter_reading_kwh
        INTO v_current_elec_social_reading
        FROM elec_social
        ORDER BY date_measurement DESC
        FETCH FIRST 1 ROW ONLY;

        dbms_output.put_line(v_current_elec_social_reading);
        INSERT INTO elec_social (
            id_meter,
            date_measurement,
            meter_reading_kwh,
            power_kwh
        ) VALUES (
            131,
            sysdate,
            v_current_elec_social_reading + v_random_e_social/12,
            v_random_e_social
        );
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- 表为空时执行首次插入，初始读数为0加上计算值
            INSERT INTO elec_social (
                id_meter,
                date_measurement,
                meter_reading_kwh,
                power_kwh
            ) VALUES (
                131,
                sysdate,
                0 + v_random_e_social / 12,
                v_random_e_social
            );
            dbms_output.put_line('首次插入公共电表读数');
    END add_reading_e_social;

END pkg_meter_reading;

方案二：使用聚合函数+NVL避免异常抛出 #

通过MAX()聚合函数查询最新读数，即使表为空，MAX()会返回NULL，再用NVL()将其转换为0，这样不会触发NO_DATA_FOUND异常，代码无需额外异常处理，更简洁：

create or replace PACKAGE BODY pkg_meter_reading IS

    PROCEDURE add_reading_e_prod IS
        v_random_e_prod                    elec_prod.power_kwh%TYPE := dbms_random.value(0, 2000);
        v_current_elec_prod_reading        elec_prod.meter_reading_kwh%TYPE;
    BEGIN
        -- 使用MAX()获取最新读数，表空时返回NULL，NVL转为0
        SELECT NVL(MAX(meter_reading_kwh), 0)
        INTO v_current_elec_prod_reading
        FROM elec_prod;

        dbms_output.put_line(v_current_elec_prod_reading);
        INSERT INTO elec_prod (
            id_meter,
            date_measurement,
            meter_reading_kwh,
            power_kwh
        ) VALUES (
            11,
            sysdate,
            v_current_elec_prod_reading + v_random_e_prod / 12,
            v_random_e_prod
        );
    END add_reading_e_prod;

    PROCEDURE add_reading_e_social IS
        v_random_e_social                       elec_social.power_kwh%TYPE := dbms_random.value(0, 2000);
        v_current_elec_social_reading           elec_social.meter_reading_kwh%TYPE;
    BEGIN
        -- 使用MAX()获取最新读数，表空时返回NULL，NVL转为0
        SELECT NVL(MAX(meter_reading_kwh), 0)
        INTO v_current_elec_social_reading
        FROM elec_social
        WHERE id_meter = 131; -- 可选：如果电表ID唯一，加上过滤更精准

        dbms_output.put_line(v_current_elec_social_reading);
        INSERT INTO elec_social (
            id_meter,
            date_measurement,
            meter_reading_kwh,
            power_kwh
        ) VALUES (
            131,
            sysdate,
            v_current_elec_social_reading + v_random_e_social/12,
            v_random_e_social
        );
    END add_reading_e_social;

END pkg_meter_reading;

方案对比 #

• 方案一：明确处理异常逻辑，适合需要区分首次插入和后续插入场景的需求，可添加针对性日志。
• 方案二：代码更简洁，避免异常抛出，性能更优（无需捕获异常分支），推荐作为首选方案。

内容的提问来源于stack exchange，提问作者Pitt Bear