游标
一:什么是游标 游标是可以在结果集中上下游动的指针 二:创建一个简单的游标
use xland
go
--声明变量以后有用
declare @id int
declare @title varchar(max)
declare @username varchar(50)
--定义一个游标并打开它
declare tablecursor cursor for
select a.id,a.title,u.username from mytable a join [user] u on u.id = a.uid
open tablecursor
--开始提取记录，放入指定的变量
fetch next from tablecursor into @id,@title,@username
while @@fetch_status = 0 --0表示提取成功-1找不到记录-2超出了最后一条
begin
--cast(@id as varchar)为了正确的执行输出
print(cast(@id as varchar)+' '+@title+' '+@username)
--提取下一条记录
fetch next from tablecursor into @id,@title,@username
end三:游标的作用域 做个存储过程,
里面的游标是全局的,
存储过程内部没有关闭释放游标 代码如下
use xland
go
create proc spCursorScope
as
declare @id int
declare @title varchar(max)
declare @username varchar(50)
declare @num int
set @num = 1
--定义一个全局游标
declare tablecursor cursor global for
select a.id,a.title,u.username from mytable a join [user] u on u.id = a.uid
open tablecursor
fetch next from tablecursor into @id,@title,@username
while (@num <=3) and (@@fetch_status = 0)
begin
set @num = @num+1
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
fetch next from tablecursor into @id,@title,@username
end
--不关闭游标，不释放内存
--close tablecursor
--deallocate tablecursor接着执行这个存储过程 执行语句如下
use xland
go
exec spcursorscope执行结果 1 2 测试看看 xland 2 4 asdfasdf xland 3 5 asdfasdf xland 4 6 全文索引全文索引全文索引xland的全文索引 xland 再看一种执行方式
use xland
go
exec spcursorscope
declare @id int
declare @title varchar(max)
declare @username varchar(50)
declare @num int
set @num = 5
while (@num <=8) and (@@fetch_status = 0)
begin
set @num = @num+1
fetch next from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
end
--关闭游标，释放内存
close tablecursor
deallocate tablecursor看执行结果 1 2 测试看看 xland 2 4 asdfasdf xland 3 5 asdfasdf xland 4 6 全文索引全文索引全文索引xland的全文索引 xland 6 7 xland xland 7 8 可以吗 xland 8 9 应该没问题 xland 9 10 暗暗 xland 我们在存储过程的外部调用了游标 说明游标是全局的 但不建议这样使用游标
四:游标的滚动 next --移动到下一条记录 prior --移动到上一条记录 first --移动到第一条记录 last --移动到最后一条记录 看例子
use xland
go
declare @id int
declare @title varchar(max)
declare @username varchar(50)
declare @num int
set @num = 1
--定义一个局部的可滚动的游标
declare tablecursor cursor local scroll for
select a.id,a.title,u.username from mytable a join [user] u on u.id = a.uid
open tablecursor
--滚过来
fetch next from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
while (@num <=3) and (@@fetch_status = 0)
begin
set @num = @num+1
fetch next from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
end
--滚回去
while (@num >1) and (@@fetch_status = 0)
begin
set @num = @num-1
fetch prior from tablecursor into @id,@title,@username
print(cast(@num as varchar) +' '+ cast(@id as varchar)+' '+@title+' '+@username)
end
close tablecursor
deallocate tablecursor五:静态游标 static的游标,程序员都知道static的意思,我就不多说了
先看例子
use xland
go
--使用select into创建一个临时表
select id,title into cursortable from mytable where id <6
--定义局部的可滚动的静态的游标
declare cursortest cursor local scroll static for
select id,title from cursortable
declare @id int,@title varchar(max)
open cursortest
fetch next from cursortest into @id ,@title
while @@fetch_status = 0
begin
print (cast(@id as varchar)+' '+@title)
fetch next from cursortest into @id ,@title
end
update mytable set title ='这是我更新的数据' where id = 4
select id,title from mytable where id<6
--滚到第一条
fetch first from cursortest into @id,@title
while @@fetch_status =0
begin
print (cast(@id as varchar)+' '+@title)
fetch next from cursortest into @id,@title
end
close cursortest
deallocate cursortest
drop table cursortable返回的消息: (3 行受影响) 2 测试看看 4 asdfasdf 5 asdfasdf title1
(1 行受影响)
(3 行受影响) 2 测试看看 4 asdfasdf 5 asdfasdf
其中一行受影响就是
select 语句的执行结果,为 2 测试看看 4 这是我更新的数据 5 asdfasdf 静态游标:一旦创建就与实体记录分开了,并不维持任何锁 实体表发生了更新,并不影响游标里的情况 六:键驱动的游标 看例子
use xland
go
--使用select into创建一个临时表
select id,title into cursortable from mytable
--给这个临时表来个主键
alter table cursortable add constraint pkcursor primary key (id)
--允许将显式值插入到标识列中
set identity_insert cursortable on
--定义局部的可滚动的静态的游标
declare cursortest cursor local scroll static for
select id,title from cursortable
declare @id int,@title varchar(max)
open cursortest
fetch next from cursortest into @id ,@title
while @@fetch_status = 0
begin
print (cast(@id as varchar)+' '+@title)
fetch next from cursortest into @id ,@title
end
update cursortable set title ='这不是我更新的数据' where id = 4
delete from cursortable where id = 2
insert into cursortable (id,title) values (33,'这是插入的')
select id,title from cursortable
--滚到第一条
fetch first from cursortest into @id,@title
while @@fetch_status != -1
begin
if @@fetch_status = -2
begin
print 'delete'
end
else
begin
print (cast(@id as varchar)+' '+@title)
end
fetch next from cursortest into @id,@title
end
close cursortest
deallocate cursortest
drop table cursortable得到的消息 (8 行受影响) 2 测试看看 4 这是我更新的数据 5 asdfasdf 6 全文索引全文索引全文索引xland的全文索引 7 xland 8 可以吗 9 应该没问题 10 暗暗
(1 行受影响)
(1 行受影响)
(1 行受影响)
(8 行受影响) delete 4 这不是我更新的数据 5 asdfasdf 6 全文索引全文索引全文索引xland的全文索引 7 xland 8 可以吗 9 应该没问题 10 暗暗
得到的结果 4 这不是我更新的数据 5 asdfasdf 6 全文索引全文索引全文索引xland的全文索引 7 xland 8 可以吗 9 应该没问题 10 暗暗 33 这是插入的 如果把keyset改成dynamic 删除的记录没有通知 消息里显示出了 插入的记录和更新的记录 可以在游标滚动的时候修改表的记录
腾讯云开发者
