SQL statement implementation that limits the number of data queries in ORACLE and SYBASE databases

SQL statement implementation that limits the number of data queries in ORACLE and SYBASE databases

I. Overview
For some software that needs to deal with a large amount of data through databases, processing performance is very important. In order to ensure that the software can process all data without crashing, the idea of batch processing came into being. The specific method of batch processing is to compile an SQL statement. Each time a specified number of data records are returned to the software for processing, after the batch of data is processed, the next batch is processed.
This article describes how to write SQL statements in batches in ORACLE and SYBASE databases through the operation process of specific database tables (tb_employeeinfo.

2. ORACLE database processing
First, create the tb_employeeinfo table, which is defined as follows:

beginexecute immediate 'drop table tb_employeeinfo CASCADE CONSTRAINTS';EXCEPTION WHEN OTHERS THEN NULL;end;/create table tb_employeeinfo(    employeeno    varchar2(20)     not null,    -- no. of employee    employeename  varchar2(20)     not null,    -- name of employee    employeeage   int              not null     -- age of employee);create unique index idx1_tb_employeeinfo on tb_employeeinfo(employeeno);prompt 'create table tb_employeeinfo ok';commit;

Then, insert seven data entries in the tb_employeeinfo table, as shown below:

insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1000', 'ZhangSan', 20);insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1001', 'LiSi', 21);insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1002', 'WangWu', 21);insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1003', 'ZhouLiu', 22);insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1004', 'SunQi', 22);insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1005', 'LiuBa', 23);insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1006', 'ChenShi', 25);

If we want to query 5 data records from the tb_employeeinfo table at a time, what should we do?
The ORACLE database has a rownum used in the query (select) statement to limit the number of data records returned after each execution. For example, if you want to return 5 data records from the tb_employeeinfo table, write the following SQL statement:

select employeeno, employeename, employeeage from tb_employeeinfo where rownum<=5;

The execution result is as follows:

SQL> select employeeno, employeename, employeeage from tb_employeeinfo where 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 * from sysobjects where name='tb_employeeinfo') drop table tb_employeeinfogocreate table tb_employeeinfo( employeeno varchar(20) not null, -- no. of employee employeename varchar(20) not null, -- name of employee employeeage int not null -- age of employee)gocreate unique index idx1_tb_employeeinfo on tb_employeeinfo(employeeno)goprint 'create table tb_employeeinfo ok'go

Then, insert seven data entries in the tb_employeeinfo table, as shown below:

insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1000', 'ZhangSan', 20)insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1001', 'LiSi', 21)insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1002', 'WangWu', 21)insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1003', 'ZhouLiu', 22)insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1004', 'SunQi', 22)insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1005', 'LiuBa', 23)insert into tb_employeeinfo(employeeno, employeename, employeeage) values('A1006', 'ChenShi', 25)

If we want to query 5 data records from the tb_employeeinfo table at a time, what should we do?
In the SYBASE Database, you can use the "set rowcount X" statement to limit the number of queries. For example, if you want to return 5 data records from the tb_employeeinfo table, write the following SQL statement:

set rowcount 5select employeeno, employeename, employeeage from tb_employeeinfoset rowcount 0

The execution result is as follows:

employeeno employeename employeeage A1000 ZhangSan 20 A1001 LiSi 21 A1002 WangWu 21 A1003 ZhouLiu 22 A1004 SunQi 22

Note: After setting the number of query entries to 5 and the query is successful, you must have the "set rowcount 0" statement. Otherwise, at the next execution, you can only return up to five data records.
For example, execute the following statement first:

set rowcount 5select employeeno, employeename, employeeage from tb_employeeinfo

The returned results are the same as those above.

Execute the following statement (to query all 7 statements ):

select employeeno, employeename, employeeage from tb_employeeinfo

However, at this time, only five results are returned, which is inconsistent with our intention.
Therefore, in the SYBASE Database, the "set rowcount X" statement must be paired with the "set rowcount 0" statement.

Iv. Summary
Compared with the data table in this article, the data table fields in the actual software project are more, the data volume is larger, and some other conditions may exist in the query statement, however, the basic SQL statement compiling modes are the same. You can compile the corresponding SQL statements by reference.