oracle分页 同事让我改 没看懂 高手们请进 给改下
通用的分页就不必了 各位大神进来看看
  CREATE OR REPLACE PROCEDURE CaseMessagePager
(
pagesize int,--页大小
pageindex int:=1,--页索引
orderfield in varchar2,--排序字段
ordertype in varchar2,--排序类型
tabname in varchar2,--表名
strwhere in varchar2,--条件
returnColunm in varchar2,--返回的列
tolrecord out number,--总记录
tolpage out number --总页数
)


as

BEGIN
declare firstsql nvarchar(500)
if(strwhere='')
set firstsql='select mytolrecord=count(*) from '+tabname
else
set firstsql='select mytolrecord=count(*) from '+tabname+' where '+strwhere
exec sp_executesql firstsql,N'mytolrecord int output', tolrecord output
set tolpage=ceiling(tolrecord*1.0/pagesize)
declare sql varchar(1000)
if(strwhere='')
set sql='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from '
+tabname+' where policycode not in(select top '+convert(varchar(10),(pageindex-1)*pagesize)
  +' policycode from '+tabname+' order by policycode asc) order by '+orderfield+' '+ordertype

else
set sql='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from '
+tabname+' where ('+strwhere+') and (policycode not in(select top '+convert(varchar(10),(pageindex-1)*pagesize)
  +' policycode from '+tabname+' order by policycode asc)) order by '+orderfield+' '+ordertype
print(sql)
exec(sql)
END CaseMessagePager;



PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误

错误：PLS-00103: 出现符号 "IF"在需要下列之一时：
  := ; not null default
  character
行：19
文本：if(strwhere='')

错误：PLS-00103: 出现符号 "SET"在需要下列之一时：
  *


PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误

错误：PLS-00103: 出现符号 "IF"在需要下列之一时：
  := ; not null default
  character
行：19
文本：if(strwhere='')

错误：PLS-00103: 出现符号 "SET"在需要下列之一时：
  *




PROCEDURE ZXDRB.CASEMESSAGEPAGER 编译错误

错误：PLS-00103: 出现符号 "IF"在需要下列之一时：
  := ; not null default
  character
行：19
文本：if(strwhere='')

错误：PLS-00103: 出现符号 "SET"在需要下列之一时：
  *



------解决方案--------------------
实测编译通过：

SQL code

CREATE OR REPLACE PROCEDURE CaseMessagePager
(
    pagesize INTEGER, --页大小
    pageindex INTEGER :=1,--页索引
    orderfield IN VARCHAR2,--排序字段
    ordertype IN VARCHAR2,--排序类型
    tabname IN VARCHAR2,--表名
    strwhere IN VARCHAR2,--条件
    returnColunm IN VARCHAR2,--返回的列
    tolrecord OUT NUMBER,--总记录
    tolpage OUT NUMBER --总页数
)
AS
    firstsql NVARCHAR2(500);
    sqlstr VARCHAR2(1000);
BEGIN

IF strwhere = '' THEN
    firstsql :='select mytolrecord=count(*) from ' || tabname;
ELSE 
    firstsql :='select mytolrecord=count(*) from ' || tabname || ' where ' || strwhere;
--    exec sp_executesql firstsql,N'mytolrecord int output', tolrecord output
    tolpage :=ceiling(tolrecord*1.0/pagesize);
END IF; 
   
IF strwhere='' THEN
    sqlstr :='select top '+convert(varchar(10),pagesize)+' '+returnColunm+' from ' ||
        tabname || ' where policycode not in(select top ' || convert(varchar(10),(pageindex-1)*pagesize)
        || ' policycode from ' || tabname ||' order by policycode asc) order by ' 
        ||orderfield || ' ' ||ordertype;

ELSE
    sqlstr :='select top ' || convert(varchar(10),pagesize) || ' ' || returnColunm || ' from '
        || tabname || ' where ('+strwhere+') and (policycode not in(select top '
        || convert(varchar(10),(pageindex-1)*pagesize)
        || ' policycode from ' || tabname || ' order by policycode asc)) order by '
        || orderfield || ' ' || ordertype;
END IF;        
    DBMS_OUTPUT.PUT_LINE(sqlstr);