SQL游标

 

游标是一种从结果集中每次提取一条记录的机制

游标提供一中对表中数据进行操作的灵活手段

使用游标，可以实现以下目标

A、允许定位到结果集中的特定行

B、从结果集的当前位置检索一行或多行数据

C、支持对结果集中当前位置的行进行修改

D、对于其他用户对结果集的修改，支持不同的可见性级别

 

游标的使用顺序

1、定义游标

2、打开游标

3、使用游标

4、关闭游标

5、释放游标

提取和滚动

从游标中检索一行或多行的操作成为提取

执行一系列的提取操作一向前或向后检索的行的操作称为滚动

 

声明游标的语法如下：

Declare Cursor_name [ INSENSITIVE ][SCROLL] CURSOR

FOR select_statement

[FOR{READ ONLY|UPDATE [OF column_name_list]}]

 其中Cursor_name是游标的名字

SCROLL:表示取游标时可以使用关键字NEXT、PRIOR、FIRST、Last、absolute、relative

select_statement:是定义游标结果集的标准SELECT语句，它可以是一个完整的语法和语义的Transact-SQL的SELECT语句，但是这个SELECT语句必须有FROM子句，不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE、和INTO

FOR READ ONLY：指出该游标结果集只能读，不能修改

FOR UPDATE:指出该游标结果集可以被修改

OF column_name_list:列出可以被修改列的名单

注意：

1、游标有且只有两种方式：FOR READ ONLY或FOR UPDATE，默认为FOR UPDATE方式

2、单游标方式指定为FOR READ ONLY时，游标涉及的表不能被修改

3、单游标方式指定为FOR UPDATE 时，可以删除或更新游标涉及的表中的行

4、游标必须先申明在打开即声明游标的DECLARE CURSOR语句必须是在该游标的任何OPEN语句之前

 

打开游标

Open <Cursor_name>

检索记录 

Fetch <Cursor_name>

关闭游标

Close <Cursor_name>

删除游标引用

Deallocate <Cursor_name>

 关闭游标格式

使用CLOSE语句关闭游标

CLOSE { { [ GLOBAL ] 游标名 } | 游标变量名 }

使用DEALLOCATE语句删除游标，其语法格式如下：

DEALLOCATE { { [ GLOBAL ] 游标名 } | @游标变量名

 

提取和滚动游标

从打开的游标中提取行的语法如下：

FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE] FROM] cursor_name [INTO fetch_target_list]

游标被打开后，游标位置位于结果集的第一行前，默认的缺省值是NEXT，即向下移动

Fetch First :提取游标的第一行

Fetch Next :提取上次提取的行的下一行

Fetch Prior  提取上次提取的行的前一行

Fetch  Last 提取游标中的最后一行

Fetch absolute n:

如果n为正整数，则提取游标中的第n行

如果n为负整数，则提取游标最后一行之前的第n行

如果n为0，这不提取任何

FETCH absolute n from mycursor into @sortid,@sortname !--其中@sortid,@sortname是申明的变量

Fetch relative n

如果n为正，则提取上次提取的行之后的第n行

如果n为负，则提取上次提取的行之前的第n行

Fetch relative n from mycursor into @sortid,@sortname

INTO fetch_target_list:指定存放被提取的列数据的目的变量清单，这个清单中变量的个数、数据类型、顺序必须与定义给游标的select_statement的select_list中列出的清单相匹配，为了更灵活的操作数据，可以把从已声明并已打开的游标结果集中提取列的数据，分别存放在目的变量中。

Fetch语句全局变量

@@FETCH_STATUS

为执行的上一个FETCH语句返回一个整数值

保存者最后Fetch语句执行的状态信息，其值和含义如下：

0:表示成功完成Fetch语句

-1表示Fetch语句执行错误，或者当前游标位置已在结果集中的最后一行，结果集不再有数据

-2：提取的行不存在

@@CURSOR_ROWS（静态游标）

返回当前打开的游标中符合条件的行的数目

@@Rowcount

保存者自游标打开以后的第一个Fetch语句，只道最近一次Fetch语句为止，已存游标结果集中提取的行数即已Fetch的记录数，而非所有记录数

  没个打开的游标都与一个特定的@@rowcount有关，关闭游标是，该@@rowcount变量也被删除，在Fetch语句执行后查看这个变量可以得知从游标结果集中已提取的行数

问题：

@@rowcount只能记录得到已Fetch的记录数，如果要取集合内所有的记录数，必须循环把集合内的记录都Fetch一遍，有什么办法直接在打开游标后就获取到游标结果集的行数？

答：可以声明游标为静态游标(insensitive)在打开游标以后，@@cursor_rows的值即使游标结果集的行数

