Ispirer Website
Ispirer Capabilities - Oracle Migration
Free Trial
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 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;
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;
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
IF t_array.EXISTS(iParamID) THEN t_array(iParamID).Status := 0; END IF;