CODEWITHSUNDEEP
plsql
Coding Duchess
Coding Duchess

Reputation: 6909

Checking for multiple conditions with PL/SQL

In my code I need to check a variable for multiple values in the IF statement. I am using:

IF var1 = 1 OR var1 = 12 OR var1 = 15 or var1 = 5 THEN

is there a way to do it more gracefully, line an IN clause in SQL?

I tried using MEMBER OF

IF var1 MEMBER OF (1,5,12,15) THEN

but that did not work

Upvotes: 1

Views: 1323

Answers (2)

Belayer
Belayer

Reputation: 14886

As @OldProgrammer tried to indicate, you do not need Member of. That is typically for much more complicated data structures. For a simple scalar value a simple in (list) is sufficient.

declare
  var1    number := 2;
  var2    number := 12;
begin
  dbms_output.put( 'var1 value ' || var1);
  if var1 in (1,5,12,15) then
     dbms_output.put_line(' is in list.');
  else
     dbms_output.put_line(' is not in list.');
  end if;
  
  dbms_output.put( 'var2 value ' || var2);
  if var2 in (1,5,12,15) then
     dbms_output.put_line(' is in list.');
  else
     dbms_output.put_line(' is not in list.');
  end if;  
end;

Results:
var1 value 2 is not in list.
var2 value 12 is in list.

Upvotes: 4

Littlefoot
Littlefoot

Reputation: 142778

That's not really what you wanted (more gracefully), but - does the job. See if it helps.

SQL> declare
  2    var1    number := 2;
  3    l_cnt   number;
  4  begin
  5    select count(*)
  6    into l_cnt
  7    from table(sys.odcinumberlist(1,5,12,15))
  8    where column_value = var1;
  9
 10    if l_cnt > 0 then
 11       dbms_output.put_line('Yes');
 12    else
 13       dbms_output.put_line('No');
 14    end if;
 15  end;
 16  /
No                                              --> 2 doesn't exist in the list

PL/SQL procedure successfully completed.

SQL> l2
  2*   var1    number := 2;
SQL> c/2/15
  2*   var1    number := 15;
SQL> /
Yes                                             --> 15 exists in the list

PL/SQL procedure successfully completed.

SQL>

Upvotes: 0

Related Questions