Collections in PL/SQL

In PL/SQL a collection variable is a variable that can store zero, one ore more elements of a specific type (either an internal data type or a user defined data types). The type of the variable is itself a user defined type. Since a collection is a user defined type, a collection type can store collections as well.

There are three collection types in PL/SQL:

• nested tables
• index-by tables
  also known as associative arrays
• varrays

Nested tables extend the functionality of index-by tables. The main difference is that nested tables can be stored in a table column while index by tables can not.

• Declaration of collection types
• Constructors for collection types
• Iterating over collection variables

This example shows how a table can be returned from a pl/sql function.

poor man's text index is an example that uses collections to search in the middle of words in a table. (ie where filed like '%word%')

Index by tables are also used in bulk collect statements.

The collection functions operate on nested tables and varrays.

deleting and inserting from/to a nested table.