oracle分页的存储过程:
create or replace procedure fenye(
tableName in varcahr2,
pageSize in number,
pageNow in number,
myrows out number,
myPageCount out number,
p_cursor out testPackage.test_cursor
) is
-定义部分
- 定义sql语句,字符串
v_sql varchar2(1000);
- 定义两个整数
v_begin number := (pageNow - 1)pageSize + 1;
v_end number := pageNow*pageSize;
begin
-执行部分
v_sql := 'select * from (select t., rownum rn from (select * from ‘|| tableName ||’ order by sal asc) t where rownum <= ‘|| v_end ||’) where rn >= ‘|| v_begin ||’’;
- 把游标和sql关联
open p_cursor for v_sql;
- 计算myrows和myPageCount
- 组织一个sql
v_sql := 'select count(*) from ’ || tableName;
- 执行sql,并把返回的值,赋给myrows
execute immediate v_sql into myrows;
- 计算myPageCount
if mod(myrows, pageSize) = 0 then
myPageCount := myrows / pageSize;
else
myPageCount := myrows / pageSize + 1;
end if;
end;