分页

create or replace procedure fenye  ( 
pageSize in  number,   
pageCurrent  in  number, 
sumRows  out number, 
pageCount out number, 
fenye_cursor out sys_refcursor
)
is

v_sql varchar2(1000);
v_begin number := (pageNow-1)*page_size+1;
v_end number := pageNow*page_size;

begin  
v_sql :='select count(*) from tpg_ryxx';
execute immediate v_sql into myrows;
if mod(myrows,page_size) =0
  then   myPageCount := myrows/page_size;
else myPageCount := ceil(myrows/page_size);
v_sql := 'select ryxm from (select t1.*,rownum rn from (select * from  tpg_ryxx order by cjsj )t1 where rownum<='||v_end||' )where rn>='||v_begin;
open my_cursor for v_sql;
end if;

end;

--psql中调用


declare 
v_sumRows   number;
v_pageCount number;
fenye_cursor sys_refcursor ;
v_row varchar2(100);
begin
  fenye1(60,2,v_sumRows,v_pageCount,fenye_cursor);
  fetch fenye_cursor into v_row;
  while fenye_cursor%found loop
    dbms_output.put_line(v_row);
  fetch fenye_cursor into v_row;
    end loop;
   close fenye_cursor;
   dbms_output.put_line('共 '|| v_sumRows ||' 条' || '  共 '|| v_pageCount  ||' 页');
  end;