PL/SQL - Collections (Datatype Table of, Varray of)

Card Puncher Data Processing

Definition

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.

  • 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

Syntax

Database PL/SQL Language Reference - Collections

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

Using Collection Methods :

  • 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





Discover More
Card Puncher Data Processing
PL/SQL - OPEN-FOR, FETCH, and CLOSE statements

The OPEN-FOR statement executes the query associated with a cursor variable. It's an important statement of the dynamic sql Management. It allocates database resources to process the query and identifies...
Card Puncher Data Processing
PL/SQL - (Data Type|Internal Component)

Variable can have the following data type either: scalar or composite. PL/SQL defines two type of composite data types: collection and record.
Card Puncher Data Processing
PL/SQL - (Nested) Record

Records are simply a row representation in PL/SQL. Records (as rows) contain uniquely named fields (column name for row), which can have different datatypes whereas collection contains elements of the...
Card Puncher Data Processing
PL/SQL - (Nested) tables (Table Of without indexing)

A “table of” data type without the index-by clause is a list of elements (collection). For a table of indexed by, see Syntax...
Card Puncher Data Processing
PL/SQL - (Procedure Language|PL) SQL

PlSql is the development language of the oracle database. SQL was designed from the start to operate on SETS (ie parallel task) whereas PL/SQL brought a lot in terms of exception handling. PL/SQL...
Card Puncher Data Processing
PL/SQL - Bulk Collect - Fetch collection of (records|Collection)

You can use the BULK COLLECT clause with a SELECT INTO or FETCH statement to retrieve a set of rows into a collection (ie table of varray): of records. of a collections Fetch into a collection...
Card Puncher Data Processing
PL/SQL - FOR LOOP Statement (On Cursor, Sequence, )

The for statement has two forms. It can be use with: a sequence to loop though a sequence of number or through a collection a cursor You break the for loop with an statement. Sequence Syntax...
Card Puncher Data Processing
PL/SQL - Table Of Clause

A “table of” data type is a list of elements (collection) implementing: by default a list structure without the index-by clause. See bu can be used to implement a map structure with the index-by...
Card Puncher Data Processing
PL/SQL - VARRAY

VARRAY (variable-size arrays) is a collection, a variable-size arrays (varrays for short) Type PLSQL where: collection methods



Share this page:
Follow us:
Task Runner