A collection is an ordered group of elements, all of the same data type and are called elements. Each element is addressed by a unique subscript that represents its position in the collection. You access each element by its unique subscript.
PL/SQL has three collection types,
- An associative array (or index-by table)
- Nested table
- Variable-size array (varray)
Oracle documentation provides the following characteristics for each type of collections
|Collection Type||Number of Elements||Subscript Type||Dense or Sparse||Where Created||Can Be Object Type Attribute|
|An associative array (or index-by table)||Unbounded||String or integer||Either||Only in PL/SQL block||No|
|Nested table||Unbounded||Integer||Starts dense, can become sparse||Either in PL/SQL block or at the schema level||Yes|
|Variable-size array (varray)||Bounded||Integer||Always dense||Either in PL/SQL block or at the schema level||Yes|
Unbounded – there is no limit to the number of elements in the collection.
Dense means that the collection has no gaps between elements – every element between the first and last element is defined and has a value (which can be NULL).
We have already discussed varray in the chapter ‘PL/SQL – Arrays’.
Associative Arrays (Index-By Tables)
An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; table_name type_name;
Nested Table Collections
A nested table is like a one-dimensional array with an arbitrary number of elements.
A nested table differs from an array in these important ways:
- An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
- An array is always dense (that is, it always has consecutive subscripts). A nested array is dense initially, but it can become sparse because you can delete elements from it.
TYPE type_name IS TABLE OF element_type [NOT NULL]; table_name type_name;
This declaration is similar to the declaration of an index-by table, but there is no INDEX BY clause.
A collection method is a built-in PL/SQL subprogram that returns information about a collection or operates on a collection. Collection methods make collections easier to use and make your applications easier to maintain.
The following table lists the methods and their purpose
|EXISTS(n)||Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.|
|COUNT||Returns the number of elements that a collection currently contains.|
|LIMIT||Checks the maximum size of a collection.|
|FIRST||Returns the first (smallest) index numbers in a collection that uses the integer subscripts.|
|LAST||Returns the last (largest) index numbers in a collection that uses the integer subscripts.|
|PRIOR(n)||Returns the index number that precedes index n in a collection.|
|NEXT(n)||Returns the index number that succeeds index n.|
|EXTEND||Appends one null element to a collection.|
|EXTEND(n)||Appends n null elements to a collection.|
|EXTEND(n,i)||Appends n copies of the ith element to a collection.|
|TRIM||Removes one element from the end of a collection.|
|TRIM(n)||Removes n elements from the end of a collection.|
|DELETE||Removes all elements from a collection, setting COUNT to 0.|
|DELETE(n)||Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.|
|DELETE(m,n)||Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.|