Index By Tables or Associate Arrays
An index-by table has no limit on its size. Elements are inserted into index-by table whose
index may start non-sequentially including negative integers.
Associate Arrays can only be accessed in PL/SQL. (not in SQL)
They are modeled as similar to database tables but they are not permanent tables i.e. they
can be created and manipulated only in PL/SQL block.
Index can be of any data type.
We can store unlimited values.
Suitable for storing and displaying the values of one column of a table given by a cursor.
These Associate arrays do not require initialization and have no constructor syntax.
No need to allocate space before assigning values (i.e. no extend method is required) as in
Varrays and Nested tables.
Associate arrays cannot be used in tables. They may be used only as programming
structures i.e. in pl/sql.
Example1:-
[sql]DECLARE
/* declaring the Index-by-table or associate array based on the “emp” table type*/
TYPE enametab IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
/*assigning the tabletype variable into anther variable which same as table type variable*/
enames enametab;
/*declaring the cursor*/
CURSOR emp_cur
IS
SELECT ename FROM emp;
v_num NUMBER;
BEGIN
v_num:=1;
FOR v_ec IN emp_cur
LOOP
/*assign the index based on the ename’s of the “emp” table*/
enames (v_num):= v_ec.ename;
v_num:=v_num+1;
END LOOP;
/*print the elements in “enames” type by using table type methods i.e. FIRST and LAST*/
FOR i IN enames.FIRST.. enames.LAST
LOOP
DBMS_OUTPUT.put_line (enames (i));
END LOOP;
END;[/sql]
Output:-
anonymous block completed
KING /*DBMS output */
BLAKE
CLARK
ALLEN
WARD
MARTIN
TURNER
JAMES
MILLER
Example2:-
[sql]DECLARE
/*define variables to traverse a string indexed associative array.*/
current VARCHAR2 (9 CHAR);
element INTEGER;
/* define required collection data types. */
/* first define varray of size 12. */
TYPE months_table IS VARRAY (12) OF VARCHAR2 (10);
/*define a pl/sql table type */
TYPE calendar_table IS TABLE OF VARCHAR2 (9) INDEX BY VARCHAR2 (9);
/*declare a varray.*/
month_list months_table :=
months_table(‘January’,’February’,’March’,’April’,’May’,’June’
,’July’,’August’,’September’,’October’,’November’,’December’);
/*declare empty associative array.*/
Calendar_num calendar_table;
BEGIN
/*Check if calendar has no elements.*/
IF Calendar_num.COUNT = 0
THEN
/* Print assignment output title.*/
DBMS_OUTPUT.PUT_LINE (‘Assignment loop :’);
DBMS_OUTPUT.PUT_LINE (‘—————-‘);
FOR i IN month_list.FIRST.. month_list.LAST
LOOP
Calendar_num(month_list(i)):= TO_CHAR (i);
DBMS_OUTPUT.PUT_LINE (‘Index [‘|| month_list(i)||’] is [‘||i||’]’);
END LOOP;
/* Print assigned output title.*/
DBMS_OUTPUT.PUT (CHR (10));
DBMS_OUTPUT.PUT_LINE (‘Post-assignment loop:’);
DBMS_OUTPUT.PUT_LINE (‘———————‘);
FOR i IN 1.. Calendar_num.COUNT
LOOP
IF i = 1 THEN
/*assign the first character index to a variable.*/
current := Calendar_num.FIRST;
/* Use the derived index to find the next index.*/
element := Calendar_num(current);
ELSE
/*Check if next index value exists.*/
IF Calendar_num.NEXT (current) IS NOT NULL
THEN
/*Assign the character index to a variable.*/
current := Calendar_num.NEXT (current);
/*Use the derived index to find the next index.*/
element := Calendar_num(current);
ELSE
/* Exit loop since last index value is read.*/
EXIT;
END IF;
END IF;
/*Print an indexed element from the array.*/
DBMS_OUTPUT.PUT_LINE (‘Index [‘||current||’] is [‘||element||’]’);
END LOOP;
END IF;
END;[/sql]
Output:-
anonymous block completed
Assignment loop : /* DBMS output and it may be different in your systems */
----------------
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
Index [July] is [7]
Index [August] is [8]
Index [September] is [9]
Index [October] is [10]
Index [November] is [11]
Index [December] is [12]
Post-assignment loop:
---------------------
Index [April] is [4]
Index [August] is [8]
Index [December] is [12]
Index [February] is [2]
Index [January] is [1]
Index [July] is [7]
Index [June] is [6]
Index [March] is [3]
Index [May] is [5]
Index [November] is [11]
Index [October] is [10]
Index [September] is [9]
