首先要通过包创建一个游标,代码如下:
create or replace package mypackage as
type mycursor_cursor is ref cursor;
end mypackage;
其次才是编写分页的存储过程,代码如下:
create or replace procedure myproc(
tableName in varchar2,
pageSize in number,
pageNow in number,
myRows out number,
myPageCount out number,
myc_cursor out mypackage.mycursor_cursor) is
v_sql_str varchar2(1000);
v_begin_page number :=(pageNow-1)*pageSize+1;
v_end_page number := pageNow*pageSize;
begin
v_sql_str :='select * from (select t1.*,rownum rn from (select * from '|| tableName ||') t1 where rownum<='||v_end_page||') where rn>='||v_begin_page;
open myc_cursor for v_sql_str;
v_sql_str:= 'select count(*) from '|| tableName;
execute immediate v_sql_str into myRows;
if mod(myRows,pageSize)=0 then
myPageCount:= myRows/pageSize;
else
myPageCount:= myRows/pageSize+1;
end if;
end myproc;
分页存储过程的编写代码
最新推荐文章于 2024-09-05 18:50:50 发布