oracle分页存储过程

create or replace package mypack

as

type cursor testCursor is ref cursor;

end mypack;

tableName表名 pageSize每页显示的数量pageNow第几页rows总页数pageCount总页数p_cursor游标

create or replace procedure fenye

(tableName in varchar pageSize in number pageNow in number rows out number pageCount out number p_cursor out mypacktestCursor)

is

declare

定义sql语句

v_sql varchar();

定义个整数记录上一页

v_begin number := (pageNow ) * pageSize + ;

v_end number := pageNow * pageSize;

begin

v_sql := select * from (select t* rownum r from (select * from || tableName ||) t where rownum<=|| v_end ||) where r>=|| v_begin ;

将游标和sql语句关联起来

open p_cursor for v_sql;

计算rows和pageCount;

v_sql := select count(*) from || tableName;

执行sql语句并将返回的值付给 rows;

execute immediate v_sql into rows;

pageCount := (rows + pageSize ) / pageSize; 总页数;

close p_cursor;

end;