http://zzx0421.javaeye.com/blog/281015
Oracle 分頁(yè)：
create or replace procedure P_QuerySplit(
sqlscript varchar2, 表名/SQL語(yǔ)句
pageSize integer, 每頁(yè)記錄數(shù)
pageIndex integer, 當(dāng)前頁(yè)
totalCount out number, 總記錄數(shù)
totalPage out number, 總頁(yè)數(shù)
v_cur out sys_refcursor 返回游標(biāo)
- ) is
- /**
- * by chenjianxin 2008-5-3
- *
- */
- v_PageSize number;
- v_PageIndex number;
- v_SQL_Count varchar2(4000);
- v_SQL varchar2(4000);
- v_StartIndex number;
- v_EndIndex number;
- begin
- v_PageSize:=pageSize;
- if v_PageSize=0 then
- v_PageSize:=1;
- end if;
ibatis調(diào)用Oracle分頁(yè)存儲(chǔ)過(guò)程中需要統(tǒng)計(jì)記錄數(shù)量
- v_SQL_Count := 'select count(*) from (' ? ? sqlscript ? ?') a ';
- execute immediate v_SQL_Count into totalCount;
計(jì)算總頁(yè)數(shù)
- totalPage:=CEIL(totalCount/v_PageSize);
驗(yàn)證頁(yè)號(hào) 如果頁(yè)號(hào)大余了最大頁(yè)數(shù)，返回最后一頁(yè)
- v_PageIndex:=pageIndex;
- if v_PageIndex>totalPage then
- v_PageIndex:=totalPage;
- end if;
計(jì)算開(kāi)始的Index和結(jié)束的Index
- v_StartIndex:=(v_PageIndex-1)*v_PageSize 1;
- v_EndIndex:=v_PageIndex*v_PageSize;
- v_SQL:='SELECT /* FIRST_ROWS */* FROM (';
- v_SQLv_SQL:=v_SQL ? ?' SELECT A.*, ROWNUM RN ';
- v_SQLv_SQL:=v_SQL ? ?' FROM (' ? ?sqlscript ? ?') A ';
- v_SQLv_SQL:=v_SQL ? ?' WHERE ROWNUM <= ' ? ?v_EndIndex;
- v_SQLv_SQL:=v_SQL ? ?')WHERE RN >= ' ? ?v_StartIndex;
- open v_cur for v_SQL;
- end P_QuerySplit;
create or replace procedure PageInation(
p_CURSOR out TESTPACKAGE.Test_CURSOR,
tableName in varchar2,
tableResult in varchar2,
lowerNum in numeric,
higherNum in numeric
) is
sqls varchar2(2000);
begin
sqls :='select * from ( select rownum rownum_,'||tableResult||' from ('||tableName||') row_ where rownum <=' ||higherNum||') where rownum_ >'||lowerNum;
OPEN p_CURSOR FOR sqls;
end PageInation;