PL\SQL写的一个分页,跟大家分享。多多指点。
create or replace procedure prefection_page(strSql varchar2,curpage number,pageunit number,ordername varchar2,ordertype number,allrecord out number,records out yong.r_cursor)
as
allSql varchar2(1000);
allrecord_sql varchar2(500);
startpoint number(10);
endpoint number(10);
begin
if(curpage is not null and pageunit is not null)then
startpoint:=(curpage-1)*pageunit+1;
endpoint:=curpage*pageunit;
end if;
-- allrecord_sql:='select count(1) from ('||strSql||')';
-- execute immediate allrecord_sql into allrecord;
-- get_allrecord(strSql,allrecord);--存储过程调用存储过程
-- allrecord:=get_fun_allrecord(strSql);--存储过程调用函数
allrecord:=yong.get_fun_allrecord(strSql);--存储过程调用包中的函数
if(ordername is not null and length(ordername)>0)then
allSql:=strSql||' order by '||ordername;
if(ordertype is not null) then
if(ordertype=1) then
allSql:=allSql||' asc';
elsif
(ordertype=2) then
allSql:=allSql||' desc';
end if;
end if;
end if;
allSql:='select B.* from (select A.*,rownum rid from ('||allSql||') A where rownum<='||endpoint||') B where B.rid>='||startpoint;
open records for allSql;
Exception
when others then
dbms_output.put_line('程序运行出错了!');
raise;
end;