前端之家收集整理的这篇文章主要介绍了
oracle创建一个分页过程，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
select *
from
(select e.*,rownum rn
from emp e
where rownum<=10
) t
where t.rn>=6;
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/
create or replace procedure fenye
(
tableName in varchar2,--表明
pagesize in number,--每页的记录数
pageNow in number,--准备到第几页
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回记录集
) is
v_sql varchar2(1000);
startNo number:=pageNow*pagesize-pagesize+1;
endNo number:=pagesize*pageNow;
begin
--dbms_output.put_line(startNo||' '||endNo);
v_sql:='select * from (select e.*,rownum rn from '||tableName||' e where rownum<='||endNo||') t where t.rn>='||startNo;
open p_cursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myrows;
if mod(myrows,pagesize)=0 then
myPageCount := myrows/pagesize;
else
myPageCount := myrows/pagesize+1;
end if;
--close p_cursor;
end;
/
declare
myrows number(4);
mycount number(4);
pcoursor testpackage.test_cursor;
begin
--my_pro('emp',10,6);
fenye('emp',2,3,myrows,mycount,pcoursor);
end;
/
create or replace procedure my_pro(
tableName in varchar2,endNo in number,startNo in number
) is
pc testpackage.test_cursor;
v_sql varchar2(1000);
begin
v_sql:='select * from (select e.*,rownum rn from '||tableName||' e where rownum<='||endNo||') t where t.rn>='||startNo;
--v_sql:='select e.* from emp e';
open pc for v_sql;
end;
/
