Oracle 分頁步驟

--Oracle分頁
定義好的遊標

create or replace package chj1_package as
type chji1_cursor is ref cursor;
end chj1_package;

--一步
select t1.*,rownum from (select * from chengji) t1
--二步
select t1.*,rownum rn from (select * from chengji) t1 where rownum <=2
--三步
select * from
(select t1.*,rownum rn from (select * from chengji) t1 where rownum <=2)
where rn>1
--建立一個預存程序
--輸入表名 每頁顯示的條數  當前頁
--返回總的條數、頁數、結果集
create or replace procedure proc_fenye
(
tableName in varchar2,
everyPageSize in number,
nowPage in number,
allRowCount out number,--總條數
allPageCount out number,--總頁數
fy_cursor out chj1_package.chji1_cursor--返回集
)
is--定義部分
v_sql varchar2(1000);
v_begin number:=(nowPage-1)*everyPageSize+1;--開始條數
v_end number:=nowPage*everyPageSize;--結束條數

begin--執行部分
v_sql:='select * from
(select t1.*,rownum rn from (select * from '|| tableName ||') t1 where rownum <='|| v_end ||')
where rn>'|| v_begin ||'';
open fy_cursor for v_sql;

--計算allRowCount和allPageCount
v_sql:='select count(*) from '|| tableName ||'';

--執行sql 並把值賦給allRowCount
execute immediate v_sql into allRowCount;

--根據allRowCount的值計算allPageCount總頁數
if mod(allRowCount,everyPageSize)=0 then
allPageCount:=allRowCount/everyPageSize;
else
allPageCount:=allRowCount/everyPageSize+1;
end if;
--close fy_cursor;
end;