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 outnumber,--总记录数 

 total_page_num out number, --总页数

 result_info_cursor outfenye_cursor_package.fenye_cursor_type) is

 

 

 v_sql varchar2(1000);--定义sql

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

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

 

 begin 

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

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

     openresult_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;

     

     ifmod(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;

     endif;

 

 end;