PL/SQL Collections

Introduction to PL/SQL Collections

First of all, like other modern programming languages, PL/SQL also provides the facility to programmers to use PL/SQL collections. So, in general terms, the collection is a data structure having an ordered group of elements of the same data type and these elements are accessed through indexes according to the specific requirements. Commonly used collections in most of the programming languages are Arrays, Set, List, and Queue, etc.

The PL/SQL collection provides a lot of advantages to both the programmer and the user while storing and accessing the data of similar data types in an application. When there is a need to process the data all at once or handling a large amount of data, PL/SQL collections are very useful.

It also reduces the size and processing speed while storing the data and accessing the elements. The elements in collections can be accessed easily through loops and indexes. Unlike arrays in other programming languages, PL/SQL supports only one-dimensional collections and the data in the collections are identified by the subscripts (also called index in other languages). Each element is accessed and processed through this unique subscript.

Types of PL/SQL Collections

The PL/SQL collections are of 3 types which are given below with syntax:

1. Nested tables

A nested table is a PL/SQL collection in which the size is not fixed. So, it is like a one-dimensional array except for the fact that the size is fixed in arrays and it can be dense and sparse both. The programmer needs to extend the memory every time before using it using EXTEND Subscript in the Nested table starts with an integer ‘1’.

Nested tables come under Persistent PL/SQL collections which means that they can be reused further as stored in the database. The programmer can also delete an array element and make the table sparse, which means that Nested tables can be both dense and sparse. The Nested tables can be declared either inside the PL/SQL block or at the schema level.

As there is no limit on the upper size in Nested tables, they come under the category of Unbounded PL/SQL collections.

Syntax:

TYPE typ_name IS table of element_data_type;
Table_name typ_name;

2. Variable Sized Arrays or VARRAYs 

VARRAY is the PL/SQL collection in which the size of the collection is fixed as defined in its definition so VARRAYs are called bounded Elements in arrays are populated sequentially starting from the subscript 1. Unlike Nested tables, varrays provides less flexibility as they are dense only. So the programmer cannot delete any element from between, either whole varray is deleted or it can be trimmed from the end. Varrays has to be accessed and stored sequentially. It can be defined either inside the PL/SQL block or the schema level. Like nested tables, varrays comes under the category of persistent collections so they can be reused and stored at the database.

Syntax:

TYPE typ_name IS VARRAY <size> OF element_data_type;

3. Associative Arrays

As the name suggests, associative arrays are used to hold the values in key-value pairs. The key used can be either a string or integer type. Associative arrays are also called an Index-by table. This collection can be either dense or sparse. In associative arrays, the array size is not fixed in the starting and the programmer needs not to initialize them before using it. An associative array comes under the category of non-persistent collections which means that they are not stored in the database and can not be reused so they are defined inside the PL/SQL block and are used in that particular session only. These arrays are under the category of unbounded collections.

Syntax:

TYPE typ_name IS TABLE OF element_data_type;

Collection Methods

PL/SQL provides some predefined methods to make it easy to work with collections. Some of the methods are given below:

S.No.	Method Name	Description
1.	COUNT	It returns the number of elements present in the collection
2.	FIRST	It returns the smallest (first) index number in the collection for integer subscripts
3.	LAST	It returns the largest (last) index number in the collection for integer subscripts.
4.	EXISTS(n)	It is used to check whether a particular element is present in the collection or not. It returns TRUE if the nth elements are present in the collection, FALSE if not.
5.	PRIOR(n)	It returns the index number that is the predecessor of the index(n) given by the user in the collection.
6.	NEXT(n)	It returns the index number that is the successor of the index(n) given by the user in the collection.
7.	TRIM	It is used to remove elements from the collection. TRIM removes the last element from the collection and TRIM(n) removes the last n element from the end of the collection.
8.	DELETE	It is used to remove all the elements from the given collection. It sets the collection count to 0 after removing all elements
9.	DELETE(m,n)	It is used in the case of associative arrays and indexed tables to remove all the elements in the range from m to n. It returns null if m is greater than n.
10.	LIMIT	It is used to check the maximum size of the collection.

Collection Exceptions

Given below are some of the exceptions most likely to arise while working with collections.

Exception Name	A scenario in which Exception arise
NO_DATA_FOUND	An exception arises when a subscript referring an element that is deleted and does not exist anymore.
VALUE_ERROR	The exception occurs when the value of columns trying to access is non-convertible to the key type or when a subscript is null
COLLECTION_IS_NULL	Exception arise on working on an automatically null collection
SUBSCRIPT_BEYOND_CO UNT	An exception arises when a subscript exceeds the maximum count of a number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIM IT	The exception arises when trying to reference using the index number which is outside the legal range.
TOO_MANY_ROWS	An exception arises when a SELECT into statement returns more than 1 row.

Advantages of Collections in PL/SQL

 Some of the advantages of PL/SQL collections are given below:

• Besides, one of the biggest advantages of collections is that it improves the system performance by caching the static data which needs to be accessed regularly.
• Most importantly, collections are helpful while working with the large dataset having the same data type on which the user needs to perform multiple DML operations.
• By using the one collection variable we can reduce the multiple numbers of variables used for storing different values and hence saving the memory.
• Performing different operations like storing and processing data becomes easy through PL/SQL collection methods already provided.

Conclusion

Through the above description, you may get an idea of what the PL/SQL collection is and the methods that can be used in the PL/SQL collections. Before using any PL/SQL collection type in the program, the developer needs to think deeply about the scenario first before choosing any type. Though working in PL/SQL collections is not difficult but certain exceptions may arise at different points which the programmer must be aware of and knows how to deal with them.

Recommended Articles

This is a guide to PL/SQL Collections. Here we discuss the syntax, types, methods, and exceptions of PL/SQL collections along with the advantages. You may also look at the following articles to learn more –

1. Cursors in PL/SQL
2. CASE statement in PL/SQL
3. Oracle PL/SQL Interview Questions
4. PL/SQL Commands