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;
oracle海量数据分页存储过程
最新推荐文章于 2023-03-29 17:05:33 发布