Using Flattened Subqueries:
To manipulate the individual rows of a nested table stored in a database column that use the keyword THE. It is a prefixed keyword to a subquery which returns a single column value or an expression which yields a nested table. A runtime error occurs if the subquery returns more than a single column value. Since the value is a nested table, not a scalar value, An Oracle must be informed, that is what THE does.
QUERING NESTED TABLE Values alone (Built-in Function Used: THE)
SELECT nt.studno, nt.studname, nt.sex FROM
THE (SELECT students FROM faculty WHERE
factno = 1 ) nt ;
Note, here only one main subset value can be selected. The display of the above query will be,
STUDNO STUDNAME S
----- ------------ -
10 Rajesh M
11 Suresh M
12 Uma F
Here, if a user requires entering another record that can be done using the subsequent syntax:
INSERT INTO THE (SELECT students FROM faculty
WHERE factno=1) nt
VALUES (13, 'Hema', 'F');
1 row gets created. If the previous query is issued, the output changes to,
STUDNO STUDNAME S
--------- ---------------- -
10 Rajesh M
11 Suresh M
12 Uma F
13 Hema F
In the case of Nested table, a particular column value can be inserted using,
INSERT INTO THE ( SELECT students FROM faculty
WHERE factno=1 ) nt
(Nt.studname) VALUES ( 'Badri' ) ;
The output of the query after insert is ,
SELECT nt.studno, nt.studname, nt.sex FROM
THE (SELECT students FROM faculty WHERE
factno = 1 ) nt;
STUDNO STUDNAME S
--------- -------------------- -
10 Rajesh M
11 Suresh M
12 Uma F
13 Hema F
Badri
Data in the Relational table can be inserted into the Nested table using the function known as CAST. The CAST along with MULTISET operator and converts the relational record to a nested table record.
INSERT INTO faculty VALUES
(2,'Geetha',
CAST(MULTISET (SELECT * FROM THE (SELECT #nested query#
Students FROM faculty WHERE factno = 1))
AS stud_nt)
);
The output when all the records are selected will be,
FACTNO NAME
--------- -----------------------------
STUDENTS (STUDNO, STUDNAME, SEX)
---------------------------------------
1 Ramesh
STUD_NT(STUD_TYPE(10,'Rajesh','M'), STUD_TYPE(11,'Suresh','M'),ST
_TYPE(12,'Uma','F'),STUD_TYPE(13, 'Hema ', 'F'), STUD_TYPE(NULL
'Badri ', NULL), STUD_TYPE(NULL, 'Badri ', NULL))
2 Geetha
STUD_NT(STUD_TYPE(10, 'Rajes ', 'M'), STUD_TYPE(11, 'Suresh ', 'M'), ST_TYPE(12, 'Uma ', 'F'), STUD_TYPE(13, 'Hema ', 'F'), STUD_TYPE(NULL 'Badri ', NULL), STUD_TYPE(NULL, 'Badri ', NULL))