PL/SQL循环执行时触发No Data Found错误的排查求助

API 探路者

2026-7-26

解决PL/SQL中SELECT INTO的NO_DATA_FOUND错误

你遇到的NO_DATA_FOUND错误，核心原因是SELECT INTO语句强制要求返回且仅返回1行数据——如果某个查询没有匹配到任何记录，PL/SQL就会抛出这个异常。比如循环到某个customertrxid时，TrasladosTotal表中没有对应0.03税率的记录，对应的SELECT IMPORTE into ImporteTotal03...就会触发错误。

下面给你三种可行的解决思路：

1. 捕获NO_DATA_FOUND异常，给变量赋默认值

在每个可能无数据的SELECT INTO外层加异常处理，触发异常时给变量设默认值（比如0或NULL），避免程序中断：

DECLARE
    current_id       Comprobante.customertrxid%TYPE;
    SumaImporteCon   NUMBER := 0;
    Subtotal         NUMBER := 0;
    SumaTasa03       NUMBER := 0;
    ImporteTotal03   NUMBER := 0;
    SumaTasa16       NUMBER := 0;
    ImporteTotal16   NUMBER := 0;
    SumaImpuesto     NUMBER := 0;
    ImpuestoTotal    NUMBER := 0;
BEGIN
    FOR id_rec IN (SELECT customertrxid FROM comprobante) LOOP
        current_id := id_rec.customertrxid;
        DBMS_OUTPUT.PUT_LINE('ID: ' || current_id);
        
        -- 处理Conceptos表查询
        BEGIN
            SELECT sum(IMPORTE) INTO SumaImporteCon 
            FROM Conceptos 
            WHERE customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SumaImporteCon := 0;
        END;
        
        -- 处理Comprobante表查询
        BEGIN
            SELECT SUBTOTAL INTO Subtotal 
            FROM Comprobante 
            WHERE customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                Subtotal := 0;
        END;
        
        -- 处理Traslados 0.03税率查询
        BEGIN
            SELECT sum(IMPORTE) INTO SumaTasa03 
            FROM Traslados 
            WHERE TasaOcuota = '0.030000' AND customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SumaTasa03 := 0;
        END;
        
        -- 处理TrasladosTotal 0.03税率查询
        BEGIN
            SELECT IMPORTE INTO ImporteTotal03 
            FROM TrasladosTotal 
            WHERE TasaOcuota = '0.030000' AND customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                ImporteTotal03 := 0;
        END;
        
        -- 处理Traslados 0.16税率查询
        BEGIN
            SELECT sum(IMPORTE) INTO SumaTasa16 
            FROM Traslados 
            WHERE TasaOcuota = '0.160000' AND customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SumaTasa16 := 0;
        END;
        
        -- 处理TrasladosTotal 0.16税率查询
        BEGIN
            SELECT IMPORTE INTO ImporteTotal16 
            FROM TrasladosTotal 
            WHERE TasaOcuota = '0.160000' AND customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                ImporteTotal16 := 0;
        END;
        
        -- 处理TrasladosTotal总税额查询
        BEGIN
            SELECT sum(IMPORTE) INTO SumaImpuesto 
            FROM TrasladosTotal 
            WHERE customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                SumaImpuesto := 0;
        END;
        
        -- 处理ImpuestosTotal总税额查询
        BEGIN
            SELECT TOTALIMPUESTOSTRASLADOS INTO ImpuestoTotal 
            FROM ImpuestosTotal 
            WHERE customertrxid = current_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                ImpuestoTotal := 0;
        END;
        
        -- 输出对比结果
        dbms_output.put_line('Suma importe conceptos ' || SumaImporteCon);
        dbms_output.put_line('Subtotal Comprobante: ' || Subtotal);
        IF (SumaImporteCon = Subtotal) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        dbms_output.put_line('Suma tasa 0.03: ' || SumaTasa03);
        dbms_output.put_line('Importe 0.03: ' || ImporteTotal03);
        IF ( SumaTasa03 = ImporteTotal03) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        dbms_output.put_line('Suma tasa 0.16: ' || SumaTasa16);
        dbms_output.put_line('Importe 0.16: ' || ImporteTotal16);
        IF (SumaTasa16 = ImporteTotal16) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        dbms_output.put_line('Suma importe impuesto: ' || SumaImpuesto);
        dbms_output.put_line('El impuesto total: ' || ImpuestoTotal);
        IF (SumaImpuesto = ImpuestoTotal) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
    END LOOP;
