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 SyntaxTYPE 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