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 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:
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.
You can also use collection exceptions to handle errors that may occur while working with collections.
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:
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.