Oracle Associative Arrays and Index by Binary Integer Arrays

Oracle Arrays Version 11.2.0.3

General Information
Array Syntax	TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER \| PLS_INTEGER \| VARCHAR2(size_limit)]; INDEX BY key_type;

Associative Array
Note: An associative array in PL/SQL is similar to its counterpart in Perl: An array indexed by a string rather than by an integer.
Create, load and accessing an associative array	conn uwclass/uwclass set serveroutput on DECLARE TYPE assoc_array IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30); state_array assoc_array; BEGIN state_array('Alaska') := 'Juneau'; state_array('California') := 'Sacramento'; state_array('Oregon') := 'Salem'; state_array('Washington') := 'Olympia'; dbms_output.put_line(state_array('Alaska')); dbms_output.put_line(state_array('California')); dbms_output.put_line(state_array('Oregon')); dbms_output.put_line(state_array('Alaska')); END; /

Binary Integer Array
Create, load and accessing an array indexed by binary integer	conn uwclass/uwclass set serveroutput on DECLARE TYPE bin_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; state_array bin_array; BEGIN state_array(1) := 'Alaska'; state_array(2) := 'California'; state_array(3) := 'Oregon'; state_array(4) := 'Washington'; FOR i IN 1 .. state_array.COUNT LOOP dbms_output.put_line(state_array(i)); END LOOP; END; / CREATE TABLE t ( resultcol VARCHAR2(20)); DECLARE TYPE bin_array IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; state_array bin_array; BEGIN state_array(1) := 'Alaska'; state_array(2) := 'California'; state_array(3) := 'Oregon'; state_array(4) := 'Washington'; FORALL i IN 1 .. state_array.COUNT INSERT INTO t VALUES (state_array(i)); COMMIT; END; / SELECT * FROM t;

Binary Integer Array
Comparison of associative arrays and arrays indexed by binary integer	conn uwclass/uwclass set serveroutput on DECLARE TYPE ntab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; p ntab; TYPE vtab IS TABLE OF NUMBER INDEX BY VARCHAR2(100); p1 vtab; q NUMBER; BEGIN q := dbms_utility.get_time; -- begin standard array load FOR i IN 1 .. 100000 LOOP p(i) := i; END LOOP; dbms_output.put_line(dbms_utility.get_time-q); q := dbms_utility.get_time; -- begin associative array load FOR i IN 1 .. 100000 LOOP p1('STUFF'\|\| TO_CHAR(i)) := i; END LOOP; dbms_output.put_line(dbms_utility.get_time-q); END; /