oracle 分页存储过程

存储过程包

create or replace package EQT_PAGINGS as
type test_cursor is ref cursor;
procedure GetDataByPages(
 p_tableName varchar2,--表名
 p_fields varchar2,--查询的字段
 p_filter varchar2,--查询的条件
 p_sort varchar2,--排序字段
 p_curPage number,--第几页
 p_pageSize number,--每页多少条
 p_totalRecords out number,--总记录数
 p_cursor out EQT_PAGINGS.test_cursor --查询结果集
 );
 
end EQT_PAGINGS;

存储过程体

create or replace package body EQT_PAGINGS is
procedure GetDataByPages(
 p_tableName varchar2,--表名
 p_fields varchar2,--查询的字段
 p_filter varchar2,--查询的条件
 p_sort varchar2,--排序字段
 p_curPage number,--第几页
 p_pageSize number,--每页多少条
 p_totalRecords out number,--总记录数a
 p_cursor out EQT_PAGINGS.test_cursor --查询结果集
 )
 is
 v_sql varchar2(1000):='';
 v_startRecord number(6);    --支持10万条数据的查询（由于分页中不能支持原来过万数据的查询而修改） mmy 2015-7-1 
 v_endRecord number(6);
begin
--获取总的记录数
v_sql:='select to_number(count(*)) from '||p_tableName;
 if p_filter is not null then
 v_sql:=v_sql||' where 1=1 and '||p_filter;
 end if;
 execute immediate v_sql into p_totalRecords;

 v_startRecord:=(p_curPage-1)*p_pageSize;
 v_endRecord:=p_curPage*p_pageSize;
 v_sql:='select * from (select rownum as rownums,table_alias.* from (select ';
 if p_fields is not null then
 v_sql:=v_sql||p_fields;
 end if;
 v_sql:=v_sql||' from '|| p_tableName;
 if p_filter is not null then
 v_sql:=v_sql||' WHERE '||p_filter;
 end if;
 if p_sort is not null then
 v_sql:=v_sql||' ORDER BY '||p_sort;
 end if;
 v_sql:=v_sql||') table_alias) where rownums<='||to_char(v_endRecord)||' and rownums >'||to_char(v_startRecord);
 open p_cursor for v_sql;

 end GetDataByPages;
 
 end EQT_PAGINGS;