sql中游标的使用
一般情况下,我们用SELECT这些查询语句时,都是针对的一行记录而言,
如果要在查询分析器中对多行记录(即记录集)进行读取操作时,则需要使用到游标或WHILE等循环
游标的类型：
1、静态游标(不检测数据行的变化）2、动态游标（反映所有数据行的改变）
3、仅向前游标（不支持滚动）
4、键集游标（能反映修改，但不能准确反映插入、删除）
游标使用顺序：
1、定义游标2、打开游标
3、使用游标
4、关闭游标
5、释放游标
Transact-SQL：
declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS]for selet语句 [for update[of 列名[，列名]]
注：LOCAL 局部游标 GLOBAL 全局游标
FORWARD_ONLY 仅向前 SCROLL 滚动
STATIC 静态 KEYSET 键集 DYNAMIC 动态
READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行
获取游标的数据
FETCH [[NEXT | PRIOR | FIRST | LAST |ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]
From ] 游标名 [into 变量]
注：
NEXT 下一行 PRIOR 上一行 FIRST 第一行
LAST 最后一行 ABSOLUTE n 第n行
RELATIVE n 当前位置开始的第n行
into 变量 把当前行的各字段值赋值给变量
游标状态变量:
@@fetch_status 游标状态0 成功 -1 失败 -2 丢失
@@cursor_rows 游标中结果集中的行数
n 行数 -1 游标是动态的 0 空集游标
操作游标的当前行:
current of 游标名
例1：利用游标对查询的数据集进行遍历
use pubs
go
declare @auid char(12),@aulname varchar(20),@aufname varchar(20), @st char(2),@auinfo varchar(50)
declare auth_cur cursor for
select au_id, au_lname, au_fname, state
from authors
open auth_cur
fetch next from auth_cur into @auid,@aulname,@aufname, @st
while (@@fetch_status=0)
begin
print '作者编号: '+@auid
print '作者姓名: '+@aulname+','+@aufname
print '所在州: '+@st
print '--------------------------'
fetch next from auth_cur into @auid,@aulname,@aufname, @st
end
close auth_cur
deallocate auth_cur
例2：利用游标修改、删除数据
declare auth_cur cursor scroll for
select au_id, au_lname, au_fname, state
from authors for update of au_lname
open auth_cur
declare @rowcount int
set @rowcount = 6
fetch absolute @rowcount from auth_cur --将变量@rowcount标识的行设置为当前行
--下面1行是利用游标进行修改操作
update authors set au_lname='张' where current of auth_cur --修改游标中的当前行
--下面1行是利用游标进行删除操作
delete from authors where current of auth_cur
例3：利用游标进行插入数据
如下例子中，从T_School表中查出schoolID、classID、studentID放在游标中，每次从游标中取一条记录插入到T_LeaveSchool表中，直到游标数据取完时，
select count(*) from T_LeaveSchool where StudentID=@StudentID and SchoolID=@SchoolID and ClassId=@ClassID这条语句查出来为0，不满足if语句，游标结束。
--创建数据库 create proc InsertStudent as --定义所需要的变量 declare @SchoolID int declare @ClassID int declare @StudentID int declare @IDNumber int --条件判断时需要 begin --创建游标 declare FeeInsertStudent cursor for select stuID,SchID,ClasID from T_School --打开游标 open FeeInsertStudent --从游标里取出数据给 变量 赋值 fetch next from FeeInsertStudent into @SchoolID,@ClassID,@StudentID --判断有标的状态 while @@FETCH_STATUS=0 begin --为变量赋值 set @IDNumber=(<pre name="code" class="sql">select count(*) from T_LeaveSchool where StudentID=@StudentID and SchoolID=@SchoolID and ClassId=@ClassID)if(@IDNumber=0) --判断begininsert into T_LeaveSchool(StudentID,ClassID,SchoolID)values( @StudentID,@ClassID,@StudentID)endfetch next from FeeInsertStudent into @SchoolID,@ClassID,@StudentIDendclose FeeInsertStudent --关闭游标deallocate FeeInsertStudent --撤销游标 end
例4：利用游标循环更新或删除数据
1. 利用游标循环更新、删除MemberAccount表中的数据
- DECLARE My_Cursor CURSOR --定义游标
- FOR (SELECT * FROM dbo.MemberAccount) --查出需要的集合放到游标中
- OPEN My_Cursor; --打开游标
- FETCH NEXT FROM My_Cursor ; --读取第一行数据
- WHILE @@FETCH_STATUS = 0
- BEGIN
- --UPDATE dbo.MemberAccount SET UserName = UserName + 'A' WHERE CURRENT OF My_Cursor; --更新
- --DELETE FROM dbo.MemberAccount WHERE CURRENT OF My_Cursor; --删除
- FETCH NEXT FROM My_Cursor; --读取下一行数据
- END
- CLOSE My_Cursor; --关闭游标
- DEALLOCATE My_Cursor; --释放游标
- GO
2. 利用游标循环更新MemberService表中的数据（更新每个用户所购买服务的时间）
- DECLARE @UserId varchar(50)
- DECLARE My_Cursor CURSOR --定义游标
- FOR (SELECT UserId FROM dbo.MemberAccount) --查出需要的集合放到游标中
- OPEN My_Cursor; --打开游标
- FETCH NEXT FROM My_Cursor INTO @UserId; --读取第一行数据(将MemberAccount表中的UserId放到@UserId变量中)
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT @UserId; --打印数据(打印MemberAccount表中的UserId)
- UPDATE dbo.MemberService SET ServiceTime = DATEADD(Month, 6, getdate()) WHERE UserId = @UserId; --更新数据
- FETCH NEXT FROM My_Cursor INTO @UserId; --读取下一行数据(将MemberAccount表中的UserId放到@UserId变量中)
- END
- CLOSE My_Cursor; --关闭游标
- DEALLOCATE My_Cursor; --释放游标
- GO
