Reputation: 123
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;
Upvotes: 0
Views: 204
Reputation: 17920
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;
Upvotes: 1