Collections and Records

Oracle PL/SQL collections and records are data structures that allow for the storage and manipulation of multiple values or rows of data within a single variable or object.

Collections

Collections are a group of similar data elements, such as a group of strings or numbers. There are three types of collections: arrays, lists, and associative arrays (also known as index-by tables).

For example, an array of strings can be declared as:

TYPE string_array IS TABLE OF VARCHAR2(50);

And then can be initialized and used like:

string_array my_array;
my_array := string_array('Hello', 'World');

A PL/SQL collection is an ordered group of elements like lists, arrays, and other datatypes.
Each element of the collection has a unique index that determines its position.

PL/SQL collection types:

• Index-by tables(associative arrays)
• Nested tables
• Varrays(variable-size arrays)

Collection Methods

Oracle PL/SQL provides a number of collection methods that you can use to manipulate your data.

• EXISTS method is used to check if an element exists in the collection. It returns TRUE if the element exists, and FALSE if it does not.
• COUNT method is used to retrieve the number of elements in a collection. It does not take any arguments and returns the number of elements in the collection.
• LIMIT method is used to limit the number of elements in a collection. It takes a single argument, which is the maximum number of elements that the collection can hold.
• FIRST method is used to retrieve the first element of a collection. It does not take any arguments and returns the value of the first element.
• LAST method is used to retrieve the last element of a collection. It does not take any arguments and returns the value of the last element.
• PRIOR method is used to access the element that is immediately before the current element in a collection. It can be used in a loop to iterate through the elements of a collection in reverse order.
• NEXT method is used to access the next element in a collection. It can be used in a loop to iterate through the elements of a collection in the forward order.
• EXTEND method is used to add multiple elements to a collection. It takes a single argument, which is another collection containing the elements to be added.
• TRIM method is used to remove all the elements from a collection that are beyond a certain index. It can be used to resize a collection or to remove unwanted elements from the end of a collection.
• DELETE method is used to remove an element from a collection. It takes a single argument, which is the index of the element to be removed.

Collection Exceptions

You can also use collection exceptions to handle errors that may occur while working with collections.

• COLLECTION_IS_NULL
• NO_DATA_FOUND
• SUBSCRIPT_BEYOND_COUNT
• SUBSCRIPT_OUTSIDE_LIMIT
• VALUE_ERROR

Records

Records, on the other hand, are a group of dissimilar data elements, such as a row in a table. A record can be used to store data from a single row of a table, or it can be used to store the individual fields of a user-defined type.

For example, a record can be defined as:

TYPE person_rec IS RECORD (
   first_name   VARCHAR2(20),
   last_name    VARCHAR2(20),
   age          NUMBER(3)
);

And can be initialized and used like:

person_rec my_rec;
my_rec.first_name := 'John';
my_rec.last_name := 'Doe';
my_rec.age := 30;

A PL/SQL record is a group of elements stored in fields, each field having its own name and datatype.
TYPE type_name IS RECORD (field_declaration[,field_declaration]…);

PL/SQL can handle the following types of records:

• Table-based records
• Cursor-based records
• User-defined records

Both collections and records can be used in a variety of ways in PL/SQL, including as input and output parameters for procedures and functions, and as variables in PL/SQL blocks and loops.