PL-SQL Collections and Records

PL-SQL Collections and Records- Learn about how collections and Records used in PL-SQL . Also Learn  how collections  and records are classified depends on requirement.

PL-SQL Collections and Records

COLLECTION is a type of array that stores the elements of same data type. We can access the element in the array by using its sub script.

LISTS and ARRAYS are the examples of collections.

RECORDS- A record variable can hold a table row, or some columns from a table row. It stores the elements of  different data types  which is called fields.

PL-SQL Collections

PL/SQL provides three collection types

1. Index-by tables or Associative array
2. Nested table
3. Variable-size array or Varray

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.

Index-by-table  takes dynamic array size.

Syntax

TYPE type_name IS TABLE OF DATA_TYPE [NOT NULL] INDEX BY subscript_type;

Type_name is declared as an index-by-table collection of the type ‘DATA_TYPE’.
The data type can be either simple or complex type.
The subscript or index variable is given as BINARY_INTGER or VARCHAR2  type with maximum size as required.

Note

Index by Table acts same as  array except they don’t have any upper limit. It extends the size as the data size is extending.

Collection used by index as  BINARY_INTEGER values, need not required to in consecutive order.

The collection is extended by assigning values to an element using an index value that does not currently exist.

We can give index value as BINARY_INTEGER  or VARCHAR2  for  INDEX-BY TABLE.

SUBSCRIPT	1	2	3	4	5	6	7	8
Value(sparse)	Jackson			Jackson	BAN		Jackson	RAMP

Important Points

• The subscript can be of integer or strings. At the time of creating the collection, the subscript type should be mentioned.
• These collections are not stored sequentially.
• They are always sparse in nature.
• The array size is not fixed.
• They cannot be stored in the database column. They shall be created and used in any program in that particular session.
• They give more flexibility in terms of maintaining subscript.
• The subscripts can be of negative subscript sequence also.
• They are more appropriate to use for relatively smaller collective values in which the collection can be initialized and used within the same subprograms.
• They need not be initialized before start using them.
• It cannot be created as a database object. It can only be created inside the subprogram, which can be used only in that subprogram.
• BULK COLLECT cannot be used in this collection type as the subscript should be given explicitly for each record in the collection.

Nested Table

A Nested table is like a one-dimensional array with an arbitrary number of elements. This are extension to INDEX-BY Table collections.

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.

Nested tables can be stored in a database column but index-by tables cannot.

DML operations are possible on nested tables when they are stored in the database.

During creation the collection must be dense, having consecutive subscripts for the elements.  Once created elements can be deleted using the DELETE method to make the collection sparse.

A nested table is created using the following syntax

Syntax

TYPE type_name IS TABLE OF element_type [NOT NULL];
table_name type_name;

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

SUBSCRIPT	1	2	3	4	5	6	7	8
Value (dense)	Smith	JACK	CARA	TOP	BAT	BAN	GAN	RAMP
Value(sparse)	Jackson			Jackson	BAN		Jackson	RAMP

NOTES

• The Nested table has no upper size limit.
• Since the upper size limit is not fixed, the collection, memory needs to be extended each time before we use it. We can extend the collection using ‘EXTEND’ keyword.
• Populated sequentially starting with the subscript ‘1’.
• This collection type can be of both dense and sparse, i.e. we can create the collection as a dense, and we can also delete the individual array element randomly, which make it as sparse.
• It gives more flexibility regarding deleting the array element.
• It is stored in the system generated database table and can be used in the select query to fetch the values.
• The subscript and sequence are not stable, i.e. the subscript and the count of the array element can vary.
• They need to be initialized before using them in programs. Any operation (except EXISTS operation) on the uninitialized collection will throw an error.
• It can be created as a database object, which is visible throughout the database or inside the subprogram, which can be used only in that subprogram.

Varray

A VARRAY is similar to a nested table except you must specify an upper bound in the declaration.

It is a collection method in which the size of the array is fixed. The array size cannot be exceeded than its fixed value.

The subscript of the Varray is of a numeric value.

Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.

SUBSCRIPT	1	2	3	4	5	6	7	8
Value (dense)	Smith	JACK	CARA	TOP	BAT	BAN	GAN	RAMP

NOTES

• Upper limit size is fixed
• Populated sequentially starting with the subscript ‘1’
• This collection type is always dense, i.e. we cannot delete any array elements. Varray can be deleted as a whole, or it can be trimmed from the end.
• Since it always is dense in nature, it has very less flexibility.
• It is more appropriate to use when the array size is known and to perform similar activities on all the array elements.
• The subscript and sequence always remain stable, i.e. the subscript and count of the collection is always same.
• They need to be initialized before using them in programs. Any operation (except EXISTS operation) on an uninitialized collection will throw an error.
• It can be created as a database object, which is visible throughout the database or inside the subprogram, which can be used only in that subprogram.

PL-SQL RECORDS

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the PL/SQL record has its own name and data type.

A PL-SQL Record is allows you to treat several variables as unit.
PL-SQL Records are similar to strucuture in C and C#
When a “Record Type” of fields are declared then they can be manipulated as a unit through out the application.

PL/SQL provides three ways to declare a record:

1. Table-based record
2. Cursor-based record
3. Programmer-defined records.

NOTE

• In the composite data type RECORD, we can specify the data type of the column.
• Each filed defined can have as many fields as necessary.
• Fields declared as “not null” must be initialized in the declaration part.
• A record can be initialized in its declaration part unlike PL-SQL tables, which doesn’t allow initialization in the declaration part.
• The DEFAULT keyword can also be used when defining fields.
• A RECORD can be component of another RECORD.

Table-based Record

The %ROWTYPE attribute enables a programmer to create table-based and cursor based records.

Syntax

varaiable_name  table_name%ROWTYPE

Cursor-Based Records

You can define a record based on a cursor. First, you must define a cursor. And then you use %ROWTYPE with the cursor variable to declare a record. The fields of the record correspond to the columns in the cursor SELECT statement.

varaiable_name  Cursor_name%ROWTYPE

Programmer-defined records -User defined Records

To declare programmer-defined record, first you have to define a record type by using TYPE statement with the fields of record explicitly. Then, you can declare a record based on record type that you’ve defined.

Syntax

TYPE type_name IS RECORD
(field1 data_type1 [NOT NULL] := [DEFAULT VALUE],
field2 data_type2 [NOT NULL] := [DEFAULT VALUE],
...
fieldn data_type3 [NOT NULL] := [DEFAULT VALUE]
);
record-name  type_name;