sql 存储过程分页查询

第一次遇到存储过程分页弄了挺长时间  主要用到了ROW_NUMBER()over(）方法，此方法自动生成一个num 列，更具over中的列排序，从一开始升序

drop proc getPageshowOptionNumPNum

go
create proc aa
@currPageNum int, --当前页数
@pageSize int, --每页数量
@totalDataSize int output --总信息条数
as
begin
select @totalDataSize=COUNT(VS_ID) from VOTE_SUBJECT
select top (@pageSize) * from (
select *,ROW_NUMBER()over(order by vs.VS_ID) as num from VOTE_SUBJECT  vs)
as temp where  num > @pageSize*(@currPageNum-1)
end
go


declare @a int

exec aa 1,1,@a

oracle分页

SELECT * FROM ( SELECT A.*, ROWNUM RN 
FROM (SELECT * FROM TABLE_NAME) A) WHERE RN BETWEEN 21 AND 40