PlSQL - Collections

Acollection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL provides three collection types:

• Index-by tables or Associative array
• Nested table
• Variable-size array or 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
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 schema level	Yes
Variable- size array (Varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level	Yes

We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables.

Both types of PL/SQL tables, i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table

An index-by table (also called an associative array) 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.

An index-by table is created using the following syntax. Here, we are creating an index-by table namedtable_name whose keys will be of subscript_type and associated values will be of element_type.


Example:

Following example shows how to create a table to store integer values along with names and later it prints the same list of names.


When the above code is executed at SQL prompt, it produces the following result:


Example:

Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:


When the above code is executed at SQL prompt, it produces the following result:


Nested Tables
A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:

• 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, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

A nested table is created using the following syntax:


This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause.

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

Example:

The following examples illustrate the use of nested table:


When the above code is executed at SQL prompt, it produces the following result:


Example:

Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:


When the above code is executed at SQL prompt, it produces the following result:


Collection Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose:

S.N.	Method Name & Purpose
1	EXISTS(n)Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2	COUNTReturns the number of elements that a collection currently contains.
3	LIMITChecks the Maximum Size of a Collection.
4	FIRSTReturns the first (smallest) index numbers in a collection that uses integer subscripts.
5	LASTReturns the last (largest) index numbers in a collection that uses integer subscripts.
6	PRIOR(n)Returns the index number that precedes index n in a collection.
7	NEXT(n)Returns the index number that succeeds index n.
8	EXTENDAppends one null element to a collection.
9	EXTEND(n)Appends n null elements to a collection.
10	EXTEND(n,i)Appends n copies of the ith element to a collection.
11	TRIMRemoves one element from the end of a collection.
12	TRIM(n)Removes n elements from the end of a collection.
13	DELETERemoves all elements from a collection, setting COUNT to 0.
14	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.
15	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.

Collection Exceptions

The following table provides the collection exceptions and when they are raised:

Collection Exception	Raised in Situations
COLLECTION_IS_NULL	You try to operate on an atomically null collection.
NO_DATA_FOUND	A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the allowed range.
VALUE_ERROR	A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

---