oracle 在循环PLSQL中未找到数据

zqry0prt 于 2023-03-29 发布在 Oracle

关注(0)|答案(1)|浏览(173)

所以基本上我想从不同的表中获取所有信息，但我有一个麻烦，当我使用select into时，它说没有找到数据

BEGIN
    FOR id_rec IN (SELECT customertrxid FROM comprobante) LOOP
        current_id := id_rec.customertrxid;
        DBMS_OUTPUT.PUT_LINE('ID: ' || current_id);
        SELECT sum(IMPORTE) into SumaImporteCon FROM Conceptos where current_id = customertrxid;
        SELECT SUBTOTAL into Subtotal FROM Comprobante where current_id = customertrxid;
        SELECT sum(IMPORTE) into SumaTasa03 FROM Traslados where TasaOcuota = '0.030000' and current_id = customertrxid;
        SELECT IMPORTE into ImporteTotal03 FROM TrasladosTotal where TasaOcuota = '0.030000' and current_id = customertrxid;
        SELECT sum(IMPORTE) into SumaTasa16 FROM Traslados where TasaOcuota = '0.160000' and current_id = customertrxid;
        SELECT IMPORTE into ImporteTotal16 FROM TrasladosTotal where TasaOcuota = '0.160000' and current_id = customertrxid;
        SELECT sum(IMPORTE) into SumaImpuesto FROM TrasladosTotal where current_id = customertrxid;
        SELECT TOTALIMPUESTOSTRASLADOS into ImpuestoTotal FROM ImpuestosTotal where current_id = customertrxid;
        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;
/
/

oracle

来源：https://stackoverflow.com/questions/75859707/no-data-found-inside-the-loop-plsql

1条答案

按热度按时间

oknrviil1#

在循环中使用OUTER JOIN s。类似于（未经测试，但它给了你大致的概念）：

BEGIN
  FOR id_rec IN (
    SELECT cb.customertrxid,
           cb.subtotal,
           c.SumaImporteCon,
           t.SumaTasa03,
           t.SumaTasa16,
           tt.ImporteTotal03,
           tt.ImporteTotal16,
           tt.SumaImpuesto,
           i.TOTALIMPUESTOSTRASLADOS AS ImpuestoTotal
    FROM   comprobante cb
           LEFT OUTER JOIN (
             SELECT current_id,
                    sum(IMPORTE) AS SumaImporteCon
             FROM   Conceptos
             GROUP BY current_id
           ) c
           ON (cb.current_id = c.customertrxid)
           LEFT OUTER JOIN (
             SELECT current_id,
                    sum(CASE WHEN TasaOcuota = '0.030000' THEN IMPORTE END) AS SumaTasa03,
                    sum(CASE WHEN TasaOcuota = '0.160000' THEN IMPORTE END) AS SumaTasa16
             FROM   Traslados
             GROUP BY current_id
           ) t  
           ON (t.current_id = c.customertrxid)
           LEFT OUTER JOIN (
             SELECT current_id,
                    sum(CASE WHEN TasaOcuota = '0.030000' THEN IMPORTE END) AS ImporteTotal03,
                    sum(CASE WHEN TasaOcuota = '0.160000' THEN IMPORTE END) AS ImporteTotal16,
                    sum(IMPORTE) AS SumaImpuesto
                FROM   TrasladosTotal
             GROUP BY current_id
           ) tt
           ON (tt.current_id = c.customertrxid)
           LEFT OUTER JOIN ImpuestosTotal i
           ON (i.current_id = c.customertrxid)
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('ID: ' || id_rec.customertrxid);
    dbms_output.put_line('Suma importe conceptos ' || id_rec.SumaImporteCon);
    dbms_output.put_line('Subtotal Comprobante: ' || id_rec.Subtotal);
    IF id_rec.SumaImporteCon = id_rec.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: ' || id_rec.SumaTasa03);
    dbms_output.put_line('Importe 0.03: ' || id_rec.ImporteTotal03);
    IF id_rec.SumaTasa03 = id_rec.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: ' || id_rec.SumaTasa16);
    dbms_output.put_line('Importe 0.16: ' || id_rec.ImporteTotal16);
    IF id_rec.SumaTasa16 = id_rec.ImporteTotal16 THEN
      dbms_output.put_line('SI coinciden');
    ELSE
      dbms_output.put_line('NO coinciden');
    END IF;
    dbms_output.put_line('Suma importe impuesto: ' || id_rec.SumaImpuesto);
    dbms_output.put_line('El impuesto total: ' || id_rec.ImpuestoTotal);
    IF id_rec.SumaImpuesto = id_rec.ImpuestoTotal THEN
      dbms_output.put_line('SI coinciden');
    ELSE
      dbms_output.put_line('NO coinciden');
    END IF;
  END LOOP;
END;
/

赞(0）回复(0）举报 2023-03-29

我来回答

oracle 在循环PLSQL中未找到数据

1条答案

相关问题

热门标签

最新问答