Oracle Arrays

 

PL/SQL tables are PL/SQL’s way of providing arrays. Arrays are like tem-porary

tables in memory and thus are processed very quickly. It is impor-tant

for you to realize that they are not database tables, and DML

statements cannot be issued against them. This type of table is indexed

by a binary integer counter (it cannot be indexed by another type of

number) whose value can be referenced using the number of the index.

Remember that PL/SQL tables exist in memory only, and therefore don’t

exist in any persistent way, disappearing after the session ends.

General
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	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	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;
Array Performance Demo
Comparison of associative arrays and arrays indexed by binary integer	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; /

 

转自：http://psoug.org/reference/arrays.html

         http://comsci.liu.edu/~vasilaky/db2/tables.htm