14 PL/SQL游标与分页

create or replace procedure my_pro5(v_deptno number) is
type my_cursor is ref cursor;
v_cursor my_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open v_cursor for select ename,sal from emp where deptno=v_deptno;
  loop
    fetch v_cursor into v_ename,v_sal;/***游标向下移动***/
    exit when v_cursor%notfound;
    dbms_output.put_line('雇员'||v_ename||',薪水'||v_sal);
  end loop;
  close v_cursor;
end;

create or replace package pack1 is
type my_cursor is ref cursor;
end;

create or replace procedure fengyePro
(v_table in varchar2,v_pagesize in number,v_pagenow in number,
 v_result out pack1.my_cursor,v_pagerows out number,v_pagecount out number) is
v_sql varchar2(200);
v_start number;
v_end number;
begin
  v_start:=v_pagesize*(v_pagenow-1)+1;
  v_end:=v_pagesize*v_pagenow;
  v_sql:='select t2.* from (select t1.*,rownum rn from (select *from'
         ||v_table||') t1 where rownum<='||v_end||' t2 where rn>='||v_start;
  open v_result for v_sql;
  select count(*) into v_pagerows from emp;
  if mod(v_pagerows,v_pagesize)=0 then
    v_pagecount:=v_pagerows/v_pagesize;
  else
    v_pagecount:=v_pagerows/v_pagesize+1;
  end if;
end;