Oracle PL/SQL for DBAs

Working with Collections

This section provides relatively simple examples of each of the different types of collections in this section, with explanations of the major characteristics.

Using an associative array

In the following example, I declare an associative array type and then a collection based on that type. I populate it with four rows of data and then iterate through the collection, displaying the strings in the collection. A more thorough explanation appears after the code.

      1  DECLARE
      2     TYPE list_of_names_t IS TABLE OF person.first_name%TYPE
      3        INDEX BY PLS_INTEGER;
      4     happyfamily   list_of_names_t;
      5     l_row PLS_INTEGER;
      6  BEGIN
      7     happyfamily (2020202020) := 'Eli';
      8     happyfamily (-15070) := 'Steven';
      9     happyfamily (-90900) := 'Chris';
     10     happyfamily (88) := 'Veva';
     11
     12     l_row := happyfamily.FIRST;
     13
     14     WHILE (l_row IS NOT NULL)
     15     LOOP
     16        DBMS_OUTPUT.put_line (happyfamily (l_row));
     17        l_row := happyfamily.NEXT (l_row);
     18     END LOOP;
     19* END;

    SQL> /
    Chris
    Steven
    Veva
    Eli

Line(s)	Description
2–3	Declare the associative array TYPE, with its distinctive INDEX BY clause . A collection based on this type contains a list of strings, each of which can be as long as the first_name column in the person table.
4	Declare the happyfamily collection from the list_of_names_t type.
9–10	Populate the collection with four names. Notice that I can use virtually any integer value that I like. The row numbers don’t have to be sequential in an associative array; they can even be negative!