CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Arrays
Version 11.1
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;
/
 
Related Topics
Bulk Collection and FORALL
Procedures
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [59 users online]    © 2010 psoug.org