PL / SQL - 没有数据发现错误处理程序

本文介绍了PL / SQL - 没有数据发现错误处理程序的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何修复下面的代码，以便我不会在返回的行的末尾找到 ERROR OTHERS ORA-01403：找不到数据？它不应该在那里如果我要退回记录吗？

Stanwood Consulting 6099 15-DEC-95 $ 22.50

Stanwood Consulting 6099 15 -DEC-95 $ 7.50

ERROR OTHERS ORA-01403：未找到数据

How can I fix the code below so that I won''t get ERROR OTHERS ORA-01403: no data found at the end of the returned rows? It shouldn''t be there If I am returning records?

Stanwood Consulting 6099 15-DEC-95 $22.50
Stanwood Consulting 6099 15-DEC-95 $7.50
ERROR OTHERS ORA-01403: no data found

SET SERVEROUTPUT ON
    DECLARE
        vcustid        CUSTOMERS.custid%type;
        vCname         CUSTOMERS.cname%type;
        vcustid_2      ORDERS.custid%type;
        vOrderid       ORDERS.orderid%type;
        vSalesDate     ORDERS.salesdate%type;
        vTotalValue    varchar2(100);
        vNR_OF_ORDERS  pls_integer;

   CURSOR  Customers_cur

   IS

     SELECT C.custid,C.cname,O.orderid,O.salesdate,
           to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99') AS vTotalValue,
           COUNT(O.orderid) NR_OF_ORDERS--COUNT(OI.orderid) NR_OF_ORDERS
     FROM CUSTOMERS C
           LEFT OUTER JOIN ORDERS O
            ON O.custid = C.custid

            LEFT OUTER JOIN ORDERITEMS OI
            ON O.orderid = OI.orderid

            LEFT OUTER JOIN INVENTORY I
            ON OI.partid = I.partid

   WHERE C.custid = &custid
   GROUP BY C.custid,C.cname,O.orderid,O.salesdate,OI.qty,I.price
   ORDER BY vTotalValue DESC;

   Customer_Check   Customers_cur%ROWTYPE;
   BEGIN

    OPEN Customers_cur;
   --to show column names.
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
      DBMS_OUTPUT.PUT_LINE('CustomerName           OrderId     SaleDdate         TotalValue');
      DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
   LOOP

     FETCH Customers_cur  INTO vCustid,vCname,vOrderid,vSalesDate,vTotalValue,vNR_OF_ORDERS;

     IF Customers_cur%NOTFOUND
     THEN
     RAISE NO_DATA_FOUND;
     END IF;

      DBMS_OUTPUT.put_line( (vCname)
                       || '  '  || (vOrderid)
                       || '  '  || (vSalesDate)
                       || '  '  || (vTotalValue ));

     IF vNR_OF_ORDERS = 0
     THEN
      DBMS_OUTPUT.PUT_LINE(  'custid :'
                        || '  '  || vcustid
                        || '  '  ||  ' has no orders');

    END IF;

    END LOOP;

     CLOSE Customers_cur;

  EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    DBMS_OUTPUT.put_line('ERROR OTHERS  ' || SQLERRM);  
    WHEN OTHERS
    THEN
       DBMS_OUTPUT.put_line('ERROR OTHERS  ' || SQLERRM);
  END;

6099

PL / SQL - 没有数据发现错误处理程序

问题描述

推荐答案