oracle 分页存储过程

存储过程包

create or replace package EQT_PAGINGS astype 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 isprocedure 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;