Oracle PL/SQL 实现分页

分页

–得到总记录数，总页数，记录

create or replace procedure page_pro(v_perPage number,v_page number) is

v_count number(5);–总记录数
v_pages number(3,1);–总页数
type emp_cursor is ref cursor;
v_emp_cursor emp_cursor;
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;

begin
–计算总记录数
select count(*) into v_count from emp;
–计算总页数
v_pages := trunc(v_count/v_perPage);
if mod(v_count,v_perPage) > 0 then
v_pages := v_pages + 1;
end if;
dbms_output.put_line(‘总记录数：’ || v_count || ‘,每页显示记录数：’ || v_perPage ||’,总页数：’ || v_pages);

loop
fetch v_emp_cursor into v_empno,v_ename,v_job;
exit when v_emp_cursor%notfound;
dbms_output.put_line(v_empno||’,’||v_ename||’,’||v_job);
end loop;
CLOSE v_emp_cursor;

end;
/

–分页,得到总记录数，总页数，记录

创建包，定义游标，游标用于当做参数传递

create package page_package is
type emp_cursor is ref cursor;
end;

创建分页过程

create or replace procedure page_pro(v_tableName varchar2,v_perPage number,v_page number,v_orderName varchar2,v_order varchar2,v_count out number,v_pages out number, v_emp_cursor out page_package.emp_cursor) is

v_sql varchar2(255);

begin
–计算总记录数
v_sql := ‘select count(*) from ’ || v_tableName;
execute immediate v_sql into v_count;

–计算总页数
v_pages := trunc(v_count/v_perPage);
if mod(v_count,v_perPage) > 0 then
v_pages := v_pages + 1;
end if;

v_sql := ‘select * from(
select rownum rn,temp1.* from(
select * from ’ || v_tableName || ’ order by ’ || v_orderName || ’ ’ || v_order || ’
) temp1 where rownum <= ’ || (v_page*v_perPage) ||’
) temp2 where rn > ’ || (v_perPage*(v_page-1));

open v_emp_cursor for v_sql;
end;
/