一.建表
INSERT INTO cloud VALUES( '你' )
INSERT INTO cloud VALUES( '一会看我' )
INSERT INTO cloud VALUES( '一会看云' )
INSERT INTO cloud VALUES( '我觉得' )
INSERT INTO cloud VALUES( '你看我时很远' )
INSERT INTO cloud VALUES( '你看云时很近' )二.建立游标
1.游标的一般格式
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 游标名称 (释放游标)
cursor：表示在光标当前位置执行操作
2.实例：
DECLARE
@v_line varchar(50) ---------声明一个装载语句的字符串
DECLARE cursor_cloud CURSOR
FOR
SELECT linetext from cloud;
BEGIN
OPEN cursor_cloud; -------打开游标
FETCH NEXT FROM cursor_cloud INTO @v_line-------将游标移向下一行，把获取的内容放入@V_line
WHILE @@FETCH_STATUS = 0 -------检测是否成功获取数据
BEGIN
PRINT @v_line -------进行SQL语句操作
FETCH NEXT FROM cursor_cloud INTO @v_line-------向下移行
END
CLOSE cursor_cloud; -------关闭游标
DEALLOCATE cursor_cloud; -------释放游标
END;结果得：
三.使用游标修改数据（update）
DECLARE
@v_line varchar(50),
@i int
DECLARE cursor_cloud CURSOR
FOR
SELECT linetext from cloud;
BEGIN
SET @i = 0;
OPEN cursor_cloud;
FETCH NEXT FROM cursor_cloud INTO @v_line
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i = @i + 1;
UPDATE cloud SET linetext = CAST(@i as varchar(5)) + ' ' + @v_line WHERE linetext =
@v_line;
FETCH NEXT FROM cursor_cloud INTO @v_line
END
CLOSE cursor_cloud;
DEALLOCATE cursor_cloud;
END;
结果得：
四.可灵活移动的游标
DECLARE
@v_line varchar(50)
DECLARE cursor_cloud CURSOR SCROLL
FOR
SELECT linetext from cloud;
BEGIN
OPEN cursor_cloud;
FETCH FIRST FROM cursor_cloud INTO @v_line
PRINT '第一行 ' + @v_line
FETCH NEXT FROM cursor_cloud INTO @v_line
PRINT '第一行的下一行 ' + @v_line
FETCH LAST FROM cursor_cloud INTO @v_line
PRINT '最后一行 ' + @v_line
FETCH PRIOR FROM cursor_cloud INTO @v_line
PRINT '最后一行的前一行 ' + @v_line
CLOSE cursor_cloud;
DEALLOCATE cursor_cloud;
END;结果得
五.可更新的游标
DECLARE
@v_line varchar(50),
@i int
DECLARE cursor_cloud CURSOR
FOR
SELECT linetext from cloud for update;
BEGIN
DECLARE @v_new_line varchar(50);
SET @i = 0;
OPEN cursor_cloud;
FETCH NEXT FROM cursor_cloud INTO @v_line
WHILE @@FETCH_STATUS = 0
BEGIN
set @v_new_line = SUBSTRING( @v_line, 2,100 );
UPDATE cloud SET linetext = @v_new_line WHERE CURRENT OF cursor_cloud;
FETCH NEXT FROM cursor_cloud INTO @v_line
END
CLOSE cursor_cloud;
DEALLOCATE cursor_cloud;
END;
通过update...........where current of进行游标的更新
UPDATE cloud SET linetext = @v_new_line WHERE CURRENT OF cursor_cloud;表示更新最后一个fetch命令从cursor_cloud游标获得的行
where current of不仅能结合update操作，也能结合delete进行操作
delete from cloud
WHERE CURRENT OF cursor_clould;表示删除最后一个fetch命令从cursor_cloud游标获得的行
注：
嵌入式SQL UPDATE或DELETE可以使用WHERE子句(不带游标)或WHERE CURRENT OF(带声明游标)，但不能同时使用两者。
如果指定的UPDATE或DELETE既不带WHERE也不带WHERE CURRENT OF，则会更新或删除表中的所有记录。
更新的限制
当使用WHERE CURRENT OF子句时，不能使用当前字段值更新字段以生成更新的值。
例如，SET Salary=Salary+200或SET Name=UPPER(Name)：将字段的字母全部变成大写。
尝试这样做会导致
SQLCODE -69错误:SET <field> = <value expression> not allowed with WHERE CURRENT OF <cursor>。
————————————————
原文链接：https://blog.csdn.net/yaoxin521123/article/details/121602006
练习：
插入表如下
insert into words2 values(1,'你最可爱')
insert into words2 values(2,'我说时来不及思索')
insert into words2 values(3,'而思索过后')
insert into words2 values(4,'还是会这么说')
（1）
declare
@num int,@linetext varchar(100)
declare cursor_words cursor scroll
for
select linetext from words2
set @num=1
open cursor_words
fetch next from cursor_words into @linetext
while @@FETCH_STATUS=0
begin
print '普希金说的第'+cast(@num as varchar(10))+'句话：'+@linetext
fetch next from cursor_words into @linetext
set @num = @num+1
end
print ' '
print'他一共说了'+cast(@num-1 as varchar(10))+'句话'
close cursor_words
deallocate cursor_words
（2） 在奇数行后面加逗号，偶数行后面加句号
declare
@i int,@linetext varchar(100)
declare cursor_words cursor
for
select linetext from words2
begin
set @i=0
open cursor_words;
fetch next from cursor_words into @linetext
while @@FETCH_STATUS=0
begin
set @i=@i+1
if(@i%2=1)
update words2
set linetext=@linetext+',' where linenum=@i
else
update words2
set linetext=@linetext+'。' where linenum=@i
fetch next from cursor_words into @linetext
end
close cursor_words
deallocate cursor_words
end再用
select * from words2得到
（3）将奇数行和偶数行合并在一起，同时调整相应的行号
重点：where current of
declare @num int,@linetext varchar(50),@newline varchar(100)
declare cursor_words2 cursor
for
select linetext from words2 for update;-----手工加锁语句
set @num = 1
set @newline = ''
open cursor_words2
fetch next from cursor_words2 into @linetext
while @@FETCH_STATUS=0
begin
set @newline=@newline+@linetext
if @num%2=1
delete from words2 WHERE CURRENT OF cursor_words2----------删除奇数行
else
begin
update words2 set linetext=@newline,linenum=@num/2 WHERE CURRENT OF cursor_words2-----------修改行号
set @newline=''
end
fetch next from cursor_words2 into @linetext
set @num = @num +1
end
close cursor_words2
deallocate cursor_words2
再用
select *from words2得到
其中的加锁语句可以阅读这篇：
http://t.csdn.cn/Hox1D
