declare @gonghao nvarchar(50) declare @LowValueAssetsID nvarchar(50) declare @CreateTime dateTime declare user_cur cursor for select LowValueAssetsID,gonghao,CreateTime from LowValueAssets open user_cur while @@fetch_status=0 begin --读取游标 fetch next from user_cur into @LowValueAssetsID,@gonghao,@CreateTime --set @gonghao = (select Gonghao from LowValueAssets ls where ls.LowValueAssetsID = @LowValueAssetsID) --工号 declare @Depart nvarchar(50) --部门 declare @Name nvarchar(200) --名称 --depart set @Depart =(select distinct Depart from Staffers s inner join LowValueAssets l on l.Gonghao = s.EmpID where l.Gonghao =@gonghao) --name set @Name =(select distinct s.ChineseName from Staffers s inner join LowValueAssets l on l.Gonghao = s.EmpID where l.Gonghao =@gonghao) insert into LowValueAssetslinkEmpID (LowValueAssetsID,OwnEmpID,OwnDepart,OwnName,CreateTime) values(@LowValueAssetsID,@gonghao,@Depart,@Name,@CreateTime) end close user_cur --摧毁游标 deallocate user_cur
补充别人讲解的游标
create table student ( id int identity(1,1) primary key, --自动增长编号 name varchar(20) not null, --姓名 age int not null, --年龄 address varchar(100) not null, --地址 ) 使用游标有4个过程:声明游标、打开游标、提取数据、关闭游标。 declare pcurr cursor for --声明一个名为pcurr游标 declare @customer nvarchar(50) declare @age int select name,age from student --这个查询结果非常重要,这个就是我们需要用游标读取的数据集 open pcurr --打开游标 fetch next from pcurr into @customer,@age while (@@fetch_status = 0) --判断游标是否读取完毕,读取完毕,这里将返回100而不是0 begin print (@customer) print (@age) fetch next from pcurr into @customer,@age --取值,由于我们全面的结果集返回返回的是2行记录,这里我们就需要2个自定义参数去取出结果集 close pcurr --关闭游标 deallocate pcurr --释放游标 close 与 deallocate 的不同点: close是关闭游标