分页
–得到总记录数,总页数,记录
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;
/