游标的简单使用

create table tableA
(
tid int not null,
mid int not null
)
go
insert into tableA values(2,2)
insert into tableA values(3,3)
insert into tableA values(4,4)
insert into tableA values(5,5)
create table tableB
(
fid int not null,
tid int not null
)
go
insert into tableB values(1,2)
insert into tableB values(1,3)
insert into tableB values(1,4)
insert into tableB values(1,5)
go

select * from tableB
select * from tableA

declare @num int
set @num=1
update tableB set fid=(
select top 1 tableA.mid from tableB,tableA
where tableB.tid=tableA.tid and tableA.tid>
(exec('select top '+@num+' TID from tableA'))
)
go

declare @num int
set @num=1
exec('select top '+@num+' TID from tableA')

declare @id int --定义变量来保存ID号
declare @A int --定义变量来保存值
declare mycursor cursor for select * from tableA --为所获得的数据集指定游标
open mycursor --打开游标
fetch next from mycursor into @id,@A --开始抓第一条数据
while(@@fetch_status=0) --如果数据集里一直有数据
begin
update tableB set fid=@A where tid=@id --开始做想做的事（什么更新呀，删除呀）
fetch next from mycursor into @id,@A --跳到下一条数据
end
close mycursor --关闭游标
deallocate mycursor --删除游标