Table of Contents
Ispirer Website
Ispirer Capabilities - Oracle Migration
Free Trial
Oracle PL/SQL Collections
PL/SQL data types TABLE and VARRAY allow you to define collections, an ordered set of elements, all of the same type. Each element is addressed by a unique index.
You can define collection types in a procedure, function, or package. You can pass collection variables as parameters to PL/SQL subprograms.
PL/SQL offers 3 collection types:
- Associative Arrays
- Nested Tables
- Varrays
Associative Arrays
Associative array is a set of pairs: key and value, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string. The keys do not have to be consecutive.
Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry.
DECLARE TYPE continent_type IS TABLE OF NUMBER INDEX BY VARCHAR2(80); v_continent continent_type; v_count NUMBER; BEGIN v_continent('Europe') := 100; -- New entry created v_count := v_continent('Europe'); -- 100 assigned END;
Declaring Associative Arrays
To create an associative array, you need to define TABLE collection type, and then declare a variable of that type.
The key data type can be PLS_INTEGER (BINARY_INTEGER) or VARCHAR2.
DECLARE TYPE emp_type IS TABLE OF employees%ROWTYPE% INDEX BY PLS_INTEGER; v_emp emp_type;
Collection Methods for Associative Arrays
- COUNT returns the number of elements that a collection currently contains
- FIRST returns the smallest key value in a collection. If the collection is empty FIRST returns NULL.
- LAST returns the largest key value in a collection. If the collection is empty LAST returns NULL.
- NEXT(n) returns the key value that succeeds the key n. if n has no successor, NEXT returns NULL.
- PRIOR(n) returns the key value that precedes the key n. if n has no predecessor, PRIOR returns NULL.
v_continent('Europe') := 100; v_continent('America') := 200; v_continent('Asia') := 300; v_continent('Africa') := 400;
v_continent.COUNT returns 4 v_continent.FIRST returns Africa v_continent.LAST returns Europe v_continent.NEXT('Africa') returns America v_continent.PRIOR('Europe') returns Asia
- EXISTS(n) function returns TRUE if element with the n key value exists in a collection, otherwise, it returns FALSE. You can use EXISTS to avoid referencing a nonexistent element, which raises an exception.
IF t_array.EXISTS(iParamID) THEN t_array(iParamID).Status := 0; END IF;