END;
/

2. 用聚合函数+NVL避免异常

对于单值查询（比如SELECT SUBTOTAL INTO...），用MAX()或MIN()包裹字段，无匹配记录时返回NULL，再用NVL()转成默认值；SUM函数无数据时返回NULL，同样用NVL处理。这种方式不用写大量异常块，代码更简洁：

DECLARE
    current_id       Comprobante.customertrxid%TYPE;
    SumaImporteCon   NUMBER;
    Subtotal         NUMBER;
    SumaTasa03       NUMBER;
    ImporteTotal03   NUMBER;
    SumaTasa16       NUMBER;
    ImporteTotal16   NUMBER;
    SumaImpuesto     NUMBER;
    ImpuestoTotal    NUMBER;
BEGIN
    FOR id_rec IN (SELECT customertrxid FROM comprobante) LOOP
        current_id := id_rec.customertrxid;
        DBMS_OUTPUT.PUT_LINE('ID: ' || current_id);
        
        -- 用NVL处理SUM的NULL值，用MAX处理单值查询的无数据情况
        SELECT NVL(sum(IMPORTE), 0) INTO SumaImporteCon 
        FROM Conceptos 
        WHERE customertrxid = current_id;
        
        SELECT NVL(MAX(SUBTOTAL), 0) INTO Subtotal 
        FROM Comprobante 
        WHERE customertrxid = current_id;
        
        SELECT NVL(sum(IMPORTE), 0) INTO SumaTasa03 
        FROM Traslados 
        WHERE TasaOcuota = '0.030000' AND customertrxid = current_id;
        
        SELECT NVL(MAX(IMPORTE), 0) INTO ImporteTotal03 
        FROM TrasladosTotal 
        WHERE TasaOcuota = '0.030000' AND customertrxid = current_id;
        
        SELECT NVL(sum(IMPORTE), 0) INTO SumaTasa16 
        FROM Traslados 
        WHERE TasaOcuota = '0.160000' AND customertrxid = current_id;
        
        SELECT NVL(MAX(IMPORTE), 0) INTO ImporteTotal16 
        FROM TrasladosTotal 
        WHERE TasaOcuota = '0.160000' AND customertrxid = current_id;
        
        SELECT NVL(sum(IMPORTE), 0) INTO SumaImpuesto 
        FROM TrasladosTotal 
        WHERE customertrxid = current_id;
        
        SELECT NVL(MAX(TOTALIMPUESTOSTRASLADOS), 0) INTO ImpuestoTotal 
        FROM ImpuestosTotal 
        WHERE customertrxid = current_id;
        
        -- 输出对比结果（同原代码）
        dbms_output.put_line('Suma importe conceptos ' || SumaImporteCon);
        dbms_output.put_line('Subtotal Comprobante: ' || Subtotal);
        IF (SumaImporteCon = Subtotal) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        -- 后续输出逻辑同原代码，此处省略
    END LOOP;
END;
/

3. 合并查询（推荐）

把多个表的查询合并成一个，用LEFT JOIN关联所有表，一次性获取所有需要的数据，既避免了多次SELECT INTO的异常问题，还能提升执行效率：

