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