oracle 分页存储过程

--package 定义cursor

create or replace package fenye_cursor_package as

type fenye_cursor_type is ref cursor;

end fenye_cursor_package;

--建立分页存储过程

create or replace procedure pro_fenye 

(tabname in varchar, 

  record_num in number,--每页显示行数

  cur_page in number,   --当前页

  record_total_num out number,--总记录数 

  total_page_num out number, --总页数

  result_info_cursor out fenye_cursor_package.fenye_cursor_type) is

 

 

  v_sql varchar2(1000);--定义sql

  v_begin_num number:=(cur_page-1)*record_total_num; -- 定义起始数

  v_end_num number:= cur_page*record_total_num+1;--定义结束数

 

  begin 

      v_sql:= 'select * from (select fenye_1.*,rownum rn from   (select * from '||tabname

      ||') fenye_1   where rownum<='||v_end_num||') where rn>='||v_end_num;

      open result_info_cursor for v_sql;

      execute immediate v_sql into result_info_cursor;    

      v_sql:= 'select count(*) from '||tabname;

      execute immediate v_sql into   record_total_num;

     

      if mod(record_total_num,record_num)=0 then

          total_page_num:=   record_total_num/record_num;

      else

          total_page_num:=   record_total_num/record_num+1;

      end if;

 

  end;