Raise Exception when record not found

Question

I have a table that includes customer ID and order ID and some other data.

I want to create a procedure that takes customer ID as input and look inside the table.

if that customer exists then print the order details for that customer and if customer does not exist then raise an exception "Customer not found."

I have this code, but it's not working properly, or maybe I have the wrong approach to this question.

CREATE OR REPLACE PROCEDURE order_details(customer NUMBER)
  IS
  CURSOR order_cursor IS
    SELECT ORDER_ID, ORDER_DATE, TOTAL, CUSTOMER_ID
      FROM PRODUCT_ORDER
  WHERE CUSTOMER_ID = customer ;
  order_row order_cursor%ROWTYPE ;
  customer_error EXCEPTION ;

  BEGIN
        FOR order_row IN order_cursor
    LOOP
      IF order_cursor%FOUND THEN
    dbms_output.put_line ('order id = ' || order_row.ORDER_ID) ;
  ELSE
    RAISE customer_error ;
  END IF;
  END LOOP;

  EXCEPTION
    WHEN customer_error THEN
    dbms_output.put_line ('no customer' ) ;
      END;

So if I run the procedure with this line

BEGIN
  order_details(103);
END;

I get two results because order exists for this customer.

and if I run the procedure with this line

BEGIN
  order_details(101);
END;

I don't get anything (not even the error ) because there is no order for that customer.

Table Data

Answer 1

You must use an "Explicit Cursor" instead of "Cursor FOR LOOP". Because the latter just enter the code between LOOP and END LOOP when the query returns more than one record.

CREATE OR REPLACE PROCEDURE order_details(customer NUMBER)
  IS
  CURSOR order_cursor IS
    SELECT ORDER_ID, ORDER_DATE, TOTAL, CUSTOMER_ID
      FROM PRODUCT_ORDER
  WHERE CUSTOMER_ID = customer ;
  order_row order_cursor%ROWTYPE ;
  customer_error EXCEPTION ;

  BEGIN
  OPEN order_cursor;
  LOOP  
    FETCH order_cursor INTO order_row;
    EXIT WHEN order_cursor%NOTFOUND;
    dbms_output.put_line ('order id = ' || order_row.ORDER_ID);
  END LOOP;

  IF order_cursor%rowcount = 0 THEN
    RAISE customer_error;
  END IF;

  CLOSE order_cursor;

  EXCEPTION
    WHEN customer_error THEN
    dbms_output.put_line ('no customer' ) ;

END;

Regards