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;
    end if;

    open o_cur_count for
        Select l_rowcount row_count, l_pagecount page_count from dual;

    --如果查询记录大于0则查询结果集
    if l_rowcount > 0 and i_curpage >= 1 and i_curpage <= l_pagecount then
        --查询所有(只有一页)
        if l_rowcount <= i_pagesize then
            v_select_sql := 'select ' || i_tablecolumn || ' from ' ||
                            i_tablename;
            if i_where is not null then
                v_select_sql := v_select_sql || ' where ' || i_where;
            end if;
            if i_order is not null then
                v_select_sql := v_select_sql || ' order by ' || i_order;
            end if;
            open o_cur_data for v_select_sql;
        elsif i_curpage = 1 then
            --查询第一页
            v_select_sql := 'select ' || i_tablecolumn || ' from ' ||
                            i_tablename;
            if i_where is not null then
                v_select_sql := v_select_sql || ' where ' || i_where ||
                                ' and rownum<=:page_size';
            else
                v_select_sql := v_select_sql || ' where rownum<=:page_size';
            end if;
        
            if i_order is not null then
                v_select_sql := v_select_sql || ' order by ' || i_order;
            end if;
            open o_cur_data for v_select_sql using i_pagesize;
        else
            --查询指定页
            v_select_sql := ' select ' || i_tablecolumn || ' from ' ||
                            i_tablename;
        
            if i_where is not null then
                v_select_sql := v_select_sql || ' where ' || i_where;
            end if;
        
            if i_order is not null then
                v_select_sql := v_select_sql || ' order by ' || i_order;
            end if;
        
            v_select_sql := 'select /*+ first_rows(' || i_pagesize ||
                            ') */ * from ' || '(select a.*, rownum rn ' ||
                            '   from (' || v_select_sql || ') a ' ||
                            '   where rownum <= :end_row) ' ||
                            ' where rn>=:start_row';
            open o_cur_data for v_select_sql
                using i_curpage * i_pagesize,((i_curpage - 1) * i_pagesize) + 1;
        end if;
    else
        open o_cur_data for 'select ' || i_tablecolumn || ' from ' || i_tablename || ' where 1!=1';
    end if;

end sp_page;