Definition
Many programming techniques use collection types. To support these techniques in database applications, PL/SQL provides three data types of collections:
- Associative arrays (formerly called “PL/SQL tables” or “index-by tables”) - (Table of with the index by clause)
- Nested tables (Table of without the index by clause). See PL/SQL - (Nested) tables (Table Of without indexing)
- Variable-size arrays (varrays). VARRAY
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.
- Collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.
Language structure Mapping:
In other languages | In PLSQL |
---|---|
Arrays | varrays |
Sets and bags | nested tables |
Hash tables and other unordered tables | associative arrays |
Articles Related
Syntax
Memory
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.
Management
- Checking If a Collection Element Exists (EXISTS Method)
- Counting the Elements in a Collection (COUNT Method)
- Checking the Maximum Size of a Collection (LIMIT Method)
- Finding the First or Last Collection Element (FIRST and LAST Methods)
- Looping Through Collection Elements (PRIOR and NEXT Methods)
- Increasing the Size of a Collection (EXTEND Method)
- Decreasing the Size of a Collection (TRIM Method)
- Deleting Collection Elements (DELETE Method)
- Applying Methods to Collection Parameters
Create
To create a collection, you first define a collection type, and then declare a variable of that type.
Get
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:
- an integer in most cases,
- or a VARCHAR2 for associative arrays declared with strings as keys.
Set
One collection can be assigned to another by:
- an INSERT, UPDATE, FETCH, or SELECT statement,
- an assignment statement,
- or a subprogram call.
Assignment statement
collection_name (subscript) := expression;
Merge (MULTISET )
Several operator permits to merge two collections:
- MULTISET UNION
- MULTISET INTERSECT
- MULTISET INTERSECT DISTINCT
- MULTISET EXCEPT
- MULTISET EXCEPT DISTINCT
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
Set is a collection operator that suppress the duplicate of a collection (Same as a distinct for SQL)
Remove
- Trim n.TRIM(2) remove the last 2 elements
Built-in Exception
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. |
Reference
- Using PL/SQL Collections and Records Oracle® Database PL/SQL Language Reference