一个使用oracle存储过程分页的小例子

1， 建一个程序包。如下：

CREATE OR REPLACE PACKAGE TESTPACKAGE AS

TYPE Test_CURSOR IS REF CURSOR;

end TESTPACKAGE;

2，建立存储过程，存储过程为：

create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR,lowerNum in numeric,higherNum in numeric) is
begin
OPEN p_CURSOR FOR select * from ( select row_.*, rownum rownum_ from (select id,title,status from HYQTEST) row_ where rownum <= higherNum) where rownum_ >lowerNum;
end TESTC;

使用plsql测试：
declare
lowerNum integer;
higherNum integer;
id varchar2(10);
title varchar2(500);
status numeric;
c testpackage.Test_CURSOR;
rownum_ integer;
begin
lowerNum:=1;
higherNum:=10;
TESTC(c,lowerNum,higherNum);
LOOP
FETCH c INTO id,title,status,rownum_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('=行号='||rownum_||'=='||id||'==='||title||'===='||status||'==');
END LOOP;
CLOSE c;

end;