Oracle存储过程分页

转载
from: http://cid-4e5d038451e31a25.spaces.live.com/blog/cns!4E5D038451E31A25!140.entry
create or replace procedure P_QuerySplit(
      sqlscript varchar2,     --表名/SQL语句
      pageSize integer,       --每页记录数
      pageIndex integer,      --当前页
      totalCount out number, --总记录数
      totalPage out number, --总页数
      v_cur out sys_refcursor --返回游标
      ) 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;
--统计记录数量
v_SQL_Count := 'select count(*) from ('|| sqlscript ||') a ';
execute immediate v_SQL_Count into totalCount;
--计算总页数
totalPage:=CEIL(totalCount/v_PageSize);
--验证页号 如果页号大余了最大页数，返回最后一页
v_PageIndex:=pageIndex;
if v_PageIndex>totalPage then
    v_PageIndex:=totalPage;
end if;
--计算开始的Index和结束的Index
v_StartIndex:=(v_PageIndex-1)*v_PageSize+1;
v_EndIndex:=v_PageIndex*v_PageSize;
v_SQL:='SELECT /*+ FIRST_ROWS */* from (';
v_SQL:=v_SQL||' SELECT A.*, ROWNUM RN ';
v_SQL:=v_SQL||' from ('||sqlscript||') A ';
v_SQL:=v_SQL||' WHERE ROWNUM <= '||v_EndIndex;
v_SQL:=v_SQL||')WHERE RN >= '||v_StartIndex;
open v_cur for v_SQL;
end P_QuerySplit;
java代码：　
public List listSplit(StringBuffer sbSQL,SplitPageInfo splitPageInfo)throws Exception{
      try {
          java.sql.CallableStatement proc = this.con.prepareCall(
    &n