PL / SQL - 没有数据发现错误处理程序 [英] PL/SQL - WHEN NO DATA FOUND ERROR HANDLER

问题描述

如何修复下面的代码，以便我不会在返回的行的末尾找到 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;

推荐答案

22.50

Stanwood Consulting 6099 15-DEC-95

22.50
Stanwood Consulting 6099 15-DEC-95

7.50

ERROR OTHERS ORA-01403：未找到数据

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

L. EFT 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;
- 显示列名。
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
那么
RAISE NO_DATA_FOUND;
END IF ;

DBMS_OUTPUT.put_line （（vCname）
|| ' ' ||（vOrderid）
| | ' ' ||（vSalesDate）
|| ' ' ||（vTotalValue））;

IF vNR_OF_ORDERS = 0
那么
DBMS_OUTPUT.PUT_LINE（' custid：'
|| ' ' || vcustid
|| ' ' || ' 没有订单'）;

END IF ;

END LOOP;

关闭 Customers_cur;

EXCEPTION
WHEN NO_DATA_FOUND
那么
DBMS_OUTPUT.put_line（' ERROR OTHERS' || SQLERRM）;
WHEN 其他
那么
DBMS_OUTPUT.put_line （' ERROR OTHERS' || SQLERRM）;
END ;

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;

这篇关于PL / SQL - 没有数据发现错误处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！