Oracle 游标分页

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;