CODEWITHSUNDEEP
oracle-databasesyntaxplsql
user60108
user60108

Reputation: 3452

IN and IF operators in PL/SQL

I'm trying to do (in PL/SQL)

if ((var1, var2) in ( select number1, number2 from.......) ) then
....
end if;

Why does this not work? How would be the right thing to do?

Upvotes: 0

Views: 251

Answers (2)

hol
hol

Reputation: 8423

The answer is that in PL/SQL you have to read (select) something into variables and then use it in an IF statement. You could do this in various ways but you cannot do this within the IF clause. Chorel's answer is quite inventive on this and he deserves some praise for the answer.

You could also just do

declare
  l_count number;
  var1 number := 1; -- or whatever the number
  var2 number := 2;
begin
  select count(*) 
    into l_count
    from mytable
   where number1 = var1 and number2 = var2;
  if l_count > 0 then
     ... the row exists
  end if;
end;

But the following does not work

if (select count(*) from mytable where (number1,number2) in (1,2)) > 0 then -- WRONG
    ...
end if;

If you want to use the in functionality you have to resort to table dual

   where (number1, number2) in (select var1, var2 from dual);

Like this

declare
  l_count number;
  var1 number := 1; -- or whatever the number
  var2 number := 2;
begin
  select count(*) 
    into l_count
    from mytable
   where (number1, number2) in (select var1, var2 from dual);
  if l_count > 0 then
     ...
  end if;
end;

Upvotes: 2

Chorel
Chorel

Reputation: 362

Try this:

declare
  l_exist number(1);
  var1 ...
  var2 ...
begin
  --obtain var1 & var2 
  -- ...
  select 
    case 
      when exists(select1 from ...
                  where number1 = var1 and number2 = var2) then 1
      else 0
    end into l_exist
  from dual;

  if l_exist = 1 
  then
    -- do what you want here
  end if;
end;

Upvotes: 2

Related Questions