Oracle 游标分页
先建一个包声明一个游标类型
create or replace package pkg_dividepage as
type cur_page is ref cursor;
end pkg_dividepage;
然后创建存储过程 如下:
create or replace procedure proc_dividepage(p_tableName varchar2, --表名
p_pageIndex number, --当前页码
p_pageSize number, --每页记录数
p_pageCount out number, --总页数
p_totalCount out number, --总记录数
v_cursor out pkg_dividepage.cur_page, --返回的结果集
p_where varchar2, --查询条件
p_order_key varchar2, --排序关键字(asc desc)
p_order_column varchar2 --排序字段
) as
v_pageIndex number;
v_pageSize number;
v_startCurs number;
v_endCurs number;
v_sql varchar2(2000);
--输出参数可以对其进行运算操作,所以声明为out的参数在此不用定义变量来操作
begin
v_sql := 'select count(*) from ' || p_tableName; -- ||'where 1=1';
if p_where is not null or p_where <> '' then
v_sql := v_sql || ' where ' || p_where;
end if;
execute immediate v_sql
into p_totalCount; --查询总记录数
p_pageCount := ceil(p_totalCount / p_pageSize); --计算总页数
v_pageSize := p_pageSize;
if v_pageSize < 0 then
v_pageSize := 0;
end if;
v_pageIndex := p_pageIndex;
if v_pageIndex < 0 then
v_pageIndex := 1;
end if;
if v_pageIndex > p_pageCount then
v_pageIndex := p_pageCount;
end if;
v_startCurs := (v_pageIndex - 1) * v_pageSize + 1;
v_endCurs := v_pageIndex * v_pageSize;
v_sql := 'select * from (select rownum num,t.* from (select * from ' ||
p_tableName;
if p_where is not null or p_where <> '' then
v_sql := v_sql || ' where ' || p_where;
end if;
if p_order_column is not null or p_order_column <> '' then
v_sql := v_sql || ' order by ' || p_order_column || ' ' || p_order_key;
end if;
v_sql := v_sql || ') t where rownum<=' || v_endCurs || ')where num>=' ||
v_startCurs;
open v_cursor for v_sql;
dbms_output.put_line(v_sql);
end proc_dividepage;
最后v_sql:
select * from (select t.*,rownum rn from (select * from p_tablename where p_where order by p_order_column p_order_key) t where rownum<=v_endCurs) where rn>=v_startCurs;