|
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.
A D V E R T I S E M E N T
Just about all modern programming languages provide support for collections. A collection can be loosely defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index. Commonly used collection types used in the programming world include arrays, maps, and lists.
Different collection types
There are three collection types in PL/SQL:
- index-by tables, also known as associative arrays
- varrays
- nested tables
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.
Use for collections
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.
Collection functions
The collection functions operate on nested tables and varrays.
cardinality
collect
powermultiset
powermultiset_by_cardinality
set
|
