分页的存储过程

最新推荐文章于 2022-08-24 11:14:15 发布

iteye_14794

最新推荐文章于 2022-08-24 11:14:15 发布

阅读量84

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/iteye_14794/article/details/82538260

版权

oracle 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

分页的存储过程

create or replace package pagingPackage as
type paging_cursor is ref cursor;
end pagingPackage;

create or replace procedure pagingProcedure
(
   tableName in varchar2,
   pageSize in number,
   pageNow in number,
   rowsCount out number,
   pageCount out number,
   p_cursor out pagingPackage.paging_cursor
) 
is
v_sql varchar2(1000);
v_begin number := (pageNow - 1) * pageSize + 1;
v_end number := pageNow * pageSize;
begin
   v_sql := 'select * from ( select t.*,rownum rn from (select * from '||tableName||') t where rownum <= '|| v_end ||') where rn >= '|| v_begin;
   open p_cursor for v_sql;
   v_sql := 'select count(*) from '||tableName;
   execute immediate v_sql into rowsCount;
   if mod(rowsCount,pageSize) = 0 then
      pageCount := rowsCount/pageSize;
   else
      pageCount := rowsCount/pageSize + 1;
   end if;
   close p_cursor;
end;

http://www.cnblogs.com/zfc2201/archive/2012/03/12/2392475.html