一.建表
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进行操作
1. delete from cloud 2. 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>。
练习：
插入表如下
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
得到
其中的加锁语句可以阅读这篇：
