create procedure pages
@p_pagesize numeric(5),
@p_curpage numeric(5)
as
declare @pageSize int --返回一页的记录数
declare @curPage int--页号(第几页)1:第一页;2:第二页;......;-1最后一页。
declare @count int
declare @id varchar(6)
declare @pages int --总页数
set @pagesize=@p_pagesize
set @curpage=@p_curpage select @count=count(*) from loc_mst
if @count%@pagesize=0
begin
set @pages=@count/@pagesize
set @count=@count-@pagesize+1
end
else
begin
set @pages=(@count/@pagesize)+1
set @count=(@count/@pagesize)*@pagesize+1
end
--最后一页
if @curpage>=@pages or @curpage=-1
begin
set rowcount @count
select @id=loc from loc_mst order by loc asc
end--定位
if @curpage>0 and @curpage<@pages
set @count=@pagesize*(@curpage-1)+1set rowcount @count
select @id=loc from loc_mst order by loc asc
--返回记录
set rowcount @pagesize
select * from loc_mst where loc>=@id order by loc asc
set rowcount 0GO
@p_pagesize numeric(5),
@p_curpage numeric(5)
as
declare @pageSize int --返回一页的记录数
declare @curPage int--页号(第几页)1:第一页;2:第二页;......;-1最后一页。
declare @count int
declare @id varchar(6)
declare @pages int --总页数
set @pagesize=@p_pagesize
set @curpage=@p_curpage select @count=count(*) from loc_mst
if @count%@pagesize=0
begin
set @pages=@count/@pagesize
set @count=@count-@pagesize+1
end
else
begin
set @pages=(@count/@pagesize)+1
set @count=(@count/@pagesize)*@pagesize+1
end
--最后一页
if @curpage>=@pages or @curpage=-1
begin
set rowcount @count
select @id=loc from loc_mst order by loc asc
end--定位
if @curpage>0 and @curpage<@pages
set @count=@pagesize*(@curpage-1)+1set rowcount @count
select @id=loc from loc_mst order by loc asc
--返回记录
set rowcount @pagesize
select * from loc_mst where loc>=@id order by loc asc
set rowcount 0GO
(
p_pagesize number,
p_curpage number,
c_ret out ref cursor
)
as
pageSize number; --返回一页的记录数
curPage number; --页号(第几页)1:第一页;2:第二页;......;-1最后一页。
count number;
id varchar2(6);
pages number; --总页数
begin
pagesize:=p_pagesize;
curpage:=p_curpage; select count(*) into count from loc_mst;
if mod(count,pagesize)=0 then
pages:=count/pagesize;
count:=count-pagesize+1;
else
pages:=(count/pagesize)+1;
count:=(count/pagesize)*pagesize+1;
end if;
--最后一页
if curpage>=pages or curpage=-1 then
select loc into id from loc_mst where rownum=count order by loc asc;
end if; --定位
if curpage>0 and curpage<pages then
count:=pagesize*(curpage-1)+1;
end if;
select loc into id from loc_mst where rownum=count order by loc asc;
--返回记录
open c_ret for
select * from loc_mst where loc>=@id and rownum<=pagesize order by loc asc;
end pages;
/
--返回记录
open c_ret for
select * from loc_mst where loc>=id and rownum<=pagesize order by loc asc;
你这么写有问题,我也这样写过有问题,你的我也试过确实不好用!
主要问题是如:
set rowcount @count //返回前@count 行,=select top 4 * from loc_mst;
select @id=loc from loc_mst order by loc asc
在t_sql中这样写是 返回前 @count 记录集合中的最后一个记录给@id(@id=loc);
sorry! 是我错了!