SQL statement implementation for data query bar limits in Oracle and Sybase databases

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