René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Iterating over collection variables [PL/SQL]

These are cook book recipes that show, for each of the three collection types, how to iterate over their elements.

Nested tables

declare
  type table_varchar  is table of varchar2(10);

  var_table_varchar  table_varchar;
begin
  var_table_varchar  := table_varchar('one', 'two', 'three', 'four');

  for elem in 1 .. var_table_varchar.count loop
    dbms_output.put_line(elem || ': ' || var_table_varchar(elem));
  end loop;
end;
/
1: one
2: two
3: three
4: four

Index by tables

declare
  type assoc_varchar  is table of varchar2(10) index by pls_integer;

  var_assoc_varchar  assoc_varchar;

  elem varchar2(10);
begin
  var_assoc_varchar(40) := 'forty';
  var_assoc_varchar(10) := 'ten';
  var_assoc_varchar(30) := 'thirty';
  var_assoc_varchar(20) := 'twenty';

  elem := var_assoc_varchar.first;
  while elem is not null loop
    dbms_output.put_line(elem || ': ' || var_assoc_varchar(elem));
    elem := var_assoc_varchar.next(elem);
  end loop;
end;
/
10: ten
20: twenty
30: thirty
40: forty

varrays

declare
  type varray_varchar is varying array(20) of varchar2(10);

  var_varray_varchar varray_varchar;
begin
  var_varray_varchar  := varray_varchar('un', 'deux', 'trois', 'quattre');

  for elem in 1 .. var_varray_varchar.count loop
    dbms_output.put_line(elem || ': ' || var_varray_varchar(elem));
  end loop;
end;
/
1: un
2: deux
3: trois
4: quattre