An Easy Guide to PL/SQL Collections

 

A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript".

To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position of each element in the list. If we number the positions sequentially starting from one, we could say:

color[1] = red, color[2] = blue, and so on.

Here, color is the name of the collection, and the numbers within [] are the subscripts.

PL/SQL has three collection types. In this article, we’ll look at a chart for comparing the three, their advantages and limitations, and which one to use for your needs.

To introduce the three collection types:

1. Index by tables: Also called associative arrays.
2. Nested tables
3. Varrays: Also called variable arrays

The chart below lists the properties of the three collection types on a set of parameters such as size, ease of modification, persistence, etc.

	Index By Tables	Nested Tables	Varrays
Size	Unbounded i.e. the number of elements it can hold is not pre-defined	Unbounded i.e. the number of elements it can hold is not pre-defined	Bounded i.e. holds a declared number of elements, though this number can be changed at runtime
Subscript Characteristics	Can be arbitrary numbers or strings. Need not be sequential.	Sequential numbers, starting from one	Sequential numbers, starting from one
Database Storage	Index by tables can be used in PL/SQL programs only, cannot be stored in the database.	Can be stored in the database using equivalent SQL types, and manipulated through SQL.	Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables)
Referencing and lookups	Works as key-value pairs. e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000;	Similar to one-column database tables. Oracle stores the  nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1.	Standard subscripting syntax e.g.  color(3) is the 3rd color in varray color
Flexibility to changes	Most flexible. Size can increase/ decrease dynamically.   Elements can be added to any position in the list and deleted from any position.	Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions.	Not very flexible. You must retrieve and update all the elements of the varray at the same time.
Mapping with other programming languages	Hash tables	Sets and bags	Arrays

Which Collection Type To Use?

You have all the details about index by tables, nested tables and varrays now. Given a situation, will one should you use for your list data?

Here are some guidelines.

Use index by tables when:

• Your program needs small lookups
• The collection can be made at runtime in the memory when the package/ procedure is initialized
• The data volume is unknown beforehand
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential)
• You do not need to store the collection in the database

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database
• The number of elements of the varray is known in advance
• The data from the varray is accessed in sequence
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray

Related Posts: