游标的那些事（小例题）

游标
----1讲的工资更新，更新后的金额为原来的工资+奖金。
--第一,定义两个变量,来存放奖金表(TblTeatherSalary)中的tTId和reward
declare @id int
declare @reward money
declare cur_Reward cursor fast_forward
for  select tTId,reward from TblTeatherSalary --基于查询的数据集
select * from T_Teather
open cur_Reward
  fetch next from cur_Reward into @id,@reward
  while @@FETCH_STATUS=0
  begin
    update T_Teather set FteaSalary=FteaSalary+@reward where FteaId=@id
    fetch next from cur_Reward into @id,@reward
  end
close cur_Reward
deallocate cur_Reward

--  2将奖金中的reward设置为 工资的0.1倍
select * from TblTeatherSalary

declare @sid int
declare @salary money
--(1)定义游标
declare cur_reward cursor fast_forward
for select tTid from TblTeatherSalary
--打开
open cur_reward
fetch next from cur_reward into @sid
while @@FETCH_STATUS=0               --表示上次我们游标移动成功
begin
  --set @salary=(select FteaSalary from TblTeatherSalary)
  select @salary=FteaSalary from TblTeatherSalary
--更新奖金表
  update TblTeatherSalary set reward=@salary*0.1 where FteaId=@sid
end
close cur_reward
deallocate cur_reward

 --3(1)把游标写在对面的表上第一题把游标写在TblTeacher.改TblTeacher；
 declare @tid int
 declare @reward2 money
 declare cur_reward cursor forward_only
 for select Fteaid from T_Teather
 open cur_reward
 fetch next from cur_reward into @tid
 while @@FETCH_STATUS=0
 begin
 --set @salary=(select FteaSalary from T_Teather )
 select @reward2=reward from TblTeatherSalary where tTid=@tid
 
  update T_Teather set FteaSalary=FteaSalary+@reward2 where current of cur_reward
 fetch next from cur_reward into @tid
 end
 close cur_reward
 deallocate cur_reward
--(2)第二题改TblTeacherSalary,我们把游标写在techar上。
 declare @tid2 int
 declare @tSalary money
 declare cur_Reward2 cursor forward_only
 for select Fteaid,FteaSalary from T_Teather
 open cur_Reward2
 fetch next from cur_Reward2 into @tid2,@tSalary
 while @@FETCH_STATUS=0
 begin
 update TblTeatherSalary set reward=@tSalary*0.1 where tTid=@tid2
 fetch next from cur_Reward2 into @tid2,@tSalary
 end
 close cur_Reward2
 deallocate cur_Reward2
 
  select * from TblTeatherSalary
  select * from T_Teather
  --4把奖金表中的奖金更新为 reward-teacher.salary*0.05
 update TblTeatherSalary  set reward=reward-(select FteaSalary from T_Teather
 where T_Teather.Fteaid=TblTeatherSalary.tTid)*0.05
 
 select * from TblTeatherSalary