Loop through pre defined values

for-loop, oracle, plsql, stored-procedures

Solution

You could do this, though probably not as slick as you'd like:

declare
  type nt_type is table of number;
  nt nt_type := nt_type (1, 3, 5);
begin
  for i in 1..nt.count loop
    dbms_output.put_line(nt(i));
  end loop;
end;

If you create a type in the database:

create type number_table is table of number;

then you can do this:

begin
  for r in (select column_value as var from table (number_table (1, 3, 5))) loop
    dbms_output.put_line(r.var);
  end loop;
end;

Also, as A.B.Cade has commented below there are database types that come with Oracle that you can use, such as `sys.dbms_debug_vc2coll`:

begin
  for r in (select column_value as var from table (dbms_debug_vc2coll (1, 3, 5))) loop
    dbms_output.put_line(r.var);
  end loop;
end;

Problem

Is there a way to do a "for each" in oracle, something like this: ``` begin for VAR in {1,2,5} loop dbms_output.put_line('The value: '||VAR); end loop; end; ``` I know you can do something like: ``` begin for VAR in 1..5 loop if VAR in(1,3,5) then dbms_output.put_line('The value: '||VAR); end if; end loop; end; ``` But isn't there a way to do this in a nicer way? Defining a set of values and iterating through them? Thanks.

Original source