Nested tables and variable Arrays

Source: Internet
Author: User
Nested tables and variable Arrays
From: http://www.zhaoyang-db.com.cn Author: (08:02:00)

 

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.