2 id int
3 name varchar ( 50 )
4
5 declare @id int
6 declare @name varchar ( 50 )
7 declare cursor1 cursor for -- 定义游标cursor1
8 select * from table1 -- 使用游标的对象(跟据需要填入select文)
9 open cursor1 -- 打开游标
10
11 fetch next from cursor1 into @id , @name -- 将游标向下移1行,获取的数据放入之前定义的变量@id,@name中
12
13 while @@fetch_status = 0 -- 判断是否成功获取数据
14 begin
15 update table1 set name = name + ' 1 '
16 where id = @id -- 进行相应处理(跟据需要填入SQL文)
17
18 fetch next from cursor1 into @id , @name -- 将游标向下移1行
19 end
20
21 close cursor1 -- 关闭游标
22 deallocate cursor1
declare @deptid int , @username varchar ( 20 )
-- 定义游标
declare Select_cursor cursor for
select deptid,username from #Temp1
open Select_cursor --打开游标
fetch next from Select_cursor into @deptid , @username -- 提取操作的列数据放到局部变量中
while @@fetch_status = 0 -- 返回被 FETCH 语句执行的最后游标的状态
/*
@@FETCH_STATUS =0 FETCH 语句成功
@@FETCH_STATUS =-1 FETCH 语句失败或此行不在结果集中
@@FETCH_STATUS =-2 被提取的行不存在
*/
begin
-- 当表#Temp2列deptid存在相同的数据时,就直接在列username上追加@username值
if ( exists ( select * from #Temp2 where deptid = @deptid ))
update #Temp2 set username = username + @username where deptid = @deptid
else
-- 插入新数据
insert into #Temp2 select @deptid , @username
fetch next from Select_cursor into @deptid , @username
end
close Select_cursor
deallocate Select_cursor