分页（效果）存储过程，游标的应用

--分页（效果）存储过程，游标的应用，表变量的使用；学习之用

--建表
create table student
(
	sno varchar(10) primary key, 
	sname nvarchar(10), 
	sex char(2), 
	age int, 
	dept nvarchar(10)
)
--插入数据
insert into student values('1001','张三','男',20,'计算机')
insert into student values('1002','李四','男',18,'数学')
insert into student values('1003','赵六','女',19,'计算机')
insert into student values('1004','里斯','男',20, '数学')
insert into student values('1005','张三三','男',20,'计算机')
insert into student values('1006','李四四','男',18,'数学')
insert into student values('1007','赵六六','女',19,'计算机')
insert into student values('1008','里斯四','男',20, '数学')
insert into student values('1009','里斯五','男',20, '数学')

go
--建立分页(效果)的存储过程第二版,@tn表名,@cnt每次显示的记录数
create proc SplitPages;2(@tn nvarchar(30)='student', @cnt int=3) as
begin
	--声明游标
	declare curSelect cursor for 
		select sno, sname, sex, age, dept from student
	
	--声明变量
	declare @i int, @sno varchar(10), @sname nvarchar(10)
	declare @sex char(2),@age int, @dept nvarchar(10)

	declare @t table(--声明表变量
		sno varchar(10), 
		sname nvarchar(10), 
		sex char(2), 
		age int, 
		dept nvarchar(10))

	open curSelect --打开游标
	--从游标提取到变量中
	fetch next from curSelect into @sno, @sname, @sex, @age, @dept
	set @i=0

	while @@fetch_status=0--能提取到数据
	begin
		set nocount on
		insert into @t values(@sno, @sname, @sex, @age, @dept)--插入结果到表变量
		set @i=@i+1
		if @i % @cnt = 0 --够@cnt个后输出
		begin
			select * from @t
			delete from @t --清空表变量,为下次存放结果作准备
		end
		fetch next from curSelect into @sno, @sname, @sex, @age, @dept
	end
	if exists(select * from @t) select * from @t --不够@cnt的剩余记录输出

	close curSelect --关闭游标
	deallocate curSelect --释放游标
end
go

exec SplitPages;2 --以每次默认3条记录的形式调用
exec SplitPages;2 'student', 4 --以每次4条记录的形式调用