Oracle PL equivalent of foreach-loop

user_1747834 0
Viewed 1647

I need to loop over about 10 strings, which are all known to me in advance -- I don't need to SELECT them from anywhere.

I attempted:

BEGIN
    FOR tab IN ('one', 'two', 'three')
    LOOP
        EXECUTE IMMEDIATE 'DROP TABLE ' || tab;
    END LOOP;
END;

But that does not work (LOOP is unexpected)...

Is there a way to do this?

plsql oracle loops
3 Answers
declare
   type t_strings is table of varchar2(100);
   strings t_strings:=t_strings('one','two','three');
begin
   for i in 1..strings.count loop
      dbms_output.put_line(strings(i));
   end loop;
end;
/

Results:

one
two
three

Or you can use your own short-cut functions: http://orasql.org/2017/10/02/plsql-functions-iterate-and-keys-for-associative-arrays/

Btw, Oracle 21 has some new features for FOR-LOOP:

So it can be rewritten as:

declare
   type t_strings is table of varchar2(100);
begin
   for str in values of t_strings('one','two','three') loop
      dbms_output.put_line(str);
   end loop;
end;
/
user_4291000

"almost real" foreach loop:

declare 
    type tabList is table of varchar2(32);
    tabs tabList := tabList ('one', 'two', 'three');
    curr varchar2(32) := tabs.first;
begin
    <<foreach>> loop exit foreach when curr is null;
        execute immediate 'create table '||tabs(curr)||' (col char(1))';
        execute immediate 'drop table '||tabs(curr)||' purge';
        curr := tabs.next (curr);
    end loop;
end;
/
PL/SQL procedure successfully completed.
user_65710200 
begin
for tab_rec in (  select 'abc' val from dual
            union 
            select '123' val from dual )
loop
 EXECUTE IMMEDIATE 'DROP TABLE ' || tab_rec.val;
end loop;
end;
user_17455440
Related