声明游标使用实例：

--设置不显示计数，即不显示每条SQL语句执行完成后下面显示的(X行受影响)

set nocount on

--声明变量

declare @sortid int

declare @sortname nvarchar(50)

--声明游标

declare mycursor cursor for select id,sortname from productSort

--打开游标

open mycursor

--读取数据

fetch next from mycursor into @sortid,@sortname

--判断游标的状态

--0 fetch成功

--1: fetch语句失败或此行不在结果集中

---2:被提取的行不存在

while(@@FETCH_STATEU=0)

begin

print '产品类型:'+@sortname

select products.productname as 产品名称,products.price as 单价，Unit.UnitName as 计价单位 from products left outer join units on products.UnitID=Units.ID where

products.ProductSortID=@sortid

--用游标去下一条记录

Fetch next from mycursor into @sortid,@sortname

end

--关闭游标

close mycursor

--删除游标

deallocate mycursor

--恢复计数设置

set nocount off

 

直接获取游标结果集的行数

原理：利用声明静态游标和@@cursor_rows直接在游标打开后获取游标结果集的行数

set nocount on

declare @sortid int

declare @sortname nvarchar(50)

declare mycursor insensitive cursor for select id,sortname from productSort

open mycursor

select @@cursor_rows /*获取游标结果集的行数*/

余下代码同上

使用游标修改数据

用户可以在update或delete语句中使用游标来更新、删除表或视图中的行，但不能用来插入新行

 

通过在update 语句中使用游标可以更新表或视图中的行。被更新的行依赖于游标位置的当前值。

更新数据语法形式如下：

update {table_name|view_name} set [[{table_name.|view_name.}] column_name= {new_value}[..n] where current of cursor_name

其中：紧跟update之后的table_name|view_name；要更新的表名或视图名，必须是声明白游标的select 语句中的表名或视图名

column_name：是要更新的列的列名，必须是什么游标的select语句中update of column_name_list的子集

new_value:为被更新列的新值，它可以是一个表达式、空值、字查询。

where current of:使用sql server值更新由指定游标的游标位置当前值的确定的行

cursor_name:是已声明为for update方式并一打开的游标名。

注意：

A、使用update  ...current of语句一次只能更新当前游标位置确定的那一行，open语句将游标位置定位在结果集第一行前，可以使用fetch语句将游标定位到要呗更新的数据行处。

B、update .. where current of 语句更新表中的行时，不会移动游标，被更新的行可以再次被修改，直到下一个fetch语句的执行，

C、update..where current of语句可以更新多表视图或被连接的多表，单只能更新其中一个表的行，即所有被更新的列都来自同一个表

使用游标删除数据

通过delete语句中使用游标来删除表或视图的行，被删除的行依赖于游标位置的当前值

删除数据语法形式如下：

delete [from] [[database.].owner.]{table_name|view_name} where current of cursor_name

其中：

table_name|view_name为要从其中删除行的表名或视图名，必须是定义给游标的select语句中的表名或视图名

cursor_name:为已声明并已打开的游标名

where current of::它使用sql server只删除有指定游标的游标位置当前值确定的行

注意：使用游标的delete语句，一次只能删除但却游标位置确定的那一行，open语句将游标位置定位在结果集第一行之前，可以用fetch语句把游标位置定位到要删除的行处，

在delete语句中使用的游标必须声明为for update 方式，而且声明游标的select语句不能含有链接操作或涉及多表视图，否则即使声明了for update 方式，也不能删除其中的行

对使用游标删除行的表，要求有一个唯一索引

使用游标的delete语句，删除一行后将游标位置向前移动一行

 

 

set nocount on

delcare @price money

declare mycursor cursor for select price from products

open mycursor

fetch next from mycursor into @price

where (@@FETCH_STATUS=0)

BEGIN
IF(@Price>10)

begin

delete from products where current of mycursor

end

else

begin

update products set price=price*2 where current of mycursor

end

fetch next from mycursor into @price

end

close mycursor

deallocate mycursor

set nocount off

游标与事务结合使用

--开始事务

begin tran

--设置不显示计数信息，即不显示条sql语句执行完成后下面显示的(x行受影响）

set nocount on

--声明变量，用来存储未来游标中取出的值及@@error累计值

declare @sortid int

declare @sortname nvarchar(50)

delcare @Sumerror int =0

while(@@FETCH_STATUS=0)

begin

........................

执行增、删、改（如果需要)

set @SumError+=@@error;

--用游标取下一条记录

fetch next from mycursor into @sortid,@sortname

end

--对事务进行最终的操作

if(@Sumerror!=0)

begin

rollback tran

end

else

begin

commit tran

end

close mycursor