游标的使用

---使用游标
---1游标的定义
--（1）定义
declare cur_MyStudent cursor fast_forword
for select * from MyStudent --基于返回一个结果集
--（2）打开
open cur_MyStudent
--(3)操作
fetch next from cur_MyStudent
while @@fetch_status=0   ---while循环说的是多条记录的操作
begin
fetch next from cur_MyStudent
end
--(4)关闭
close cur_MyStudent
--(5)释放
deallocate

 

select * from TblTeacher
select * from TblteacherSalary

------1将老师的工资更新，更新后的金额为原来的工资+奖金。
----第一，定义两个变量，来存放TblTeacherSalary表中的tTId和reward
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 wheretTId=@id
 fetch next from cur_Reward into @id,@reward
 end
close cur_Reward
deallocate cur_Reward

--2将奖金中的reward设置为工资的0.1倍

declare @sid int
declare @salary money
--1定义游标
declare cur_reward2 cursor fast_forward
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
--更新奖金表
update TblTeacherSalary set reward=@salary*0.1 wheretTId=@sid
end
close cur_reward2
deallocate cur_reward2