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

Question

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

Avoid looping constructs in SQL. Start thinking in SET based operations. http://www.simple-talk.com/sql/database-administration/the-road-to-professional-database-development-set-based-thinking/ — Oded, Mar 22 '12 at 16:57
The Oracle documentation is comprehensive, online and free. You shoudl learn how to use it to answer trivial syntax questions for yourself. Here is the section on PL/SQL loops. http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/controlstructures.htm#i8296 — APC, Mar 22 '12 at 17:51
Reading the documentaion would also have answered your subsequent question http://stackoverflow.com/q/9827581/146325 too — APC, Mar 22 '12 at 17:53
@APC Useless rtfm link. A `for-each` loop iterates over a list of values. The Oracle documentation describes only a basic `for` loop iterating over numbers. — ceving, Oct 20 '14 at 16:01

score 13 · Accepted Answer · answered Mar 22 '12 at 16:59

13

for i in my_array.first ..my_array.last loop
  --do_something with my_array(i);
end loop;

answered Mar 22 '12 at 16:59

A.B.Cade

16,735
1
37
53

1

As some comments in ceving's link stated, this would return an error if `my_array` is empty. For that case, it is better to use `FOR i IN 1 .. my_array.COUNT` – plasmaTonic Apr 30 '18 at 10:28

0xdb · Answer 2 · 2018-05-23T07:48:29.367

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;

Mateusz Żurek · Answer 3 · 2022-07-07T10:52:39.650

In my opinion 0xdb solution is best. Even if you have numeric index it is better to us this construct

DECLARE
  TYPE TTab_SomeTable IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
  --
  vt_SomeTable TTab_SomeTable;
  vi_Idx       NUMBER;
BEGIN
  vt_SomeTable(1) := 'First';
  vt_SomeTable(2) := 'Second';
  vt_SomeTable(5) := 'Fifth';
  vt_SomeTable(10) := 'Tenth';

  
  vi_Idx := vt_SomeTable.FIRST;
  LOOP
    --
    EXIT WHEN vi_Idx IS NULL;
    --
    dbms_output.Put_Line('vt_SomeTable(' || vi_Idx || ') = ' || vt_SomeTable(vi_Idx));
    --
    vi_Idx := vt_SomeTable.NEXT(vi_Idx);
    --
  END LOOP vi_Idx;
END;

It is not susceptible to index discontinuity like below two examples, which will fail on index 3:

DECLARE
  TYPE TTab_SomeTable IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
  --
  vt_SomeTable TTab_SomeTable;
BEGIN
  vt_SomeTable(1) := 'First';
  vt_SomeTable(2) := 'Second';
  vt_SomeTable(5) := 'Fifth';
  vt_SomeTable(10) := 'Tenth';

  -- Throw No_data_found on vi_Idx = 3
  FOR vi_Idx IN vt_SomeTable.FIRST .. vt_SomeTable.LAST
  LOOP
    dbms_output.Put_Line('vt_SomeTable(' || vi_Idx || ') = ' || vt_SomeTable(vi_Idx));
  END LOOP vi_Idx;
END;



DECLARE
  TYPE TTab_SomeTable IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
  --
  vt_SomeTable TTab_SomeTable;
BEGIN
  vt_SomeTable(1) := 'First';
  vt_SomeTable(2) := 'Second';
  vt_SomeTable(5) := 'Fifth';
  vt_SomeTable(10) := 'Tenth';

  -- Throw No_data_found on vi_Idx = 3.
  FOR vi_Idx IN 1 .. vt_SomeTable.COUNT
  LOOP
    dbms_output.Put_Line('vt_SomeTable(' || vi_Idx || ') = ' || vt_SomeTable(vi_Idx));
  END LOOP vi_Idx;
END;

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

3 Answers3