Oracle分页

最新推荐文章于 2026-01-03 12:21:22 发布

原创最新推荐文章于 2026-01-03 12:21:22 发布 · 688 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #dataset #网络

数据库相关专栏收录该内容

4 篇文章

订阅专栏

----普通的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;