Skip to content
Advertisement

Is it possible to iterate over a list of strings and conditionally query an oracle database table based on the current value using SQL or PL/SQL?

Is it possible to query conditionally based on the current value in a list of strings using either SQL or PL/SQL? I am working with an Oracle Database if that helps?

Example of desired functionality (pseudo-code):

for stringVal in ('string1', 'string2', 'string3'):
  if (stringVal == 'string2'):
    select * from SCHEMA.TABLE where CONDITION;
  else:
    select * from OTHER_SCHEMA.OTHER_TABLE where CONDITION;

Advertisement

Answer

Is it possible? Yes. For example:

SQL> declare
  2    l_result number;
  3  begin
  4    for stringval in (select 'string1' col from dual union all
  5                      select 'string2'     from dual union all
  6                      select 'string3'     from dual
  7                     )
  8    loop
  9      if stringval.col = 'string2' then
 10         select max(sal)
 11           into l_result
 12           from scott.emp;
 13      else
 14         select min(deptno)
 15           into l_result
 16           from mike.dept;
 17      end if;
 18      dbms_output.put_line('Stringval.col = ' || stringval.col || '; result = ' || l_result);
 19    end loop;
 20  end;
 21  /
Stringval.col = string1; result = 10
Stringval.col = string2; result = 5000
Stringval.col = string3; result = 10

PL/SQL procedure successfully completed.

SQL>

Note, however, that

  • IF belongs to PL/SQL
  • SELECT has to have its INTO; my example is a simple one and returns just a single value into a scalar variable. Depending on what your queries really return, you might not be able to do that but use e.g. a collection or a ref cursor instead
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement