SQL Server基础五---游标

—游标能干什么?
—步骤：5步
—创建游标
declare jia cursor
for
select * from Product
—打开游标
open jia
—提取游标
fetch next from jia
—关闭游标
close jia
—释放游标
deallocate jia

———滚动游标—————————-
select from Product
declare jia scroll cursor
for
select from Customer
open jia
select @@CURSOR_ROWS
fetch first from jia —第一条
fetch last from jia —最后一条
fetch prior from jia —前一个
fetch next from jia —下一个
fetch relative -2 from jia —当前位置的回2个
fetch relative 2 from jia —当前位置的前2个
fetch absolute 5 from jia —整个表的第五条数据

———————提取状态———————————-
select @@FETCH_STATUS

fetch next from jia —下一个,判断后面的循环有没有必要做
while(@@FETCH_STATUS=0)
begin
fetch next from jia
end

close jia
deallocate jia

—————fetch——-into————————-
select from Product
declare jia scroll cursor
for
select from Customer
open jia
declare @a int,@b varchar(50),@c varchar(50),@d varchar(50),@e varchar(50)
fetch next from jia
into @a,@b,@c,@d,@e
print’听说有一个叫’+@b+’的’+@d+’孩子，住在’+@c
close jia

———-作用域—————————
alter proc p_9
as
begin
declare jia cursor
—local —默认不写就是全局（Global），local表示本地局部
for
select top 5 Productname,price from Product
open jia
end

exec p_9
fetch next from jia

close jia
deallocate jia

—————————-修改（删除）数据—————-where current of 游标名—————
declare jia scroll cursor
for
select from aaa
open jia
select @@FETCH_STATUS
select from aaa
fetch last from jia
delete from aaa where current of jia

fetch absolute 1 from jia
update aaa set sex=’0’ where current of jia
close jia
deallocate jia