11-14游标

 

---使用游标
--1游标的定义
--使用游标
--游标的定义
--(1)定义
declare cur_student cursor fast_forward--游标快速向前
for select * from T_student--基于返回的结果集
--2(打开)
open cur_student
--3操作
fetch next from cur_student--向下移动一条游标
while @@fetch_status=0--=0标志着上次游标移动成功
begin
fetch next from cur_student
end
--4关闭
close cur_student
--5释放游标
deallocate cur_student
-------------------------------------------------------------

create table teacherward
(
 autoId int identity(1,1) primary key,
 tTId int,
 reward money

)
insert into teacherward(tTId ,reward )
select 1,'1200' union all
select 2,'2100' union all
select 3,'800 'union all
select 4,'200 'union all
select 5,'7200' union all
select 6,'4200 'union all
select 7,'2400' union all
select 8,'5000'

create table Teachersalary(tTid int, tTname nvarchar(50),tTsalary money)
insert into Teachersalary
select 1,'姚洪波',200 union all
select 2,'李伟',100 union all
select 3,'陈伟华',400 union all
select 4,'陈红军',300
---------------------将工资更新，更新后的金额为原来的工资+奖金
--declare @id int
--declare @reward money
--declare cur_Reward cursor fast_forward
--for select tTId,reward from teacherward
--open cur_Reward
--fetch next from cur_Reward into @id,@reward
--while @@FETCH_STATUS =0
--begin
-- update Teachersalary set tTsalary=tTsalary+@reward wheretTid=@id
-- fetch next from cur_Reward into @id,@reward
--end 
--close cur_Reward
--deallocate cur_Reward
------------------------将奖金的reward 设置成工资的0.1倍

--declare @sid int
--declare @salary money
--declare cur_Reward2 cursor forward_only
--for select tTId from teacherward
----打开
--open  cur_Reward2
--fetch next from cur_Reward2 into @sid
--while @@FETCH_STATUS =0
--begin
----set @salary=select tTsalary from Teachersalary where tTid =@sid
--     select @salary =tTsalary from Teachersalary where tTid =@sid
----更新奖金表
--     update teacherward  set reward =@salary *0.1 where current  of cur_Reward2
--     fetch next from cur_Reward2 into @sid
--end
--close cur_Reward2
--deallocate cur_Reward2

------------------作业
--3把游标写在对面的表上
--第一题把游标写在TblTeacher.改TblTeacher；

declare @tTid int
declare @reward money
declare cur_Reward cursor forward_only
for select tTid,tTsalary from Teachersalary ----基于返回的结果集
open cur_Reward
fetch next from cur_Reward into @tTid ,@reward
while @@FETCH_STATUS =0
begin
  select @reward= reward from teacherward where tTId =@tTid 
 update Teachersalary set tTsalary=tTsalary+@reward where current  of cur_Reward
 fetch next from cur_Reward into @tTid,@reward
end 
close cur_Reward
deallocate cur_Reward
--第二题改TblTeacherSalary,我们把游标写在techar上。
declare @id int
declare @salary money
declare cur_Reward cursor fast_forward
for select tTid,tTsalary from Teachersalary
open cur_Reward
fetch next from cur_Reward into @id,@salary
while @@FETCH_STATUS =0
begin
 update teacherward  set reward =@salary *0.1 wheretTid=@id
 fetch next from cur_Reward into @id,@salary
end 
close cur_Reward
deallocate cur_Reward

select * from teacherward
select * from Teachersalary
--  4把奖金表中的奖金更新为 reward-teacher.salary*0.05

declare @id1 int
declare @s int
declare cur_teacher cursor forward_only
for select tTid from teacherward
open cur_teacher
fetch next from cur_teacher into @id1
while @@FETCH_STATUS=0
begin
  select @s= tTsalary from Teachersalary where tTid=@id1
  set @s=@s*0.05
  update teacherward  set reward =reward-@s wheretTId=@id1
  fetch next from cur_teacher into  @id1
end
close cur_teacher
deallocate cur_teacher