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 --总页数
)
asBEGIN
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+' '+@ordertypeelse
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;
(
@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 --总页数
)
asBEGIN
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+' '+@ordertypeelse
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;
<an identifier>
<a double-quoted delimited-identifier> current
行:4
文本:@pageindex int:=1,--页索引
sorry 刚刚有事出了 电脑卡了下 不好意思各位 本人菜鸟
CREATE OR REPLACE PROCEDURE CaseMessagePager
(
pagesize int;--页大小
pageindex int:=1;--页索引
orderfield in varchar2(64);--排序字段
ordertype in varchar2(64);--排序类型
tabname in varchar2(64);--表名
strwhere in varchar2(64);--条件
returnColunm in varchar2(64);--返回的列
tolrecord out number;--总记录
tolpage out number; --总页数
)
as
*/
declare
--in
tabname varchar2(64) := 'hi_dataValue';
returnColunm varchar2(64) := '列名';
pageindex int := 1;
pagesize int := 30;
orderfield varchar2(64) := '排序字段';
ordertype varchar2(32) := '排序方式';
strwhere varchar2(100) := ' indcode like ''I000002%''';
--out
tolrecord varchar2(32) := 0;
tolpage varchar(32) := 1;
--代码中使用的
firstsql varchar2(500) := '';
strSql varchar2(1000) := '';
BEGIN
if (strwhere='') then
firstsql :='select count(1) from ' || tabname;
else
firstsql :='select count(1) from ' || tabname || ' where ' || strwhere;
end if;
execute immediate firstsql into tolrecord;
dbms_output.put_line('总行数:' || to_char(tolrecord));
--总页数
firstsql := 'select ceil(' || tolrecord || '/' || pagesize || ') from dual';
execute immediate firstsql into tolpage;
dbms_output.put_line('总页数:' || to_char(tolpage)); if(strwhere='') then
strSql := 'select top ' || to_char(pagesize) || ' ' || returnColunm || ' from '
|| tabname || ' where policycode not in ( select top ' || to_char((pageindex-1)*pagesize)
|| ' policycode from ' || tabname || ' order by policycode asc) order by ' || orderfield || ' ' || ordertype;
else
strSql := 'select top ' || to_char(pagesize) || ' ' || returnColunm || ' from '
|| tabname || ' where (' || strwhere || ') and (policycode not in(select top ' || to_char((pageindex-1)*pagesize)
|| ' policycode from ' || tabname || ' order by policycode asc)) order by ' || orderfield || ' ' || ordertype;
end if;
dbms_output.put_line(strSql);
--exec(@sql)
end;
--END CaseMessagePager;
测试结果:
总行数:450223
总页数:15008
select top 30 列名
from hi_dataValue
where (indcode like 'I000002%')
and (policycode not in
(select top 0 policycode from hi_dataValue order by policycode asc))
order by 排序字段 排序方式