| 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;
/ |
