Oracle分页查询存储过程（适用于单表查询）

最近在学习存储过程，网上搜了一些例子，然后自己对照着写了一个相对简单的分页查询...

1.创建包

--/
CREATE OR REPLACE PACKAGE PKG_PAGINATION as
TYPE pkg_cursor is ref cursor;
END;
/

2.调用过程

--/
CREATE OR REPLACE PROCEDURE PAGINATION_CALL(
tabName in varchar2,
pageSize in number,
pageNum in number,
totalRows out number,
totalPage out number,
p_cursor out pkg_pagination.pkg_cursor,
p_where in varchar2,
p_orderby in varchar2,
p_cnsql out varchar2,
p_rssql out varchar2
)AS
v_sql varchar2(1000);
v_count number;
BEGIN
  p_cnsql:='';
  p_rssql:='';
  v_sql:='from '||tabName||' where 1=1'||p_where;
  PAGINATION_COUNT(v_sql,totalRows,p_cnsql);
  v_count:=totalRows;
  If(v_count>0) Then
  v_sql:=v_sql||p_orderby;
  PAGINATION_RESULT(v_sql,pageNum,pageSize,v_count,totalPage,p_cursor,p_rssql);
  Else
  dbms_output.put_line('NO DATA!');
  End If;
END;
/

3.查询返回结果记录总数过程

--/
CREATE OR REPLACE PROCEDURE PAGINATION_COUNT(
p_sql in varchar2,
totalRows out number,
r_sql out varchar2
)AS
BEGIN
  r_sql:='select count(*) '||p_sql;
  execute immediate r_sql into totalRows;
END;
/

4.查询返回页结果集过程

--/
CREATE OR REPLACE PROCEDURE PAGINATION_RESULT(
p_sql in varchar2,
pageNum in number,
pageSize in number,
totalRows in number,
totalPage out number,
p_cursor out pkg_pagination.pkg_cursor,
r_sql out varchar2
)AS
v_begin number:=(pageNum-1)*pageSize+1;
v_end number:=pageNum*pageSize;
BEGIN
  r_sql:='select * from (select t1.*, rownum rn from (select * '||p_sql||') t1 where rownum<='||v_end||') where rn>='||v_begin;
  open p_cursor for r_sql;
  if mod(totalRows,pagesize)=0 then
  totalPage:=totalRows/pagesize;
  else
  totalPage:=totalRows/pagesize+1;
  end if;
END;
/

(p.s. 初学不久，高手勿喷)