How to check parameter value existence in a procedure

plsqlstored-procedures

The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking:

ORA-01403: no data found
ORA-06512: at "XXXXX.CUST_PAY", line 19
ORA-06512: at line 2

The procedure:

CREATE OR REPLACE PROCEDURE cust_pay (temp_ccode IN NUMBER, pay_amount IN NUMBER)AS
BEGIN
    DECLARE 
      Value_check NUMBER;
      cbalance NUMBER;
    BEGIN
        SELECT Count(cus_code) 
        INTO value_check
        FROM customer
        WHERE cus_code = temp_ccode;

        IF value_check IS NULL THEN
            Dbms_Output.put_line('the value was not FOUND');
        ELSE
            UPDATE customer
            SET cus_balance = cus_balance - pay_amount
            WHERE cus_code = temp_ccode;

            SELECT cus_balance 
            INTO cbalance
            FROM customer
            WHERE cus_code = temp_ccode;

            IF cbalance < 0 THEN
                Dbms_Output.put_line('The client owes us ' || cbalance);
            ELSE
                Dbms_Output.put_line('Customer new balance is ' || cbalance);
            END IF;
        END IF;
    END;
END;

What did I do wrong? I guess I need to check the value before I SELECT, right?

Best Answer

If you use a count() aggregate on a resultset that has no rows, you'll get zero, not a null.

If a SELECT doesn't find anything, an exception is raised (NO_DATA_FOUND), and you need to catch that exception in an EXCEPTIONS block.

exceptions
  when no_data_found:
    dbms_output.put_line('ooups');
end;

In your case the second select is raising the exception - since zero is not null, your first if branch will not be taken. (update doesn't raise an exception if it doesn't update anything.)

Your procedure is generally too complicate already though. You can declare variables in the procedure "header" directly, remove both selects - all the info you want can be gotten from a single update.

create or replace
procedure cust_pay(temp_code number, pay_amount number)
as
  new_balance number;
begin
  update customer
  set cbalance = cbalance - pay_amount
  where cust_code = temp_code
  returning cbalance into new_balance;

  if sql%rowcount = 0 then
    dbms_output.put_line('No such customer: ' || temp_code);
  elsif new_balance < 0 then
    dbms_output.put_line('Negative: ' || new_balance);
  else
    dbms_output.put_line('Positive: ' || new_balance);
  end if;
end;

(This assumes that cust_code is unique.)

If the update didn't do anything, sql%rowcount will be zero, which tells you whether that customer exists or not (equivalent to your first select). The update itself is unchanged from your code except that the returning ... into avoids the need for your third select and grabs the updated value directly into a local variable.

Related Question