Nested Tables
* Contains
Associated Arrays
* Aka Index-by tables.
* Contain sets of key-value pairs similar to hash tables.
* Keys are unique and can be numbers or strings.
* The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER. It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760). The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.
An initialization clause is not required (or allowed).
When you reference an element of an associative array that uses a VARCHAR2-based key, you can use other types, such as DATE or TIMESTAMP, as long as they can be converted to VARCHAR2 with the TO_CHAR function.
Definition
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)]; INDEX BY key_type;
Examples
SET serveroutput ON; DECLARE -- Declare an associative number array type indexed by string TYPE price_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64); -- Declare an associative array of the declared type fruit_price price_type; -- Some other simple variables lemon_price NUMBER; bannana_price NUMBER; fruit VARCHAR2(64); BEGIN -- Assign elements fruit_price('lemon') := 1.10; fruit_price('bannana') := 2.20; fruit_price('orange') := 3.30; bannana_price := fruit_price('bannana'); lemon_price := fruit_price('lemon'); dbms_output.put_line('Bannana price: ' || bannana_price); dbms_output.put_line('Lemon price: ' || lemon_price); -- FIRST and LAST function dbms_output.put_line('First fruit: ' || fruit_price.FIRST); dbms_output.put_line('Last fruit: ' || fruit_price.LAST); -- Looping fruit := fruit_price.FIRST; WHILE fruit IS NOT NULL LOOP dbms_output.put_line(fruit || ' costs ' || fruit_price(fruit) || ' dollars.'); fruit := fruit_price.NEXT(fruit); END LOOP; END; anonymous block completed Bannana price: 2.2 Lemon price: 1.1 FIRST fruit: bannana LAST fruit: orange bannana costs 2.2 dollars. lemon costs 1.1 dollars. orange costs 3.3 dollars.