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;
别沉啊 顶起啊....问题决解 见者有分
