Nested table
1. Definition of nested tables:
A nested table is a table in a table. A nested table is a set of rows. It is represented as one of the columns in the main table. For each record in the master table, the nested table can contain multiple rows. In a sense, it is a way to store one-to-multiple relationships in a table. Examine a table that contains department information. Each department has many projects under implementation at any time. In a strict relational model, two independent tables department and project are required.
Nested tables allow you to store project information in the department table. You do not need to perform the Union operation to directly access records in the project table through the department table. This ability to directly select data without association makes it easier for users to access data. Even if no method is defined to access the nested table, the Department and project data can be clearly linked. In a strict relational model, the association between the Department and project tables can be achieved only through external keywords (Foreign keys.
Ii. Examples:
Suppose there is a table about the animal breeders who want to have information about the animals they raise. With a nested table, you can store the information of the breeder and all the animals it raises in the same table.
1. Creation type animal_ty: In this type, each animal contains a record that records its breed, name, and date of birth.
Create type animal_ty as object (
Breed varchar2 (25 ),
Name varchar2 (25 ),
Birthdate date );
2. Create animals_nt: This type is used as the basic type of a nested table.
Create type animals_nt as table of animal_ty;
3. Create a table breeder: information table of the breeder
Create Table Breeder
(Breedername varchar2 (25 ),
Animals animal_nt)
Nested table animals store as animals_nt_tab;
4. insert records into the nested table
Insert into Breeder
Values ('Mary ', animal_nt (animal_ty ('Dog', 'butch', '31-Mar-97 '),
Animal_ty ('dog ', 'rover', '31-Mar-97 '),
Animal_ty ('dog ', 'julio', '31-Mar-97 ')));
Insert into Breeder
Values ('jar', animal_nt (animal_ty ('cat', 'any', '31-Mar-97 '),
Animal_ty ('cat', 'jame', '31-Mar-97 '),
Animal_ty ('cat', 'killer ', '31-Mar-97 ')));
Commit;
5. query nested tables
Select name, birthdate from
Table (select animals from breeder );
Select name, birthdate from
Table (select animals from Breeder
Where breedername = 'Mary ')
Where name = 'Dog ';
Iii. Features of nested tables:
1. Object reuse: If you write an object-orientedCodeTo improve the chances of reusing previously written code modules. Similarly, the creation of object-oriented database objects increases the chance of database objects being reused.
2. Standard Support: If standard objects are created, their chances of being reused increase. If multiple applications or tables use the same database object set, it is a database object standard that is a fact.
3. Define the access path: for each object, you can define the processes and functions on it, so that the data can be combined with the methods used to access the data. With the access path defined in this way, you can standardize the data access method and improve the reusability of objects.
Variable Array
I. Definition of variable Arrays:
A variable array is similar to a nested table and is also a set. A mutable array is a collection of objects, each of which has the same data type. The size of a variable array is determined by the size when it is created. After a variable array is created in the table, the variable array is treated as a column in the main table. Conceptually, a mutable array is a nested table that limits the set of rows.
A variable array that allows you to store duplicate attributes in a table. For example, if a user has a project table and a staff member is specified in the project, a project can have multiple workers, and a worker can also work for multiple projects. In a strict relational model, you can create a project table, a worker table, and a cross table that stores the relationships between them, project_worker.
You can use a variable array to store workers' names in the project table. If the number of workers in a project cannot exceed 10, you can create a variable array that is limited to 10 data items. Next, you can process this variable array, so that you can select the names of all workers for each project, instead of querying the worker table.
Ii. Examples:
1. Create comm_info
Create type comm_info as object (/* This type is a set of communication methods
No number (3),/* Communication type
Comm_type varchar2 (20),/* Communication type
Comm_no varchar2 (30);/* Number
2. Create a variable array comm_info_list
Create type comm_info_list
Varray (50) of comm_info;
3. Create a table
Create Table user_info
(User_id number (6),/* User ID
User_name varchar2 (20),/* User Name
User_comm comm_info_list);/* contact the user
4. insert records into a variable array
Insert into user_info
Values (1, 'Mary ', comm_info_list (comm_info (1, 'cell phone', '123 '),
Comm_info (2, 'pagers ', '123 ')));
Insert into user_info
Values (2, 'cars', comm_info_list (comm_info (1, 'cell phone ', '123 '),
Comm_info (2, 'pagers ', '123 ')));
Commit;
5. query variable Arrays
Select user_comm from user_info
Where user_id = 1;
Select comm_type, comm_no
From table (select user_comm from user_info
Where user_id = 1)
Where no = 1;
There are many ways to contact a user, such as mobile phones, pagers, and landlines. In a strict relational model, two independent tables are required: user information and communication methods. In a variable array, the user's contact information can be directly accessed in the user_info table, this ability to directly select data without association makes it easier for users to access data.
Iii. variable array features:
1. Object reuse: If you write object-oriented code, you will be given the opportunity to reuse previously written code modules. Similarly, the creation of object-oriented database objects increases the chance of database objects being reused.
2. Standard Support: If standard objects are created, their chances of being reused increase. If multiple applications or tables use the same database object set, it is a database object standard that is a fact.
3. Define the access path: for each object, you can define the processes and functions on it, so that the data can be combined with the methods used to access the data. With the access path defined in this way, you can standardize the data access method and improve the reusability of objects.