DECLARE
    CURSOR c_data IS
        SELECT 
            c.customertrxid,
            NVL(sum_con.SumaImporteCon, 0) AS SumaImporteCon,
            NVL(cp.Subtotal, 0) AS Subtotal,
            NVL(sum_t03.SumaTasa03, 0) AS SumaTasa03,
            NVL(tt03.ImporteTotal03, 0) AS ImporteTotal03,
            NVL(sum_t16.SumaTasa16, 0) AS SumaTasa16,
            NVL(tt16.ImporteTotal16, 0) AS ImporteTotal16,
            NVL(sum_tt.SumaImpuesto, 0) AS SumaImpuesto,
            NVL(it.ImpuestoTotal, 0) AS ImpuestoTotal
        FROM comprobante c
        LEFT JOIN (
            SELECT customertrxid, sum(IMPORTE) AS SumaImporteCon
            FROM Conceptos
            GROUP BY customertrxid
        ) sum_con ON c.customertrxid = sum_con.customertrxid
        LEFT JOIN (
            SELECT customertrxid, SUBTOTAL
            FROM Comprobante
        ) cp ON c.customertrxid = cp.customertrxid
        LEFT JOIN (
            SELECT customertrxid, sum(IMPORTE) AS SumaTasa03
            FROM Traslados
            WHERE TasaOcuota = '0.030000'
            GROUP BY customertrxid
        ) sum_t03 ON c.customertrxid = sum_t03.customertrxid
        LEFT JOIN (
            SELECT customertrxid, IMPORTE AS ImporteTotal03
            FROM TrasladosTotal
            WHERE TasaOcuota = '0.030000'
        ) tt03 ON c.customertrxid = tt03.customertrxid
        LEFT JOIN (
            SELECT customertrxid, sum(IMPORTE) AS SumaTasa16
            FROM Traslados
            WHERE TasaOcuota = '0.160000'
            GROUP BY customertrxid
        ) sum_t16 ON c.customertrxid = sum_t16.customertrxid
        LEFT JOIN (
            SELECT customertrxid, IMPORTE AS ImporteTotal16
            FROM TrasladosTotal
            WHERE TasaOcuota = '0.160000'
        ) tt16 ON c.customertrxid = tt16.customertrxid
        LEFT JOIN (
            SELECT customertrxid, sum(IMPORTE) AS SumaImpuesto
            FROM TrasladosTotal
            GROUP BY customertrxid
        ) sum_tt ON c.customertrxid = sum_tt.customertrxid
        LEFT JOIN (
            SELECT customertrxid, TOTALIMPUESTOSTRASLADOS AS ImpuestoTotal
            FROM ImpuestosTotal
        ) it ON c.customertrxid = it.customertrxid;
    r_data c_data%ROWTYPE;
BEGIN
    FOR r_data IN c_data LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || r_data.customertrxid);
        
        dbms_output.put_line('Suma importe conceptos ' || r_data.SumaImporteCon);
        dbms_output.put_line('Subtotal Comprobante: ' || r_data.Subtotal);
        IF (r_data.SumaImporteCon = r_data.Subtotal) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        dbms_output.put_line('Suma tasa 0.03: ' || r_data.SumaTasa03);
        dbms_output.put_line('Importe 0.03: ' || r_data.ImporteTotal03);
        IF (r_data.SumaTasa03 = r_data.ImporteTotal03) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        dbms_output.put_line('Suma tasa 0.16: ' || r_data.SumaTasa16);
        dbms_output.put_line('Importe 0.16: ' || r_data.ImporteTotal16);
        IF (r_data.SumaTasa16 = r_data.ImporteTotal16) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
        
        dbms_output.put_line('Suma importe impuesto: ' || r_data.SumaImpuesto);
        dbms_output.put_line('El impuesto total: ' || r_data.ImpuestoTotal);
        IF (r_data.SumaImpuesto = r_data.ImpuestoTotal) THEN
            dbms_output.put_line('SI coinciden');
        ELSE
            dbms_output.put_line('NO coinciden');
        END IF;
    END LOOP;
END;
/

这种方式把多次单表查询改成一次关联查询，减少了数据库交互次数，性能更好；同时LEFT JOIN保证即使某个表没有对应数据，也能返回NULL，再用NVL转成默认值，完全避免NO_DATA_FOUND异常。

内容的提问来源于stack exchange，提问作者AXEL EDUARDO ESCOBEDO FIGUEROA

PL/SQL循环执行时触发No Data Found错误的排查求助

1. 捕获NO_DATA_FOUND异常，给变量赋默认值 #

2. 用聚合函数+NVL避免异常 #

3. 合并查询（推荐） #

1. 捕获NO_DATA_FOUND异常，给变量赋默认值

2. 用聚合函数+NVL避免异常

3. 合并查询（推荐）