Nested tables

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