程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> ORACLE和SYBASE數據庫中實現數據查詢條數限制的SQL語句實現

ORACLE和SYBASE數據庫中實現數據查詢條數限制的SQL語句實現

編輯:Oracle教程

ORACLE和SYBASE數據庫中實現數據查詢條數限制的SQL語句實現


一、概述
對於某些需要通過數據庫與大量數據打交道的軟件來說,處理性能相當的重要。為了保證軟件能夠將所有數據處理完而不至於崩潰,分批處理的思想應運而生。分批處理的具體做法是編寫SQL語句,每次返回規定條數的數據給軟件處理,待這一批數據處理完之後,再接著處理下一批。
本文通過對具體的數據庫表(tb_employeeinfo)的操作過程,展示了ORACLE和SYBASE數據庫中分批處理SQL語句的編寫方法。

二、ORACLE數據庫中的處理
首先,建立tb_employeeinfo表,其定義如下:

begin
execute 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;

接著,在tb_employeeinfo表中插入7條數據,如下:

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);

如果我們想要一次性從tb_employeeinfo表中查詢出5條數據,該如何處理呢?
ORACLE數據庫中有一個rownum用在查詢(select)語句中來限制每次執行之後返回的數據條數。例如,本次要從tb_employeeinfo表中返回5條數據,則編寫SQL語句如下:

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

執行結果如下:

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

EMPLOYEENO    EMPLOYEENAME    EMPLOYEEAGE
A1000         ZhangSan        20
A1001         LiSi            21
A1002         WangWu          21
A1003         ZhouLiu         22
A1004         SunQi           22

三、SYBASE數據庫中的處理
首先,建立tb_employeeinfo表,其定義如下:

if exists(select * from sysobjects where name='tb_employeeinfo')
    drop table tb_employeeinfo
go
create 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
)
go
create unique index idx1_tb_employeeinfo on tb_employeeinfo(employeeno)
go
print 'create table tb_employeeinfo ok'
go

接著,在tb_employeeinfo表中插入7條數據,如下:

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)

如果我們想要一次性從tb_employeeinfo表中查詢出5條數據,該如何處理呢?
在SYBASE數據庫中,可以利用“set rowcount X”語句來實現查詢條數的限制。例如,本次要從tb_employeeinfo表中返回5條數據,則編寫SQL語句如下:

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

執行結果如下:

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

注意,在設置了查詢條數為5並查詢成功之後,一定要有“set rowcount 0”語句,否則在下次執行的時候,就最多只能返回5條數據。
例如,我們先執行如下語句:

set rowcount 5
select employeeno, employeename, employeeage from tb_employeeinfo

則此時返回的結果與上面一樣。

再執行如下語句(本意是要將7條語句都查詢出來):

select employeeno, employeename, employeeage from tb_employeeinfo

但此時的結果仍然只返回了5條,與我們的本意不符。
因此,在SYBASE數據庫中,“set rowcount X”語句一定要與“set rowcount 0”語句配對使用。

四、總結
相比本文中的數據表,在實際的軟件項目中的數據表的字段要更多一些,數據量也要更大一些,在查詢語句中也有可能會帶有一些其它條件,但基本的SQL語句編寫模式是一致的。大家可以參照來編寫對應的SQL語句。

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved