Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to write a FOR EACH loop in PL/SQL?

Tags:

sql

plsql

Is it possible to run a for each loop on a PL/SQL array?

like image 694
antonpug Avatar asked Mar 22 '12 16:03

antonpug


2 Answers

for i in my_array.first ..my_array.last loop
  --do_something with my_array(i);
end loop;
like image 91
A.B.Cade Avatar answered Oct 05 '22 04:10

A.B.Cade


It's no possible to iterate over the associative arrays with a non numeric index with a FOR-loop. The solution below works just fine.

-- for-each key in (associative-array) loop ... 
declare
    type items_type is table of varchar2(32) index by varchar2(32);
    items items_type;
begin
    items('10') := 'item 10';
    items('20') := 'item 20';
    items('30') := 'item 30';
    dbms_output.put_line('items=' || items.count); 

    <<for_each>> declare key varchar2(32); begin loop 
        key := case when key is null then items.first else items.next(key) end; 
        exit when key is null;
        dbms_output.put_line('item(' || key || ')=' || items(key));
        --do something with an item
    end loop; end for_each;
end;
like image 43
0xdb Avatar answered Oct 05 '22 02:10

0xdb