I. Overview
For some software that needs to deal with large amounts of data through a database, processing performance is quite important. In order to ensure that the software can finish all the data without crashing, the idea of batch processing came into being. The specific method of batch processing is to write SQL statements, each return the specified number of data to the software processing, after this batch of data processing, then the next batch.
This paper demonstrates how to write batch-processed SQL statements in Oracle and Sybase database through the operation process of the specific database table (Tb_employeeinfo).
II. processing in the Oracle database
First, create the Tb_employeeinfo table, which is defined as follows:
beginexecute immediate ' drop table tb_employeeinfo CASCADE CONSTRAINTS ';EXCEPTION when OTHERS and then NULL; end;/ Create table tb_employeeinfo (Employeeno varchar2 () not null, -- no. of employee EmployeeName VARCHAR2 (a) not null,--name of EMP Loyee employeeage int not null --age of employee); Create unique index idx1_tb_employeeinfo on Tb_employeeinfo (Employeeno);Prompt ' Create table tb_employeeinfo ok'; commit;
Next, insert 7 data in the Tb_employeeinfo table, as follows:
Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values(' A1000 ', ' Zhangsan ', '; Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values( ' A1001', ' LiSi ', +); Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values(' A1002 ', ' Wangwu ', +); Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values(' A1003 ', ' Zhouliu ', (); Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values(' A1004 ', ' Sunqi ', '; Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values(' A1005 ', ' Liuba ', at a); Insert into Tb_employeeinfo (Employeeno, EmployeeName, employeeage) values(' A1006 ', ' Chenshi ', ();
What if we want to query 5 data from the Tb_employeeinfo table at once?
There is one rownum in the Oracle database that is used in a query (SELECT) statement to limit the number of data bars returned after each execution. For example, to return 5 data from the Tb_employeeinfo table, write the SQL statement as follows:
select employeeno, employeename, employeeage from tb_employeeinfo where rownum<=5;
The results of the implementation are as follows:
selectfromwhere rownum<=5;EMPLOYEENO EMPLOYEENAME EMPLOYEEAGEA1000 ZhangSan 20A1001 LiSi 21A1002 WangWu 21A1003 ZhouLiu 22A1004 SunQi 22
iii. processing in the Sybase database
First, create the Tb_employeeinfo table, which is defined as follows:
if exists (Select* fromsysobjectswhereName=' Tb_employeeinfo ')Drop TableTb_employeeinfoGoCreate TableTb_employeeinfo (Employeenovarchar( -) not NULL, --No. ofEmployee EmployeeNamevarchar( -) not NULL,--Name ofEmployee Employeeageint not NULL--Age ofEmployeeGoCreate UniqueIndex Idx1_tb_employeeinfo onTb_employeeinfo (Employeeno)GoPrint' CREATE table tb_employeeinfo OK 'Go
Next, insert 7 data in the Tb_employeeinfo table, as follows:
Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1000 ',' Zhangsan ', -)Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1001 ',' LiSi ', +)Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1002 ',' Wangwu ', +)Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1003 ',' Zhouliu ', A)Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1004 ',' Sunqi ', A)Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1005 ',' Liuba ', at)Insert intoTb_employeeinfo (Employeeno, EmployeeName, Employeeage)Values(' A1006 ',' Chenshi ', -)
What if we want to query 5 data from the Tb_employeeinfo table at once?
In the Sybase database, you can use the "SET ROWCOUNT X" statement to limit the number of query bars. For example, to return 5 data from the Tb_employeeinfo table, write the SQL statement as follows:
set5selectfrom tb_employeeinfoset0
The results of the implementation are as follows:
employeeno employeename employeeage A1000 ZhangSan 20 A1001 LiSi 21 A1002 WangWu 21 A1003 ZhouLiu 22 A1004 SunQi 22
Note that after you set the number of query bars to 5 and the query succeeds, you must have a "SET ROWCOUNT 0" statement, or you can return up to 5 data at the next execution.
For example, we first execute the following statement:
set5selectfrom tb_employeeinfo
The result returned is the same as above.
Then execute the following statement (intended to query all 7 statements):
selectfrom tb_employeeinfo
But the result of this time is still only returned 5, and our intentions do not match.
Therefore, in the Sybase database, the "SET ROWCOUNT X" statement must be paired with the "SET ROWCOUNT 0" statement.
Iv. Summary
Compared to the data table in this article, in the actual software project, the data table in the field to be more, the amount of data is larger, in the query statement may also have some other conditions, but the basic SQL statement writing pattern is consistent. You can write the corresponding SQL statement by reference.
My public number: ZHOUZXI, please scan the following two-dimensional code:
SQL statement implementation for data query bar limits in Oracle and Sybase databases