function in plsql

Question

I have created this function and it works:

create or replace function log_in(
p_ssnr in customer.ssnr%type,
p_password in customer.password%type)
return number
as
v_number number(1);
    begin
        if 
        (p_ssnr is not null and p_password is not null) then 
        v_number:= 1;
        else
        v_number:= 0;
        end if;
    return v_number;
    end;

If I then type: select log_in('social security number','the password') from dual; then I get a "1" so it works. But you could basically type whatever you want and it will work so it is not a clean code. Since you can't include subquery in the if statement, how can you create the function so you only return 1 when you actually type a social security number (ssnr) and a password that matches the actual customer table? and otherwise you return 0?

Answer 1

NOt sure if you can have multiple rows for same customer. If yes, then use this:

create or replace function log_in(
p_ssnr in customer.ssnr%type,
p_password in customer.password%type)
return number
as
v_number number(1);
    begin
        select case when count(*) > 0 then 1 else 0 end
        into v_number
        from customer
        where ssnr = p_ssnr and password = p_password;
    return v_number;
    end;

If not then this should be fine:

create or replace function log_in(
p_ssnr in customer.ssnr%type,
p_password in customer.password%type)
return number
as
v_number number(1);
    begin
         count(*) 
        into v_number
        from customer
        where ssnr = p_ssnr and password = p_password;
    return v_number;
    end;