Oracle分页

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