oracle 分页存储过程

 create or replace package MyPackage as 
type MyCursor is ref cursor;
procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  resultCount out int, resultCursor out MyCursor);
end MyPackage;

create or replace package Body MyPackage is
procedure SelectBase(pageIndex int,pageSize int,tableName varchar2,whereStr varchar2,
  resultCount out int, resultCursor out MyCursor)
  is
  --定义变量
  newtableName varchar2(4000);
  rowStart  int;
  rowEnd    int;
  mySql varchar2(8000);
  whereOnly varchar2(8000);
  OrderOnly varchar2(400);
  begin
    newtableName:=tableName;
    mySql:='select count(*) from '||tableName;

    
    if whereStr is not null and length(whereStr)>0
      then
          rowStart:=instr(whereStr,'order by');
         if rowStart>0 
          then
            whereOnly:=substr(whereStr, 1,rowStart-1);    --取得条件 
            OrderOnly:=substr(whereStr,rowStart, length(whereStr)-rowStart+1);    --取得排序方式(order by 字段 方式) 
          else
            whereOnly:=whereStr;
            OrderOnly:='';
            end if;
           whereOnly:=' where '|| whereOnly;
           mySql:=mySql||whereOnly;
         
     end if;
     execute immediate mySql into resultCount;
       -- dbms_output.put_line('查询总条数SQL=>'||whereStr||'--'||mySql||resultCount); 
    --执行查询,查询总条数 
           


            --不分页查所有
          
        if pageIndex=0 and pageSize=0    
        then 
        mySql:='select * from '||tableName||whereOnly||OrderOnly;
       else
--计算起始和结束索引

        rowStart:=(pageIndex-1)*pageSize+1; 
        rowEnd:=rowStart+pageSize-1;
        mySql:='select * from (select t.*,RowNum as rn from (select * from '||newtableName||whereOnly||OrderOnly||') t) where rn between '||rowStart||' and '||rowEnd;
      
        end if;
    open ResultCursor for mySql;
   --dbms_output.put_line('SQL=>'||mySql); 
    end SelectBase;
  end MyPackage;

 

 

    如果各位遇到了在create or replace package Body MyPackage is  说create错误，那么解决方法是。

end MyPackage;

在此，加上“/”并分别执行就搞定了。

create or replace package Body MyPackage is