Oracle PL/SQL Collections types

PL/SQL Collections

These are composite variables in PL/SQL and have internal components that you can treat as individual variables. You can pass these composite variables to subprograms as a parameters.
To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type.

• In a collection, the internal components are always of the same data type, and are called elements. You access each element by its unique subscript. Lists and arrays are classic examples of collections.
• In a record, the internal components can be of different data types, and are called fields. You access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

PL/SQL Collection Types:
PL/SQL has three collection types, whose characteristics are summarized below.
1] Associative array (or index-by table)

• Number of Elements: Unbounded
• Subscript Type: String or integer
• Dense or Sparse: Either
• Where Created: Only in PL/SQL block

2] Nested Table

• Number of Elements: Unbounded
• Subscript Type: Integer
• Dense or Sparse: Starts dense, can become sparse
• Where Created: Either in PL/SQL block or at schema level

3] Variable size Array (Varray)

• Number of Elements: Bounded
• Subscript Type: Integer
• Dense or Sparse: Always Dense
• Where Created: Either in PL/SQL block or at schema level

Note:
Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there are limits, but they are very high.
Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).
A collection that is created in a PL/SQL block is available only in that block. A nested table type or varray type that is created at schema level is stored in the database, and you can manipulate it with SQL statements.
A collection has only one dimension, but you can model a multidimensional collection by creating a collection whose elements are also collections.
Associative Arrays (Index-By Tables):
An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value.

01

DECLARE

02	-- Associative array indexed by string:

03

04	TYPE ODI_RUNS IS TABLE OF NUMBER  -- Associative array type

05	INDEX BY VARCHAR2(64);

06

07	odi_batsman_runs  ODI_RUNS;        -- Associative array variable

08	i VARCHAR2(64);

09

10

BEGIN

11	-- Add new elements to associative array:

12

13	odi_batsman_runs('Virender Sehwag')  := 7380;

14	odi_batsman_runs('Ricky Ponting')    := 13082;

15	odi_batsman_runs('Sachin Tendulkar') := 17629;

16

17	-- Print associative array:

18

19	i := odi_batsman_runs.FIRST;

20

21	WHILE i IS NOT NULL LOOP

22	DBMS_Output.PUT_LINE

23	('Total ODI Runs on Jan 2010 by ' || i || ' is ' || TO_CHAR(odi_batsman_runs(i)));

24	i := odi_batsman_runs.NEXT(i);

25

END LOOP;

26

END;

Output:
Total ODI Runs on Jan 2010 by Ricky Ponting is 13082
Total ODI Runs on Jan 2010 by Sachin Tendulkar is 17629
Total ODI Runs on Jan 2010 by Virender Sehwag is 7380

• Like a database table, an associative array holds a data set of arbitrary size, and you can access its elements without knowing their positions in the array.
• An associative array does not need the disk space or network operations of a database table, but an associative array cannot be manipulated by SQL statements (such as INSERT and DELETE).
• An associative array is intended for temporary data storage.
• To make an associative array persistent for the life of a database session, declare the associative array (the type and the variable of that type) in a package, and assign values to its elements in the package body.

Nested Tables: 
A nested table is like a one-dimensional array with an arbitrary number of elements.
Within the database, a nested table is a column type that holds a set of values. The database stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. These subscripts give you array-like access to individual rows.
A nested table differs from an array in these important ways:

• 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 (that is, it always has consecutive subcripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Variable-Size Arrays (Varrays):
A variable-size array (varray) is an item of the data type VARRAY. A varray has a maximum size, which you specify in its type definition. A varray can contain a varying number of elements, from zero (when empty) to the maximum size. A varray index has a fixed lower bound of 1 and an extensible upper bound. To access an element of a varray, you use standard subscripting syntax.

01

DECLARE

02	TYPE nested_type IS TABLE OF VARCHAR2(30);

03	TYPE varray_type IS VARRAY(5) OF INTEGER;

04	v1 nested_type;

05	v2 varray_type;

06

BEGIN

07	v1 := nested_type('Shipping','Sales','Finance','Payroll');

08	v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers

09	FOR i IN v1.FIRST .. v1.LAST

10

LOOP

11	DBMS_OUTPUT.PUT_LINE('Element #' || i || 'in the nested table = ' || v1(i));

12

END LOOP;

13

14	FOR j IN v2.FIRST .. v2.LAST

15

LOOP

16	DBMS_OUTPUT.PUT_LINE('Element #' || j || 'in the varray = ' || v2(j));

17

END LOOP;

18

END;

Nested tables Vs. Varrays:

• Nested tables are unbounded and are initially dense but can become sparse through deletions. Varrays are always bounded and never sparse.
• When stored in the database, the order and subscripts of Nested tables are not preserved while varrays keep their ordering and subscripts.
• Nested table data is stored in a separate store table, a system-generated database table while a  varray is stored as a single object in the database.