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