通用的分页就不必了 各位大神进来看看
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);BEGINIF 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); EXECUTE IMMEDIATE sqlstr;END
------解决方案--------------------
- 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);BEGINIF strwhere is null 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 :=ceil(tolrecord*1.0/pagesize);END IF; IF strwhere is null THEN sqlstr :='select top '+'pagesize'+' '+returnColunm+' from ' || tabname || ' where policycode not in(select top ' || (pageindex-1)*pagesize || ' policycode from ' || tabname ||' order by policycode asc) order by ' ||orderfield || ' ' ||ordertype;ELSE sqlstr :='select top ' || pagesize || ' ' || returnColunm || ' from ' || tabname || ' where ('+strwhere+') and (policycode not in(select top ' || (pageindex-1)*pagesize || ' policycode from ' || tabname || ' order by policycode asc)) order by ' || orderfield || ' ' || ordertype;END IF; DBMS_OUTPUT.PUT_LINE(sqlstr); EXECUTE IMMEDIATE sqlstr;END;