Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to Oracle Request SQLWays

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;


Ispirer Home Page Ispirer SQLWays Product Page - Migration to Oracle Request SQLWays

sqlways/oracle/plsql-collections.txt · Last modified: March 20, 2013, 04:00:00 AM (external edit)
 
© 1999-2018, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement