Hi All,
Just would like to confirm with you about my understanding on Nested Table. Below code snippet is about students with one or more courses enrolled. For example, student 1 registered for 'A' course, Student 2 is associated with the courses 'B' and 'C', etc.
Is Nested table is just about this alone or do we have anything else as well about Nested tables? If yes, could you please give me some good links further? Thank you.
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
NESTED TABLE col1 STORE AS col1_tab;
--Insert data into table:
INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
COMMIT;
--Select from nested table:
SQL> SELECT * FROM nested_table;
ID COL1
---------- ------------------------
1 MY_TAB_T('A')
2 MY_TAB_T('B', 'C')
3 MY_TAB_T('D', 'E', 'F')
--Unnesting the subtable:
SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;
ID COLUMN_VALUE
---------- ------------------------
1 A
2 B
2 C
3 D
3 E
3 F
6 rows selected.