Paging processing of oracle stored procedures

Oracle Stored Procedure paging processing knowledge point link 1. oracle Paging

2. Use cursor to create a stored procedure [SQL] -- create a stored procedure (startpoint start point, number of getnum retrieved) create or replace procedure copylimit (startpoint number, getnum number) as/* Define table */type emp_tab is table of myemp % rowtype; emp_rd emp_tab; www.2cto.com/* define cursor */emp_cur sys_refcursor; -- enable begin -- display the start point of user input and the data volume to be retrieved dbms_output.put_line ('start point: '| startpoint | 'fetch quantity:' | getnum ); -- open the cursor and assign a value to the cursor (execute paging query) open emp_cur for select * from myemp where rowid in (select rd from (select rownum rm, rd from (select rowid rd from myemp order by empno) where rownum <startpoint + getnum) where rm> = startpoint ); -- enable loop -- assign the value bulk collect to emp_rd fetch emp_cur bulk collect into emp_rd limit 2 from the cursor; -- loop traversal for I in 1 .. emp_rd.count loop dbms_output.put_line ('employee name: '| emp_rd (I ). ename); end loop; exit when emp_cur % notfound; -- end loop; -- close the cursor close emp_cur; -- end; run the stored procedure [SQL] SQL> exec copylimt (2, 4); www.2cto.com start point: 2 fetch quantity: 4 employee name: ALLEN employee name: WARD employee name: JONES employee name: martin pl/SQL procedure successfully completed Executed in 0 seconds