Methods for associating other tables in the Select query field of an Oracle database query _oracle

In most cases, this dynamically generated SQL query statement is written as follows:

Copy Code code as follows:

Select a table. field 1,a table. field 2,b table. field returns, C table. field returns from Table A, B table, C table [where a table, B table, C Table Association and respective conditional statement]

But the disadvantage of this approach is that the business logic program that generates this query dynamically is still complex. Here we introduce a query SQL generation method to reduce the complexity of business logic. Its grammatical structure is as follows:

Copy Code code as follows:

Select a table. field 1,a table. field 2,b table. field, C table. field from a table [where a table a conditional statement]

The SQL statements generated by the business logic program in this way simply modify the fields of the Select, without the need to dynamically modify the Select field, from the table, and the where statement, as you would a common method. This entire business logic reduces the focus of generating SQL statements from 3+ to 1. Here are some examples of how to implement this method:

First, the establishment of three tables, a response to the basic situation of students information table--student table, two information for students to store the Code table--sexcode table (Sex Code table), Gradecode (age Code table), the table statement is as follows:

Copy Code code as follows:

--Create Table STUDENT
CREATE TABLE STUDENT
(
ID number,
Name Nvarchar2 (10),
Sex char (1),
Grade char (1),
Age Number (2)
)
Tablespace sdmp
Storage
(
Initial 64K
Minextents 1
Maxextents Unlimited
);
--ADD comments to the columns
Comment on column student.name
Is ' student name ';
Comment on column student.sex
Is ' student gender ';
Comment on column Student.grade
is ' grade ';
Comment on column student.age
is ' age ';

Copy Code code as follows:

--Create Table Sexcode
CREATE TABLE Sexcode
(
DM char (1),
MC NVARCHAR2 (5)
)
Tablespace sdmp
Storage
(
Initial 64K
Minextents 1
Maxextents Unlimited
);
--ADD comments to the columns
Comment on column Sexcode. Dm
is ' code ';
Comment on column Sexcode. Mc
is ' name ';

Copy Code code as follows:

--Create Table Gradecode
CREATE TABLE Gradecode
(
DM CHAR (1),
MC NVARCHAR2 (5)
)
Tablespace sdmp
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Minextents 1
Maxextents Unlimited
);
--ADD comments to the columns
Comment on column Gradecode. Dm
is ' code ';
Comment on column Gradecode. Mc
is ' name ';

Then, execute the following insert statement and fill in the information in each table separately.

Copy Code code as follows:

--insert into student
INSERT into student (Id,name,sex,grade,age) VALUES (1, ' John ', ' 1 ', ' 2 ', 8);
INSERT into student (Id,name,sex,grade,age) VALUES (2, ' Dick ', ' 0 ', ' 1 ', 11);
INSERT into student (Id,name,sex,grade,age) VALUES (3, ' Harry ', ' 1 ', ' 2 ', 9);
INSERT into student (Id,name,sex,grade,age) VALUES (4, ' Liu Yi ', ' 0 ', ' 4 ', 8);
INSERT into student (Id,name,sex,grade,age) VALUES (5, ' Korea Six ', ' 0 ', ' 3 ', 6);

--insert into Sexcode
Insert into Sexcode (DM,MC) VALUES (' 1 ', ' Male ');
Insert into Sexcode (DM,MC) VALUES (' 0 ', ' female ');

--insert into Gradecode
Insert into Gradecode (DM,MC) VALUES (' 1 ', ' first grade ');
Insert into Gradecode (DM,MC) VALUES (' 2 ', ' second Grade ');
Insert into Gradecode (DM,MC) VALUES (' 3 ', ' third grade ');

Finally, the paper gives the common SQL query method and the Query method and its query result comparison:
The general Query method and its query results are as follows:

Copy Code code as follows:

Select S.ID,S.NAME,SC.MC SEX,GC.MC grade,s.age
From student S,sexcode Sc,gradecode GC
where Sc.dm=s.sex (+) and s.grade=gc.dm (+)

Id	NAME	SEX	GRADE	Age
1	2	John doe	Woman	First-year	11
2	3	Harry	Man	Second Grade	9
3	1	Tom	Man	Second Grade	8
4	5	Han Liu	Woman	Third Grade	6
5	4	Liu Yi	Woman		8

The query method and the results of the query are as follows

Copy Code code as follows:

Select S.id,s.name,s.age,
(Select MC from Sexcode where dm=s.sex) sex,
(Select MC from Gradecode where Dm=s.grade) grade
From student S

Id	NAME	Age	SEX	GRADE
1	1	Tom	8	Man	Second Grade
2	2	John doe	11	Woman	First-year
3	3	Harry	9	Man	Second Grade
4	4	Liu Yi	8	Woman
5	5	Han Liu	6	Woman	Third Grade

Note: 1. For the performance of both, this is just a simple test, 1000 data query time is quite the same, and this article mentions the method is even slightly better than the common method.

2. This method is currently implemented and tested only in an Oracle database, and other databases are tested on your own.