游标的建立

 

--使用游标
--游标的定义
--(1)定义
declare cur_student cursor fast_forward--游标快速向前
for select * from 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

 

 

 

use testschool

create table tblsealare (tTid int, tTname nvarchar(50),tTsalaryjj money)
insert into tblsealare
select 1,'姚洪波',200 union all
select 2,'李伟',100 union all
select 3,'陈伟华',400 union all
select 4,'陈红军',300

select * from TblTeacher
select * from tblsealare
declare @salary int
declare @sid int
declare cur_reward2 cursor forward_only
for select tTid from tblsealare--奖金表
--打开
open cur_reward2
--操作
fetch next from cur_reward2 into @sid--奖金表sid
while @@FETCH_STATUS=0
begin
select @salary= tTsalary from TblTeacher where tTid=@sid
--更新奖金表
update tblsealare set tTsalaryjj=@salary*0.1 where CURRENT of cur_reward2
fetch next from cur_reward2 into @sid
end
close cur_reward2
--5释放游标
deallocate cur_reward2
----------------------------------------------
-------------------------将工资更新，更新后的金额为原来的工资+奖金
declare @s1 int
declare @id int
declare cur_jj cursor forward_only
for select tTid from TblTeacher--工资表
open cur_jj
fetch next from cur_jj into @id--工资表id获取
while @@FETCH_STATUS=0
begin
select @s1= tTsalaryjj from tblsealare where tTid =@id
update TblTeacher set  tTsalary =tTsalary+@s1 where CURRENT of cur_jj
fetch next from cur_jj into @id
end
close cur_jj
deallocate cur_jj
--3把游标写在对面的表上第一题把游标写在TblTeacher.改TblTeacher；--第二题改TblTeacherSalary,我们把游标写在techar上。
declare @s2 int
declare @id2 int
declare cur_jj2 cursor forward_only
for select tTid from tblsealare
open cur_jj2
fetch next from cur_jj2 into @id2
while @@FETCH_STATUS=0
begin
select @s2= tTsalaryjj from tblsealare where tTid=@id2
update TblTeacher set tTsalary=tTsalary+@s2 where tTid=@id2
fetch next from cur_jj2 into @id2
end
close cur_jj2
deallocate cur_jj2
--4把奖金表中的奖金更新为 reward-teacher.salary*0.05
declare @id3 int
declare @sa int
declare cur_jj3 cursor forward_only
for select tTid from tblsealare
open cur_jj3
fetch next from cur_jj3 into @id3
while @@FETCH_STATUS=0
begin
select @sa= tTsalary from TblTeacher where tTid=@id3
set @sa=@sa*0.05
update tblsealare set tTsalaryjj=tTsalaryjj-@sa where tTid=@id3
fetch next from cur_jj3 into @id3
end
close cur_jj3
deallocate cur_jj3

ps:不要忘记关闭和释放游标