Oracle 分页语句，存储过程

select * from (select t.*,rownum rn from (select * from emp) t where rownum<=10) where rn>=6;

 

创建分页结果集的游标

create or replace package fenyepackage as
type testcursor is ref cursor;
end fenyepackage;

 

创建分页存储过程

create or replace procedure fenye3(
tableName varchar2, --表名
currPage number,    --当前页
currPageSize number, --页大小
countRows out number, --总记录数
countPages out number, --总页数
fenyecursor out  fenyepackage.testcursor --当前页结果集
)is
v_sql varchar2(1000);
v_begin number:=(currPage-1)*currPageSize + 1;
v_end number:=currPage*currPageSize;
begin
v_sql:='select * from (select t.*,rownum rn from (select * from '|| tableName ||') t where rownum<='|| v_end ||')
                where rn>='||v_begin;
                           
open fenyecursor for v_sql;
v_sql:='select count(*) from '|| tableName;
execute immediate v_sql into countRows;

if mod(countRows,currPageSize)=0 then
countPages:=countRows/currPageSize;
else
countPages:=countRows/currPageSize+1;
end if;
close fenyecursor;
end;