----普通的SQL语句 查询第10页的数据
select * from (select rownum as rn,t.* from 表名 t where rownum<=110) where rn >=101
-- 带排序的SQL语句 查询第10页的数据(速度慢)
select * from
(
select rownum as rn,t.* from
(
select * from 表名 where 1=1 order by 字段 排序规则
) t
) where rn between 101 and 110
--带排序的SQL语句 查询第10页的数据
-- 注意:这里的数据是查询够的内容排序,并不是排序后的查询,可能数据不是你想要的数据(不推荐常用)
select * from (select rownum as rn,t.* from 表名 t where rownum<=110 order by 字段名 排序规则) where rn >=101
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
-- 来至网络 游标分页
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
create or replace package DotNet is
-- Author : good_hy
-- Created : 2004-12-13 13:30:30
-- Purpose :
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
PROCEDURE DotNetPagination(
Pindex in number, --分页索引
Psql in varchar2, --产生dataset的sql语句
Psize in number, --页面大小
Pcount out number, --返回分页总数
v_cur out type_cur --返回当前页数据记录
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --产生dataset的sql语句
Prcount out number --返回记录总数
);
end DotNet;
------------------------------------------------注意:分段执行SQL语句
create or replace package body DotNet is
PROCEDURE DotNetPagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
AS
v_sql VARCHAR2(1000);
v_count number;
v_Plow number;
v_Phei number;
Begin
--取分页总数
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
--显示任意页内容
v_Phei := Pindex * Psize + Psize;
v_Plow := v_Phei - Psize + 1;
--Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
open v_cur for v_sql;
End DotNetPagination;
---------------------------------
-- 获取总记录数
---------------------------------
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;
end DotNet;
http://www.diybl.com/course/7_databases/oracle/oraclexl/20090818/168740.html#