PL/SQL Collections and Records

Collections and Records in PL/SQL

Composite data types, like collections and records, enable individual access to their internal components, which can be scalar or composite. For instance, one can access array elements, columns, or elements within a table separately within these data types.

PL/SQL Collections

A collection is a group of items that share the same type and are arranged in a particular order. It is a broad term that encompasses lists, arrays, and other data structures commonly used in traditional programming. Each element in a collection is assigned a specific subscript.

In PL/SQL, there are different types of collections available, including index-by tables. These collections, also known as associative arrays, allow elements to be accessed by using arbitrary numbers or strings as subscripts. They can be compared to hash tables in other programming languages.

Here is an example for a declaration of an associative array. 

DECLARE  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
BEGIN
  country_population('Greenland') := 100000; -- Creates new entry
  country_population('Iceland') := 750000;   -- Creates new entry
-- Looks up value associated with a string
  howmany := country_population('Greenland');
  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value 
-- Returns 'Antarctica' as that comes first alphabetically.
  which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically.  which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
  howmany := continent_population(continent_population.LAST);
END;

Nested tables: Nested tables are data structures that can store an arbitrary number of elements. They use consecutive numerals as subscripts for accessing elements. Similar to arrays, nested tables can be defined with equivalent SQL types, allowing them to be stored in database tables and manipulated using SQL queries.

Below is an example of nested tables: As seen in the below image, nested tables have no stated number of items, whereas arrays have a predetermined quantity. A nested table’s size can grow and shrink dynamically, but there is a limit.

Varray: varrays, also known as variable-size arrays, have a fixed number of elements that they can hold. Like nested tables, varrays use consecutive numerals as subscripts. They can also be defined with equivalent SQL types and stored in database tables. However, their size is predetermined and cannot be changed dynamically.

In the example provided, a varray called “Grades” is illustrated. It has a maximum size specified in its type specification, and its index has a fixed lower bound of 1 and an extendible higher bound. For instance, the top bound for the varray Grades is initially 7, but it can be increased up to a maximum of 10. A varray can have any number of items, starting from zero when empty and up to the maximum size specified in its type specification.

PL/SQL Records

A record is a collection of data objects that are kept in fields, each having its own name and data type. A record can be thought of as a variable that can store a table row or a set of columns from a table row. Table columns relate to the fields.

Records are made up of a collection of fields that are similar to the columns in a row. You can declare a PL/SQL record that resembles a row in a database table without listing all the columns using the % ROWTYPE attribute. Even when columns are added to the table, your code continues to work. You can build a view or declare a cursor to choose the right columns and perform any necessary joins, then apply percent ROWTYPE to the view or cursor to represent a subset of columns in a table or columns from separate tables.

Difference between collections and records in PL/SQL

Here are the key differences between collections and records in PL/SQL.

Defining Collection Types and Declaring Collection Variables

For creating collections, you specify a collection type and then declare variables of that type. The same scope and instantiation rules apply to collections as they do to other types and variables. When you enter a block or subprogram, a collection is created, and it is destroyed when you quit. Collections in a package are created when the package is first referenced, and they are destroyed when the database session ends.

A TYPE declaration can be used to define TABLE and VARRAY types in the declarative component of any PL/SQL block, subprogram, or package.

The table or varray element type can be any PL/SQL data type except REF CURSOR for nested tables and varrays specified in PL/SQL.

A positive integer must be specified as the maximum size of a VARRAY type when it is defined. In the following example, we have defined a type that can store up to 120 dates.

DECLARE
TYPE Calendar IS VARRAY(120) OF DATE;

Declaring PL/SQL Collection Variables

You declare variables of a collection type after defining them. As with predefined types like NUMBER, you use the new type name in the declaration.

In the following example, we have implemented Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(30);
TYPE varray_type IS VARRAY(5) OF INTEGER;
TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
v1 nested_type;
v2 varray_type;
v3 assoc_array_num_type;
v4 assoc_array_str_type;
v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
v1 := nested_type('Salary','Sales','Budget','Payroll');
v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
v3(99) := 120; -- Just start assigning to elements
v3(7) := 140; -- Subscripts can be any integer values
v4(42) := 'Sam'; -- Just start assigning to elements
v4(54) := 'James'; -- Subscripts can be any integer values
v5('Canada') := 'Africs'; -- Just start assigning to elements
v5('Greece') := 'calfornia'; -- Subscripts can be string values
END;
/

Initializing and Referencing Collections

A nested table or varray will be atomically null until it is initialized. Not the elements, but the collection is null. A system-defined function with the same name as the collection type is used to initialize a nested table or varray. The elements given to this function are used to create collections.

For each varray and nested table variable, you must explicitly call constructors. They are not used in associative arrays, the third type of collection. Wherever function calls are permitted, only those calls are allowed.

The following is an example of a constructor for a Nested Table

DECLARE
TYPE dnames_tab IS TABLE OF VARCHAR2(30);
dept_names dnames_tab;
BEGIN
dept_names := dnames_tab('Salary','Sales','Budget','Payroll');
END;
/

Because a nested table does not have a declared size, you can put as many elements in the constructor as necessary.

Referencing Collection Elements

A collection name and a subscript are included in every reference to an element, and which element is processed is determined by the subscript. To refer to an element, use the syntax below to indicate its subscript.

collection_name(subscript)

where subscript is an expression that, in most applications, returns an integer, or a VARCHAR2 in the case of associative arrays with strings as keys.

The allowed subscript ranges are:

• For nested tables, 1 .. 2147483647 (the upper limit of PLS_INTEGER).
• For varrays, 1 .. size_limit, where you specify the limit in the declaration (not to exceed 2147483647).
• For associative arrays with a numeric key, -2147483648 to 2147483647.
• For associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set.

Below mentioned is an example for referencing a Nested Table Element

DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15);
names Roster := Roster('D Hamilton', 'J David', 'D Patrick', 'R Stephen');
PROCEDURE verify_name(the_name VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(the_name);
END;
BEGIN
FOR i IN names.FIRST .. names.LAST
LOOP
IF names(i) = 'J David' THEN
DBMS_OUTPUT.PUT_LINE(names(i)); -- reference to nested table element
END IF;
END LOOP;
verify_name(names(3)); -- procedure call with reference to element
END;
/

Assigning Collections

An INSERT, UPDATE, FETCH, or SELECT statement, an assignment statement, or a subprogram call can be used to assign one collection to another. You can use the syntax mentioned below to assign the value of an expression to a specific element in a collection:

collection_name(subscript) := expression;

where expression returns a value of the type supplied in the collection type description for each element.

As part of an assignment statement, you can use operators like SET, MULTISET UNION, MULTISET INTERSECT, and MULTISET EXCEPT to change nested tables.

Comparing Collections

You can tell if a collection is empty. There are no comparisons allowed, such as more than, less than, and so on. Implicit comparisons are similarly subject to this constraint.

Collections, for example, cannot be selected in a DISTINCT, GROUP BY and ORDER BY list.

Let’s take an example for checking if a Collection is Null as follows:

DECLARE
TYPE emp_name_rec is RECORD (
firstname employees.first_name%TYPE,
lastname employees.last_name%TYPE,
hiredate employees.hire_date%TYPE
);
TYPE staff IS TABLE OF emp_name_rec;
members staff;
BEGIN
-- Condition yields TRUE because we have not used a constructor.
IF members IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('Not NULL');
END IF;
END;

Multilevel Collections

You can also create multilevel collections such as you can add scalar or object types by adding to elements but both scalar or object types and elements should be collections. You can also make a nested table of varrays, a varray of varrays, and a varray of nested tables.

When creating a nested table of nested tables as a column in SQL, look at the syntax of the CREATE TABLE command to see how to specify the storage table.

Visit our to get answers to all your queries!

Collection Methods

Collection methods make it easy to use collections and maintain your applications. COUNT, DELETE, EXISTS, EXTEND, FIRST, LAST, LIMIT, NEXT, PRIOR, and TRIM are some of the techniques available.

A collection method is a dot notation-based built-in function or procedure that acts on collections. In terms of collecting techniques, the following apply:

• SQL statements are unable to call collection methods.
• Associative arrays cannot be utilized with EXTEND and TRIM.
• Functions include EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT; operations include EXTEND, TRIM, and DELETE.
• EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE all take collection subscripts as arguments, which are generally integers but can also be string in the case of associative arrays.
• To atomically null collections, only EXISTS may be used. PL/SQL raises COLLECTION IS NULL if you apply another method to such collections.

1. EXISTS Method

If the nth element in a collection exists, EXISTS(n) returns TRUE. EXISTS(n) returns FALSE otherwise.

2. COUNT Method

COUNT returns the number of elements currently present in a collection.

3. LIMIT Method

LIMIT returns NULL for nested tables and associative arrays with no defined size. LIMIT returns the maximum amount of items that a varray can hold for varrays.

4. FIRST and LAST Methods

In a collection with integer subscripts, FIRST and LAST yield the first and last (smallest and biggest) index numbers.

5. PRIOR and NEXT Methods

PRIOR(n) returns the index number in a collection that comes before index n. The index number that comes after index n is returned by NEXT(n). PRIOR(n) returns NULL if n has no predecessor. NEXT(n) returns NULL if n has no successor.

6. EXTEND Method

• Use EXTEND to expand the size of a nested table or varray.
• There are three types of this procedure:
  • EXTEND adds a single null element to the end of a collection.
  • EXTEND(n) adds a collection by adding n null items.
  • EXTEND(n, i) adds a collection by appending n copies of the ith element.

7. TRIM Method

There are two forms of this procedure:

• TRIM removes one element from a collection’s end.
• TRIM(n) removes n elements from a collection’s end.

Use DELETE without arguments if you wish to delete all items.

8. DELETE Method

This technique can take several forms:

• With no arguments, DELETE deletes all entries from a collection and sets COUNT to 0.
• DELETE(n) deletes the nth entry from a nested table or an associative array with a numeric key. The element corresponding to the key value is removed if the associative array has a string key. DELETE(n) has no effect if n is null.
• DELETE(m,n) deletes all elements from an associative array or nested table in the range m…n. DELETE(m,n) does nothing if m is more than n or if m or n is null.

Avoiding Collection Exceptions

The table below explains under which condition a specific collection exception will be raised.

Defining and Declaring Records

You need to define a RECORD type and then declare records of the same type to generate records. You may also utilize the percent ROWTYPE property to construct a matching record by creating or finding a table, view, or PL/SQL cursor with the data you desire.

RECORD types can be defined in the declarative section of any PL/SQL block, subprogram, or package. You can put a NOT NULL constraint on fields or give them default values when creating your own RECORD type.

Here is an example of declaring and Initializing a Simple Record Type

DECLARE
TYPE DeptRecTyp IS RECORD (
deptid NUMBER(4) NOT NULL := 99,
dname departments.department_name%TYPE,
loc departments.location_id%TYPE,
region regions%ROWTYPE );
dept_rec DeptRecTyp;
BEGIN
dept_rec.dname := 'buying';
END;
/

Assigning Values to Records

Assign an uninitialized record of the same type to a record to set all of its fields to default values.

Let’s take an example for assigning default values to a record:

DECLARE
TYPE RecordTyp IS RECORD (field1 NUMBER,
field2 VARCHAR2(32) DEFAULT 'something');
rec1 RecordTyp;
rec2 RecordTyp;
BEGIN
-- At first, rec1 has the values we assign.
rec1.field1 := 100; rec1.field2 := 'something else';
-- Assigning an empty record to rec1 resets fields to their default values.
-- Field1 is NULL and field2 is 'something' due to the DEFAULT clause
rec1 := rec2;
DBMS_OUTPUT.PUT_LINE('Field1 = ' || NVL(TO_CHAR(rec1.field1),'<NULL>') || ',
field2 = ' || rec1.field2);
END;
/

You may use a dot notation assignment statement to assign a value to a field in a record as follows:

emp_info.last_name := 'Fields';

Comparing Records 

Records cannot be tested for nullity or compared for equality or inequality. If you want to make such comparisons, write your own function that accepts two records as parameters and does the appropriate checks or comparisons on the corresponding fields.

Inserting PL/SQL Records into the Database

Using a single variable of type RECORD or percent ROWTYPE in the VALUES clause instead of a list of columns, you may insert records into database rows using a PL/SQL-only extension of the INSERT command. This improves the readability and maintainability of your code.

You can insert data from an entire collection of records if you use the FORALL statement with the INSERT command. The number of fields in the record must match the number of columns given in the INTO clause, and the data types of the corresponding fields and columns must match. You might find it most useful to specify the variable as table_name%ROWTYPE to ensure the record is consistent with the table.

Let me help you understand the concept by taking the example of “Inserting a PL/SQL record using %ROWTYPE”.

DECLARE
dept_info employees%ROWTYPE;
BEGIN
-- employee_id, employee_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE
dept_info.employee_id := 342;
dept_info.employee_name := 'james';
dept_info.employee_id := 4700;
-- Using the %ROWTYPE means we can leave out the column list
-- (employee_id, employee_name, and location_id) from the INSERT statement
INSERT INTO employees VALUES dept_info;
END;
/

Updating the Database with PL/SQL Record Values

Instead of a list of columns, a PL/SQL-only extension of the Alter command allows you to update database rows using a single variable of type RECORD or percent ROWTYPE on the right side of the SET clause.

Here is an example of updating a Row Using a Record

DECLARE
dept_info departments%ROWTYPE;
BEGIN
-- department_id, department_name, and location_id are the table columns
-- The record picks up these names from the %ROWTYPE.
dept_info.department_id := 300;
dept_info.department_name := 'Personnel';
dept_info.location_id := 1700;
-- The fields of a %ROWTYPE can completely replace the table columns
-- The row will have values for the filled-in columns, and null
-- for any other columns
UPDATE departments SET ROW = dept_info WHERE department_id = 300;
END;
/

Querying Data into Collections of Records

For knowing about querying data into collections of records, you need to know a term named Bulk collect.

Bulk Collect is a data retrieval method in which the PL/SQL engine instructs the SQL engine to gather several rows at once and store them in a collection. The SQL engine returns to the PL/SQL engine after retrieving all of the entries and loading them into the collection. In memory, the data may now be processed as needed.

You may retrieve a set of rows from a collection of records by using the BULK COLLECT clause with a SELECT INTO or FETCH query.