Many programming techniques use collection types. To support these techniques in database applications, PL/SQL provides three data types of collections:
Collection Type | Number of Elements | Indexed by (Subscript Type) | Dense or Sparse | Where Created | Can Be Object Type Attribute |
---|---|---|---|---|---|
Associative array (or index-by table) | Unbounded | String or integer | Either | Only in PL/SQL block | No |
Nested table | Unbounded | Integer | Starts dense, can become sparse | Either in PL/SQL block or at schema level | Yes |
Variable-size array (varray) | Bounded | Integer | Always dense | Either in PL/SQL block or at schema level | Yes |
A collection is an ordered group of elements, all of the same datatype whereas
Records contain uniquely named fields, which can have different datatypes.
. Each element has a unique subscript that determines its position in the collection.
Language structure Mapping:
In other languages | In PLSQL |
---|---|
Arrays | varrays |
Sets and bags | nested tables |
Hash tables and other unordered tables | associative arrays |
Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.
To create a collection, you first define a collection type, and then declare a variable of that type.
Every reference to an element includes a collection name and a subscript enclosed in parentheses.
collection_name (subscript)
where subscript is an expression that yields:
One collection can be assigned to another by:
Assignment statement
collection_name (subscript) := expression;
Several operator permits to merge two collections:
In SqlPlus:
set serveroutput on;
DECLARE
type t_tabOfInteger is table of Integer;
l_Integers1 t_tabOfInteger := t_tabOfInteger(1,2,3);
l_Integers2 t_tabOfInteger := t_tabOfInteger(3,4);
BEGIN
DBMS_OUTPUT.PUT_LINE('l_Integers1 count: ' || l_Integers1.COUNT );
l_Integers1 := l_Integers1 MULTISET UNION l_Integers2;
DBMS_OUTPUT.PUT_LINE('l_Integers1 count after multiset: ' || l_Integers1.COUNT );
END;
l_Integers1 count: 2
l_Integers1 count after multiset: 4
Set is a collection operator that suppress the duplicate of a collection (Same as a distinct for SQL)
Collection Exception | Raised when… |
---|---|
COLLECTION_IS_NULL | you try to operate on an atomically null collection. |
NO_DATA_FOUND | a subscript designates an element that was deleted, or a nonexistent element of an associative array. |
SUBSCRIPT_BEYOND_COUNT | a subscript exceeds the number of elements in a collection. |
SUBSCRIPT_OUTSIDE_LIMIT | a subscript is outside the allowed range. |
VALUE_ERROR | a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range. |