CODEWITHSUNDEEP
sqloracle-databaseplsql
Suhr415
Suhr415

Reputation: 35

check if two values are present in a table with plsql in oracle sql

I'm trying to create a procedure, that checks if two values are present in a table. The logic is as follows: Create a function called get_authority. This function takes two parameters (found in the account_owner table): cust_id and acc_id, and returns 1 (one), if the customer has the right to make withdrawals from the account, or 0 (zero), if the customer doesn't have any authority to the account. I'm writing plsql and using oracle live sql. I can't figure out how to handle the scenario where a customer has two accounts!

account_owner is seen here:

enter image description here

create or replace function get_authority(
p_cust_id in account_owner.cust_id%type,
p_acc_id in account_owner.acc_id%type
)
return varchar2 
as
v_return number(1);
v_acc_id account_owner.acc_id%type;
v_cust_id account_owner.cust_id%type;
begin
for v_ in (select account_owner.cust_id,
               account_owner.acc_id
               from account_owner
               where p_cust_id = cust_id)
LOOP
if p_cust_id = v_cust_id and p_acc_id = v_acc_id then 
v_return := v_return + 1;
else
v_return := v_return +  0;
end if;
return v_return;
END LOOP;
end;
/

When I check for the cust_id I get the return 0 - but it should be 1??

select get_authority('650707-1111',123) from dual; return: GET_AUTHORITY('650707-1111',123) 0

What do I do wrong?

Upvotes: 0

Views: 923

Answers (1)

Littlefoot
Littlefoot

Reputation: 142733

You got 0? How come; should be NULL. 

v_return number(1);

so it is initially NULL. Later on, you're adding "something" to it, but - adding anything to NULL will be NULL:

SQL> select 25 + null as result from dual;

    RESULT
----------


SQL>

Therefore, set its default value to 0 (zero):

v_return number(1) := 0;

Also, you declared two additional variables:

v_acc_id account_owner.acc_id%type;
v_cust_id account_owner.cust_id%type;

Then you compare them to values passed as parameters; as they are NULL, ELSE is executed.

Furthermore, there's a loop, but you don't do anything with it. If you meant that this:

for v_ in (select account_owner.cust_id,

(rewritten as for v_ in (select cust_id) evaluates to v_cust_id - it does not. Cursor variables are referred to as v_.cust_id (note the dot in between).

Also, if there's only one row per p_cust_id and p_acc_id, why do you use cursor FOR loop at all? To avoid no_data_found or too_many_rows? I wouldn't do that; yes, it fixes such "errors", but is confusing. You'd rather properly handle exceptions.

Here's what you might have done:

Sample data:

SQL> select * From account_owner;

  ACCOW_ID CUST_ID         ACC_ID
---------- ----------- ----------
         1 650707-1111        123
         2 560126-1148        123
         3 650707-1111       5899

Function; if there are more rows per parameters' combination, max function will make sure that too_many_rows is avoided (as it bothers you). You don't really care what it returns - important is that select returns anything to prove that authority exists for that account.

SQL> create or replace function get_authority
  2    (p_cust_id in account_owner.cust_id%type,
  3     p_acc_id  in account_owner.acc_id%type
  4    )
  5  return number
  6  is
  7    l_accow_id   account_owner.accow_id%type;
  8  begin
  9    select max(o.accow_id)
 10      into l_accow_id
 11      from account_owner o
 12      where o.cust_id = p_cust_id
 13        and o.acc_id  = p_acc_id;
 14
 15    return case when l_accow_id is not null then 1
 16                else 0
 17           end;
 18  end;
 19  /

Function created.

Testing:

SQL> select get_authority('650707-1111', 123) res_1,
  2         get_authority('650707-1111', 5899) res_2
  3  from dual;

     RES_1      RES_2
---------- ----------
         1          1

SQL>

Upvotes: 1

Related Questions