Oracle存储过程实现海量数据分页查询

 
CREATE OR REPLACE Procedure sp_page(i_tablename   in varchar2, --表名emp e  (也可以这样 emp e left join dep d on e.dep_id=d.dep_id)
                                    i_tablecolumn in varchar2, --查询列e.id,e.ename,e.job
                                    i_order       in varchar2, --排序e.ename desc
                                    i_pagesize    in integer, --每页大小
                                    i_curpage     in integer, --当前页
                                    i_where       in varchar2, --查询条件e.ename like '%S%'                                   
                                    o_cur_count   out sys_refcursor, --行数和页数
                                    o_cur_data    out sys_refcursor --结果集
                                    ) is
    v_count_sql  varchar2(2000);
    v_select_sql varchar2(2000);
    l_rowcount   integer; --总条数,输出参数
    l_pagecount  integer; --总页数
begin
    --查询总条数
    v_count_sql := 'select count(1) from ' || i_tablename;
    --连接查询条件(''也属于is null)
    if i_where is not null then
        v_count_sql := v_count_sql || ' where ' || i_where;
    end if;
    --执行查询,查询总条数
    execute immediate v_count_sql
        into l_rowcount;

    --得到总页数
    if mod(l_rowcount, i_pagesize) = 0 then
        l_pagecount := round(l_rowcount / i_pagesize);
    else
        l_pagecount := round(l_rowcount / i_pagesize) + 1;