游标介绍（附例题）

1.游标的特点

1）返回一个完整的结果集，但是允许程序设计语言只调用集合中的一行

2）定位在结果集的特定行

3）在结果集中对当前行进行修改

2. 声明游标

declare Grade_Cursor Cursor

For select * from grade

* declare 游标名 Cursor :只向前移动一下， NExt

* declare 游标名 Cursor Forward_only: 只支持Next

* declare 游标名 Cursor Scroll: 游标随意移动

3.打开游标

open Grade_Cursor

游标被打开后，游标指针位于结果集的第一行之前。

4.检索游标

Fetch next/prior/first/last(游标的方向) from 游标名

Fetch next from Grade_Cursor

逐行检索

while @@FETCH_STATUS=0

Begin

Fetch next from Grade_Cursor

end

全局变量

@@Fetch_Status: 0->成功完成Fetch语句

-1->Fetch语句有错，或当前游标指针已经到最后一行了

-2->提取的行不存在

@@rowcount: 返回目前已经从游标结果集中提取的行数

------------------------------------------------------------

例题：用到的表TblTeacher和TblTeacherSalary

----1将工资更新，更新后的金额为原来的工资+奖金。
declare @id int
declare @reward money
declare cur_Reward cursor fast_forward
for select tTId,reward from TblTeacherSalary

open cur_Reward
fetch next from cur_Reward into @id,@reward
while @@FETCH_STATUS=0
begin
update TblTeacher set tTSalary=tTSalary+@reward where tTId=@id
fetch next from cur_Reward into @id,@reward
end

close cur_Reward
deallocate cur_Reward

select * from TblTeacher
select * from TblTeacherSalary

---------------------------------------------------------------------------------
--2将奖金中的reward设置为工资的0.1倍
declare @sid int
declare @salary money
declare cur_reward2 cursor forward_only
for select tTId from TblTeacherSalary
open cur_reward2

fetch next from cur_reward2 into @sid
while @@FETCH_STATUS=0
begin
select @salary=tTSalary from TblTeacher where tTId=@sid
update TblTeacherSalary set reward=@salary*0.1 where current of cur_reward2
fetch next from cur_reward2 into @sid
end

close cur_reward2
deallocate cur_reward2

select * from TblTeacher
select * from TblTeacherSalary

--------------------------------------------------------------------------------
-- 3把游标写在对面的表上第一题:把游标写在TblTeacher.改TblTeacher；
declare @sid int
declare @salary money
declare cur_reward2 cursor forward_only
for select tTId from TblTeacherSalary

open cur_reward2
fetch next from cur_reward2 into @sid
while @@FETCH_STATUS=0
begin
--set @salary=(select tTSalary from TblTeacher)
select @salary=tTSalary from TblTeacher where tTId=@sid
update TblTeacherSalary set reward=@salary*0.1 where current of cur_reward2
fetch next from cur_reward2 into @sid
end
close cur_reward2
deallocate cur_reward2

select * from TblTeacher
select * from TblTeacherSalary

--第二题:改TblTeacherSalary,我们把游标写在techar上。

use TestSchool
select * from TblTeacher
select * from TblTeacherSalary

declare @id1 int
declare @salary money
declare cur_Reward1 cursor fast_forward
for select tTId,tTSalary from TblTeacher

open cur_Reward1
fetch next from cur_Reward1 into @id1,@salary
while @@FETCH_STATUS=0
begin
Update TblTeacherSalary set reward=@salary*0.1 where tTId=@id1
fetch next from cur_Reward1 into @id1,@salary
end

close cur_Reward1
deallocate cur_Reward1

-----------------------------------------------------------------------
-- 4把奖金表中的奖金更新为 reward-teacher.salary*0.05
update TblTeacherSalary set reward=reward-(select tTSalary*0.01 from TblTeacher where TblTeacher.tTId=TblTeacherSalary.tTId)

declare @id1 int
declare @salary money
declare cur_Reward1 cursor fast_forward
for select tTId,tTSalary from TblTeacher

open cur_Reward1
fetch next from cur_Reward1 into @id1,@salary
while @@FETCH_STATUS=0
begin
Update TblTeacherSalary set reward=@salary*0.05 where tTId=@id1
fetch next from cur_Reward1 into @id1,@salary
end

close cur_Reward1
deallocate cur_Reward1

select * from TblTeacher
select * from TblTeacherSalary