oracle中使用存储过程实现分页

                                                                  oracle中使用存储过程实现分页

--编写分页存储过程

--（要求可以输入表名称，每页显示的记录数，当前页，返回总记录数，总页数，和结果集）
--1定义一个包在包中定义游标存储结果集。

create package fenye_package is
type ref_cursor is ref cursor;
--type fenye_record is record(current_page number,result_set ref_cursor);
end;

--存储过程实现分页
create or replace procedure fenye_procedure(table_name in varchar2,page_record_num in number,current_page in number,
all_record_num out number,all_page out number,result_set out fenye_package.ref_cursor)is
v_temp1 number(5,1);  
v_temp2 number(5,1);
v_begin_record number(5);  
v_end_record number(5); 
v_sql varchar2(1000);             
begin
  v_sql:='select count(*) from '|| table_name;
  execute immediate v_sql into all_record_num;
  v_temp1:=all_record_num/page_record_num;
  v_temp2:=mod(all_record_num,page_record_num);
  if v_temp2<>0 then
    all_page:=v_temp1+1;
    else all_page:=v_temp1;
  end if;
  
    v_begin_record:=(current_page-1)*page_record_num+1;
    v_end_record:=v_begin_record+page_record_num-1;
    if current_page=all_page then
        v_end_record:=all_record_num;
    else v_end_record:=v_begin_record+page_record_num-1;
    end if;
  v_sql:='select * from (select a1.*,rownum rn from (select *from '||table_name||') a1 where rownum<='||v_end_record||') where rn>='||v_begin_record;
  open result_set for v_sql;
end;