Oracle PL/SQL function: if parameter not in table column => continue program

Question

Please see my code below. If the parameter p_cust_id is not in the column cust_id I want "0" to be printed out (which means not logged in). If it is in cust_id I want oracle to continue the second part of the code (the part below the empty row).

I have tried to solve this by inserting the values of column cust_id in a cursor and then inserting it in the variable v_cust_id. Perhaps this results in unnecessarily much code?

My problem is that the program does not seem to run the second part even if p_cust_id is in cust_id. It just prints out "0" even though the customer ID and the password are correct.

Before I added the cursor the program worked as it was supposed to, unless the parameter p_cust_id didn't match any value in the cust_id column. If this was the case nothing was printed out.

create or replace function log_in(
  p_cust_id in customer.cust_id%type,
  p_passwd in customer.passwd%type)
return varchar2
as
  cursor c_cust_id is select cust_id from customer;
  v_cust_id customer.cust_id%type;
  v_passwd customer.passwd%type;
  v_logged_in number(1);
  v_not_logged_in number(1);
begin
v_logged_in := 1;
v_not_logged_in := 0;
if not c_cust_id%isopen then
open c_cust_id;
end if;
loop
  fetch c_cust_id
  into v_cust_id;
  exit when c_cust_id%notfound;
end loop;
if p_cust_id not in(v_cust_id) then
  return v_not_logged_in;
end if;
close c_cust_id;

select passwd
into v_passwd
from customer
where cust_id = p_cust_id;
if v_passwd = p_passwd then
 return v_logged_in;
else
 return v_not_logged_in; 
end if;
end;

Maheswaran Ravisankar · Accepted Answer · 2015-10-02 11:02:09Z

I could see, you don't need a cursor at all, to check if the cust_id is in the table. Just search for the cust_id in the table, and attempt to fetch the password. If it exists, you get the value, and NO_DATA_FOUND exception otherwise which means not logged in.

BEGIN 
  select passwd
    into v_passwd
  from customer
   where cust_id = p_cust_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  return v_not_logged_in;
END;

Full code will be:

create or replace function log_in(
  p_cust_id in customer.cust_id%type,
  p_passwd in customer.passwd%type)
return varchar2
as
  v_cust_id customer.cust_id%type;
  v_passwd customer.passwd%type;
  v_logged_in number(1);
  v_not_logged_in number(1);
begin
v_logged_in := 1;
v_not_logged_in := 0;

BEGIN 
  select passwd
    into v_passwd
  from customer
   where cust_id = p_cust_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
  return v_not_logged_in;
END;

if v_passwd = p_passwd then
 return v_logged_in;
else
 return v_not_logged_in; 
end if;
end;

Collectives™ on Stack Overflow

Oracle PL/SQL function: if parameter not in table column => continue program

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related