--分页(效果)存储过程,游标的应用,表变量的使用;学习之用
--建表
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条记录的形式调用
分页(效果)存储过程,游标的应用
最新推荐文章于 2021-04-04 16:31:59 发布