How to check parameter value existence in a procedure?

Question

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 the error istead 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 do I do wrong? I guest I need to check the value before I make SELECT request, right?

Answer 1

Your problem is actually caused by your check:

  SELECT COUNT (cus_code)
    INTO value_check
    FROM customer
   WHERE cus_code = temp_ccode;

The above query will never return NULL, which is what you are checking for. If no values in the table match the parameter temp_ccode then the value_check will be 0. This in turn means your IF statement is incorrect and this causes your error later in your code.

There's a simpler and more efficient way of doing this though. You can use SQL%ROWCOUNT to find out how many rows were effected by your UPDATE. If the return value is 0 then your customer doesn't exist.

create or replace procedure cust_pay (
     Ptemp_ccode in number
   , Ppay_amount in number
     ) is

   l_balance number;

begin

   update customer
      set cus_balance = cus_balance - Ppay_amount
    where cus_code = Ptemp_ccode;

   if SQL%ROWCOUNT = 0 then
      dbms_output.put_line('The customer was not found.');
   else
      select cus_balance 
        into l_balance
        from customer
       where cus_code = temp_ccode;

      if l_balance < 0 then
         dbms_output.put_line('the client owes us ' || l_balance);
      else
         dbms_output.put_line('customer new balance is ' || l_balance);
      end if;
   end if;

end;

There's no need to handle the NO_DATA_FOUND exception in the select... into ... here as you've already guaranteed that the cus_code exists by your UPDATE statement.

Please note the other changes I've made:

1. Different naming conventions for parameters and variables so it's clear in the code which is which.
2. Removal of the additional nested PL/SQL block, which was unnecessary.

Generally speaking you should never use dbms_ouput.put_line in a PL/SQL block as you have to be there to see what's happening. It's fine for debugging processes but is fairly useless in production code.

It's also possible to use the RETURN statement to avoid the nested IF statements, which I think makes the code cleaner and easier to read; though this is a judgement call.

create or replace procedure cust_pay (
     Ptemp_ccode in number
   , Ppay_amount in number
     ) is

   l_balance number;

begin

   update customer
      set cus_balance = cus_balance - Ppay_amount
    where cus_code = Ptemp_ccode;

   if SQL%ROWCOUNT = 0 then
      return;
   end if;

   select cus_balance 
     into l_balance
     from customer
    where cus_code = temp_ccode;

   if l_balance < 0 then
      dbms_output.put_line('The client owes us ' || l_balance);
   else
      dbms_output.put_line('New balance is ' || l_balance);
   end if;

end;

All of this assumes that your CUSTOMER table is unique on cus_code.

Answer 2

Egor Skriptunoff has just posted an intriguing answer and I don't want to claim any credit for the idea so I'm posting this as a different answer. It's actually possible to do everything in a single statement.

Based on the principle advocated in my other answer that it doesn't matter if you update the balance of a non-existent customer you can combine my own and Egor's answers to come up with this:

create or replace procedure cust_pay (
     Ptemp_ccode in number
   , Ppay_amount in number
     ) is

   l_balance number;

begin

   update customer
      set cus_balance = cus_balance - Ppay_amount
    where cus_code = Ptemp_ccode
   returning cus_balance
     into l_balance;

   if SQL%ROWCOUNT = 0 then
      dbms_output.put_line('The customer was not found.');
   elsif l_balance < 0 then
      dbms_output.put_line('The client owes us ' || l_balance);
   else
      dbms_output.put_line('New balance is ' || l_balance);
   end if;

end;

The benefit of doing everything in a single statement is an increase in speed. Once again you don't need to worry about the NO_DATA_FOUND exception as you're doing an update only.

Everything I said in my previous answer still holds true, count(*) in a select... into... will never return null, always a number.

Answer 3

Corrected version of your code is here:

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

   IF value_check = 0 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
      RETURNING cus_balance
      INTO cbalance;

      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;

Answer 4

You could have a check before the sql query to verify parameter values

Another approach is to use EXCEPTION NO_DATA_FOUND

CREATE OR REPLACE PROCEDURE cust_pay (temp_ccode   IN NUMBER,
                                      pay_amount   IN NUMBER,errcode OUT 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;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.put_line ('no data***' || SQLERRM);
      errcode := 1;
END;