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 |