游标
----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