Oracle nested representation example

Oracle nested representation example (from: http://www.cnblogs.com/starzhao/archive/2010/05/19/1739234.html) ---- nested table: is to define the field in a table as a table, the data of this field table is stored in an external table,
For example, the data in the next nested table is stored in the required_tab table.
---- Nested tables can effectively replace connections between multiple tables
Create type bookobj as object (
Title varchar2 (40 ),
Author varchar2 (40 ),
Catalog_number number (4)
);
/
Create type
Booklist as table of bookobj;
----------- Define a nested table, which can be nested in other tables. Its field is the field in bookobj.
/
Create Table
Course_material (
Department
Char (3 ),
Course
Number (3 ),
Required_reading booklist
----- Define another table in the table, that is, the booklist table, which is the table nested in course_material, that is, the nested table.

------ It is a master-slave relationship with the master table course_material, that is, a master table record corresponds to multiple records in a nested table.
) Nested table required_reading store
Required_tab;
/
----------------------- Enter a value for the table and nested table, and submit the value to the database for storage.
Declare
V_books
Booklist: = booklist (bookobj ('sssss', 'www ', 444); ------- define a nested table variable v_books of the booklist type and give the initial values.
Begin
Insert
Into course_material
Values ('cs ', 101, booklist (bookobj ('www', 'bbb ', 1), bookobj ('A', 'dd', 33 )));
Insert into course_material
Values ('his ', 301, v_books );
End;
Or insert a record separately:

Insert into course_material
Values ('ss', 102, booklist (bookobj ('w', 'B', 1), bookobj ('A', 'D', 3 )));
----------------------- Update a nested table, that is, using required_reading as a field
Declare
V_books
Booklist: = booklist (bookobj ('xyz', 'bbb ', 111), bookobj ('zq', 'ccc', 222 ));
Begin
Update
Course_material
Set required_reading =
V_books
Where Department = 'his' and
Course = 301;
End;
---- The execution result is that all the sub-records are deleted and two new bookobj records are added.
------------- Delete records in a nested table
Delete from course_material where Department =
'His '; ---- records in the nested table corresponding to the master table his will be deleted
==================Directly insert a nested table ==============================
Insert into the (select required_reading from
Course_material where Department = 'his ')
Values ('gog', 'ggg ', 999)
----- The red one indicates a table. Note that the preceding one must be added to indicate that the table is a nested table.
==================Directly update the nested table ==============================
Update the (select required_reading from course_material
Where Department = 'his ')
Set
Catalog_number = catalog_number + 10 ----- operate fields in the nested table
Where catalog_number = 111;
=================Directly Delete the nested table ==================================
Delete from the (select required_reading from
Course_material where Department = 'his ')
Where catalog_number = 111;
=========================================Directly query nested tables, only one sub-table with one record in the master table can be returned. That is, if course_material returns more than two records, an error is returned.
Select * from the (select required_reading from
Course_material where Department = 'his ');
Or
Select * from
The (select required_reading from course_material where Department = 'his ') Where
Catalog_number = 999
Error: Select * from
The (select required_reading from course_material where department in
('His ', 'www '))

---- Nested table: defines a field in a table as a table. The data in this field table is stored in an external table,
For example, the data in the next nested table is stored in the required_tab table.
---- Nested tables can effectively replace connections between multiple tables
Create type bookobj as object (
Title varchar2 (40 ),
Author varchar2 (40 ),
Catalog_number number (4)
);
/
Create type
Booklist as table of bookobj;
----------- Define a nested table, which can be nested in other tables. Its field is the field in bookobj.
/
Create Table
Course_material (
Department
Char (3 ),
Course
Number (3 ),
Required_reading booklist
----- Define another table in the table, that is, the booklist table, which is the table nested in course_material, that is, the nested table.

------ It is a master-slave relationship with the master table course_material, that is, a master table record corresponds to multiple records in a nested table.
) Nested table required_reading store
Required_tab;
/
----------------------- Enter a value for the table and nested table, and submit the value to the database for storage.
Declare
V_books
Booklist: = booklist (bookobj ('sssss', 'www ', 444); ------- define a nested table variable v_books of the booklist type and give the initial values.
Begin
Insert
Into course_material
Values ('cs ', 101, booklist (bookobj ('www', 'bbb ', 1), bookobj ('A', 'dd', 33 )));
Insert into course_material
Values ('his ', 301, v_books );
End;
Or insert a record separately:

Insert into course_material
Values ('ss', 102, booklist (bookobj ('w', 'B', 1), bookobj ('A', 'D', 3 )));
----------------------- Update a nested table, that is, using required_reading as a field
Declare
V_books
Booklist: = booklist (bookobj ('xyz', 'bbb ', 111), bookobj ('zq', 'ccc', 222 ));
Begin
Update
Course_material
Set required_reading =
V_books
Where Department = 'his' and
Course = 301;
End;
---- The execution result is that all the sub-records are deleted and two new bookobj records are added.
------------- Delete records in a nested table
Delete from course_material where Department =
'His '; ---- records in the nested table corresponding to the master table his will be deleted
==================Directly insert a nested table ==============================
Insert into the (select required_reading from
Course_material where Department = 'his ')
Values ('gog', 'ggg ', 999)
----- The red one indicates a table. Note that the preceding one must be added to indicate that the table is a nested table.
==================Directly update the nested table ==============================
Update the (select required_reading from course_material
Where Department = 'his ')
Set
Catalog_number = catalog_number + 10 ----- operate fields in the nested table
Where catalog_number = 111;
=================Directly Delete the nested table ==================================
Delete from the (select required_reading from
Course_material where Department = 'his ')
Where catalog_number = 111;
=========================================Directly query nested tables, only one sub-table with one record in the master table can be returned. That is, if course_material returns more than two records, an error is returned.
Select * from the (select required_reading from
Course_material where Department = 'his ');
Or
Select * from
The (select required_reading from course_material where Department = 'his ') Where
Catalog_number = 999
Error: Select * from
The (select required_reading from course_material where department in
('His ', 'www '))