Nested tables

When working with Oracle PL/SQL, you may sometimes need to store data in a nested table. A nested table is a data structure that allows you to store multiple values in a single column.

PL/SQL nested tables are similar to arrays in other programming languages, but they have some important differences. First of all, nested tables can be stored in the database, where as arrays can only be used in memory. This means that you can query and update nested tables using PL/SQL, and you can pass them as parameters to stored procedures.

Nested tables are declared using the TYPE keyword, followed by the name of the nested table type. The name of the nested table type must be unique within the database. Nested tables can be either unbounded or bounded.

Unbounded nested tables do not have a fixed size, and can grow or shrink as needed. Bounded nested tables have a fixed size, and cannot grow or shrink.

Nested tables are always index-by tables, which means that they are accessed using a numeric index. The first element in the nested table has an index of 1, and the last element has an index of n, where n is the number of elements in the nested table.

Nested tables can be empty, which means that they do not contain any elements. An empty nested table is different from a NULL nested table, which means that the nested table has not been initialized.

PL/SQL nested tables can be stored in the database as columns of a table, or they can be passed as parameters to stored procedures and functions. When working with nested tables, you can use the following keywords:

– TYPE: Used to declare a nested table type
– IS TABLE OF: Used to create a variable or column of a nested table type
– INDEX BY: Used to specify the index of a nested table
– RETURN: Used to return a nested table from a function
– CAST(): Used to convert one data type to another
– COLLECT(): Used to create a nested table from a set of values
– EXTEND(): Used to add an element to a nested table
– FIRST(): Used to return the first element of a nested table
– LAST(): Used to return the last element of a nested table
– LIMIT(): Used to specify the maximum number of elements that can be stored in a nested table
– PRIOR(): Used to return the element prior to the current element in a nested table
– NEXT(): Used to return the element after the current element in a nested table
– TRIM(): Used to remove elements from a nested table
– EXISTS(): Used to test for the existence of an element in a nested table
– COUNT(): Used to return the number of elements in a nested table

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

Nested tables

DECLARE
	TYPE stu IS TABLE OF VARCHAR2(10);
	last_names stu;
	total NUMBER;
BEGIN
	last_names := stu('PETERSON', 'LEWIS', 'THOMPSON');
	total := last_names.COUNT;
	dbms_output.put_line('Total distinct name: '|| total);
	FOR i IN 1 .. total LOOP
		FOR x IN (SELECT * FROM students WHERE LAST_NAME=last_names(i))
		LOOP
			dbms_output.put_line
			('ID: '||x.student_id||' - Student: '||x.first_name||' '||x.last_name);
		END LOOP; 
	END LOOP;
END;
/

Output

Total distinct name: 3

ID: 4 – Student: Emily PETERSON

ID: 9 – Student: Elliot PETERSON

ID: 6 – Student: Gabriel LEWIS

ID: 3 – Student: Sophia THOMPSON