Index-by tables

PL/SQL Index-by tables are a type of data structure available in Oracle database. They allow you to store and access data using a key or index, similar to how you would use an array. However, unlike arrays, index-by tables can be dynamically resized, making them more flexible and efficient.

To create an index-by table, you first need to create a TYPE:

TYPE my_table IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);

Once you have created the TYPE, you can then create a variable of that type:

t my_table;

You can then insert data into the table using the key or index:

t('key1') := 'value1';
t('key2') := 'value2';

You can also update and delete data in the same way:

t('key1') := 'new_value1'; -- Update
delete t('key2'); -- Delete

To retrieve data from the table, you can use the key or index:

SELECT t('key1') FROM DUAL; 

Oracle PL/SQL also provides a number of built-in functions for working with index-by tables. These include:

FIRST – returns the first key in the table
LAST- returns the last key in the table
NEXT – returns the next key after a given key
PRIOR – returns the key prior to a given key
EXISTS – returns TRUE if a given key exists in the table, FALSE otherwise
COUNT – returns the number of keys in the table
LIMIT – returns the maximum number of keys that can be stored in the table

PL/SQL Index-by tables are a powerful tool that can help you manage and manipulate data more effectively in Oracle database.

Example

Student table

STUDENT_ID	FIRST_NAME	LAST_NAME	CITY
1	Daniel	SCOTT	New York
2	Anthony	SIMMONS	Chicago
3	Sophia	THOMPSON	Los Angeles
4	Emily	PETERSON	Phoenix
5	David	DAWSON	Seattle
6	Gabriel	LEWIS	Boston
7	Natalie	MARTIN	Baltimore
8	Ava	GONZALES	Pasadena
9	Elliot	PETERSON	Chicago

Index-by tables(associative arrays)

DECLARE
	TYPE type_student IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(200);
	adr_list type_student;
	v_address   VARCHAR2(200);
BEGIN
	adr_list('Los Angeles') := 'address_1';
	adr_list('Chicago') := 'address_2';
	adr_list('Seattle') := 'address_3';
	v_address := adr_list.FIRST;
	WHILE v_address IS NOT null LOOP 
		FOR x in (SELECT * FROM students WHERE city=v_address)
		LOOP
			DBMS_OUTPUT.PUT_LINE (x.student_id||' - '||x.first_name||' '||x.last_name||' - '||x.city);      
		END LOOP;
		v_address := adr_list.NEXT(v_address);
	END LOOP;
END;
/

Output

2 – Anthony SIMMONS – Chicago

9 – Elliot PETERSON – Chicago

3 – Sophia THOMPSON – Los Angeles

5 – David DAWSON – Seattle