关于oracle分页存储过程---在线结贴
下面是一个oracle分页存储过程，执行单表分页是没什么问题，在多表进行联合查询时，当有2页数据时，第一页的最后几条会和第二页的开始几条重复；需要怎么改进，用的是rownum方式分页。
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 number, --每页大小
i_curpage in number, --当前页
i_where in varchar2, --查询条件e.ename like '%S%'
o_cur_count out sys_refcursor, --行数和页数
o_cur_data 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;
[最优解释]
顶哈，你调试下撒。
[其他解释]
v_select_sql := 'select /*+ first_rows('
[其他解释]
'(select a.*, rownum rn '
[其他解释]
好漂亮的过程。。。
[其他解释]
i_pagesize
[其他解释]
v_select_sql
[其他解释]
别沉啊 顶起啊....问题决解 见者有分
[其他解释]
问题决解后 见者有分
[其他解释]
---------------------------
过程的意思就是代码写的太罗嗦了，根本都没法阅读，估计最后一句有问题
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
