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

来源：互联网发布：jquery 同步加载js 编辑：程序博客网时间：2024/05/20 23:37

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

--编写分页存储过程

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

create package fenye_package istype 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)isv_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;

0 0