游标的使用
-- 游标详解
id int
name varchar(50)
declare @id int
declare @name varchar(50)
declare cursor1 cursor for --定义游标cursor1
select * from table1 --使用游标的对象(跟据需要填入select文)
open cursor1 --打开游标
fetch next from cursor1 into @id,@name --将游标向下移行，获取的数据放入之前定义的变量@id,@name中
while @@fetch_status=0 --判断是否成功获取数据
begin
update table1 set name=name+'1'
where id=@id --进行相应处理(跟据需要填入SQL文)
fetch next from cursor1 into @id,@name --将游标向下移行
end
close cursor1 --关闭游标
deallocate cursor1
游标一般格式：
DECLARE 游标名称 CURSOR FOR SELECT 字段1,字段2,字段3,... FROM 表名 WHERE ...
OPEN 游标名称
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
WHILE @@FETCH_STATUS=0
BEGIN
SQL语句执行过程... ...
FETCH NEXT FROM 游标名称 INTO 变量名1,变量名2,变量名3,...
END
CLOSE 游标名称
DEALLOCATE 游标名称 (删除游标)
--测试数据
create table tmp1 (
ID int not null,
val varchar(10),
constraint PK_tmp1 primary key (ID)
);
create table tmp2 (
ID int not null,
vals varchar(100),
constraint PK_tmp2 primary key (ID)
);
insert into tmp1(id, val) values (1, 'test');
insert into tmp1(id, val) values (2, 'test2');
insert into tmp1(id, val) values (3, 'test3');
insert into tmp1(id, val) values (4, 'test4');
insert into tmp1(id, val) values (5, 'test5');
insert into tmp2(id, vals) values (1, '1,2');
insert into tmp2(id, vals) values (2, '1,3');
insert into tmp2(id, vals) values (3, '2,5');
insert into tmp2(id, vals) values (4, '1,2,3,4,5');
--存储过程
drop procedure proc_tmp_1
go
CREATE PROCEDURE proc_tmp_1 AS
begin
declare @vals varchar(500)
declare @id int
declare @vals2 varchar(1000)
declare @command varchar(1000)
declare @vals3 varchar(1000)
declare @cmd varchar(1000)
declare cursor_tmp_1 cursor for SELECT id, vals FROM tmp2
open cursor_tmp_1
fetch next from cursor_tmp_1 into @id, @vals
while @@fetch_status = 0
begin
set @vals3 = ''
set @cmd = 'declare cursor_tmp_2 cursor for select val from tmp1 where id in (' + @vals + ')'
EXEC (@cmd)
open cursor_tmp_2
fetch next from cursor_tmp_2 into @vals2
while @@fetch_status = 0
begin
if (@vals3 <> '')
begin
set @vals3 = @vals3 + ','
end
SET @vals3 = @vals3 + @vals2
fetch next from cursor_tmp_2 into @vals2
end
set @command = 'update tmp2 set vals =''' + @vals3 + ''' where id =' + convert(char, @id)
print @command
EXEC (@command)
fetch next from cursor_tmp_1 into @id, @vals
close cursor_tmp_2
deallocate cursor_tmp_2
end
close cursor_tmp_1
deallocate cursor_tmp_1
end
go
--执行存储过程
EXECUTE proc_tmp_1
Go
