Nested tables

Student table

STUDENT_IDFIRST_NAMELAST_NAMECITY
1DanielSCOTTNew York
2AnthonySIMMONSChicago
3SophiaTHOMPSONLos Angeles
4EmilyPETERSONPhoenix
5DavidDAWSONSeattle
6GabrielLEWISBoston
7NatalieMARTINBaltimore
8AvaGONZALESPasadena
9ElliotPETERSONChicago

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