Check if a record contains no rows?

[wallaceoc80]

I'm writing a stored procedure that inserts a new row into a table. In the procedure I'm calling a function that checks that the name and address provided for a customer with ID x is consistent with the data already in the table.

This is the function:

create or replace function purchase_cust_details(aCustID in purchase.customer_ID%TYPE, aCustName in purchase.customer_name%TYPE, aCustAdd in purchase.customer_address%TYPE)
return boolean
is
cursor cursor_t is select customer_name, customer_address from purchase where purchase.customer_ID = aCustID;
record_t cursor_t%rowtype;

begin
open cursor_t;
fetch cursor_t into record_t;
if record_t.customer_name <> aCustName then
return false;
elsif record_t.customer_address <> aCustAdd then
return false;
else
return true;
end if;
close cursor_t;

end;
/

However, if the customer is new to the purchase table the cursor_t query will return no rows and hence the record will be empty.

My question is how do I check if a record (or cursor) returns no rows so I can return true from the function.

Thanks for your help,

Wallace

 

[carp]

You can look at cursor_t%ROWCOUNT to see if it is greater than zero when you are either in or through the cursor loop.
But wouldn't the following be simpler?

create or replace function purchase_cust_details(aCustID in purchase.customer_ID%TYPE, aCustName in purchase.customer_name%TYPE, aCustAdd in purchase.customer_address%TYPE)
return boolean
is
   l_count NUMBER;
BEGIN
   SELECT count(*) INTO l_count
     FROM purchase
    WHERE purchase.customer_ID = aCustID 
      AND record_t.customer_name = aCustName
      AND record_t.customer_address = aCustAdd;
   IF (l_count = 0) THEN 
     RETURN false;
   ELSE
     RETURN true;
   END IF;
END;
/