In the actual development process, especially when performing data queries, the query results can be converted and returned to the business processing logic (or displayed on the page) based on the dynamically generated SQL statements) it can greatly reduce the processing complexity of business logic.
In the actual development process, especially when performing data queries, the query results can be converted and returned to the business processing logic (or displayed on the page) based on the dynamically generated SQL statements) it can greatly reduce the processing complexity of business logic.
In most cases, this dynamically generated SQL query statement is written as follows:
The Code is as follows:
Select A table. field 1, Table. field 2, table B. field return, Table C. field return from Table A, table B, table C [where A, table B, table C Association and their respective condition statements]
However, this method has a disadvantage, that is, the business logic program that dynamically generates this query statement is still very complicated. This section describes how to generate a query SQL statement that reduces the complexity of business logic. The syntax structure is as follows:
The Code is as follows:
Select Table A. Field 1, Table A. Field 2, Table B. Field, Table C. Field from Table A [where A table's Condition Statement]
When a business logic program generates an SQL statement in this way, it only needs to modify the select field, instead of dynamically modifying the select field, from table, and where Statement at the same time as the general method. In this way, the business logic can reduce the focus of generating SQL statements from 3 + to 1. The following is an example:
First, create three tables, one info table that reflects the basic information of the student-student table, two code tables that store the student-related information-sexCode table (gender code table), and gradeCode table (age code table ), the table creation statement is as follows:
The Code is 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 64 K
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 'Year ';
Comment on column STUDENT. age
Is 'age ';
The Code is as follows:
-- Create table SEXCODE
Create table SEXCODE
(
DM char (1 ),
MC nvarchar2 (5)
)
Tablespace SDMP
Storage
(
Initial 64 K
Minextents 1
Maxextents unlimited
);
-- Add comments to the columns
Comment on column SEXCODE. DM
Is 'code ';
Comment on column SEXCODE. MC
Is 'name ';
The Code is as follows:
-- Create table GRADECODE
Create table GRADECODE
(
Dm char (1 ),
MC NVARCHAR2 (5)
)
Tablespace SDMP
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
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.
The Code is as follows:
-- Insert into student
Insert into student (id, name, sex, grade, age) values (1, 'zhang san', '1', '2', 8 );
Insert into student (id, name, sex, grade, age) values (2, 'lily', '0', '1', 11 );
Insert into student (id, name, sex, grade, age) values (3, 'wang wu', '1', '2', 9 );
Insert into student (id, name, sex, grade, age) values (4, 'Liu 2', '0', '4', 8 );
Insert into student (id, name, sex, grade, age) values (5, 'Han liu', '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', '1 ');
Insert into gradecode (dm, mc) values ('2', '2 ');
Insert into gradecode (dm, mc) values ('3', '3 ');
Finally, compare the common SQL query methods with the query methods proposed in this article and their query results:
Common query methods and query results are as follows:
The Code is 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 (+)
The query method and result are as follows:
The Code is 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
Note: 1. for the performance of the two, a simple test is conducted here. The query time for 1000 pieces of data is the same as that for the two, and the method mentioned in this article is even slightly better than the common method.
2. This method is currently only implemented and tested in the oracle database. Please test it for other databases.
