SQL Server 中游标的基本操作

潇洒紫焰

对于游标的基本操作主要有以下内容：声明游标
打开游标
读取游标数据
关闭游标
获取游标的状态和属性
修改游标结果集中的行
删除游标结果集中的行
删除游标


声明游标：
可以使用DECLARE CURSOR语句来声明Transact-SQL服务器游标和定义游标的特性，例如游标的滚动行为和结果集的查询方式等。DECLARECURSOR的语法结构如下：
　　DECLAREcursor_name CURSOR
　　[LOCAL | GLOBAL ]
　　[FORWARD_ONLY | SCROLL ]
　　[STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
　　[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
　　[TYPE_WARNING ]
　　FORselect_statement
　　[FOR UPDATE [ OF column_name[ ,...n] ]]
例：
下面是定义游标的一个简单示例：
USE HrSystem
GO
DECLARE Employee_Cursor CURSOR
FOR SELECT * FROM Employees WHERE Sex = '男'
GO
游标结果集是表Employees中所有的男性员工。

打开游标：
OPEN语句的语法结构如下：
　　OPEN{ { [ GLOBAL ] cursor_name } |cursor_variable_name}
参数说明如下：
cursor_name已声明的游标的名称。如果指定了GLOBAL，cursor_name指的是全局游标，否则cursor_name指的是局部游标。
cursor_variable_name指定游标变量的名称。
例：
下面是打开游标的一个简单示例：
　　USE HrSystem
　　GO
　　DECLARE Employee_Cursor CURSOR
　　FOR SELECT * FROM Employees WHERE Sex = '男'
　　OPENE mployee_Cursor
　　GO


读取游标中的数据：
读取数据有三种方式：1.FETCH语句
2.@@FETCH_STATUS函数
3.@@CURSOR_ROWS函数
FETCH:
FETCH语句的功能是从Transact-SQL服务器游标中检索特定的一行。它的语法结构如下：
　　FETCH
　　[ [ NEXT | PRIOR | FIRST | LAST
　　| ABSOLUTE { n | @nvar }
　　| RELATIVE { n | @nvar }
　　]
　　FROM
　　]
　　{{ [ GLOBAL ] 游标名称} | @游标变量名称}
　　[INTO @variable_name [ ,...n ] ]
例：
下面是读取游标数据的一个简单示例：
　　USE HrSystem
　　GO
　　DECLARE Employee_Cursor CURSOR
　　FOR SELECT * FROM Employees WHERE Sex = '男'
　　OPEN Employee_Cursor
　　FETCH NEXT FROM Employee_Cursor
　　GO
例：
以下是使用FETCH LAST读取最后一行数据的示例：
　　USEHrSystem
　　GO
　　DECLARE Employee_Scroll_Cursor SCROLL CURSOR
　　FOR SELECT * FROM Employees WHERE Sex = '男'
　　OPENE mployee_Scroll_Cursor
　　FETCH LAST FROM Employee_Scroll_Cursor
　　GO
　　@@FETCH_STATUS函数

可以使用@@FETCH_STATUS函数获取FETCH语句的状态。返回值等于0表示FETCH语句执行成功；返回值等于-1表示FETCH语句执行失败；返回值等于-2表示提取的行不存在。
例：

执行下面的语句可以使用游标获取表Employees中所有男性员工数据。
　　USE HrSystem
　　GO
　　DECLARE Employee_Scroll_Cursor SCROLL CURSOR
　　FOR SELECT * FROM Employees WHERE Sex = '男'
　　OPEN Employee_Scroll_Cursor
　　WHILE @@FETCH_STATUS = 0
　　BEGIN
　　FETCH FROM Employee_Scroll_Cursor
　　END
　　GO
　　@@CURSOR_ROWS函数

返 回 值	说 明
−m	游标被异步填充。返回值是键集中当前的行数
−1	游标为动态。因为动态游标可反映所有更改，所以符合游标的行数不断变化。因而永远不能确定地说所有符合条件的行均已检索到
0	没有被打开的游标，没有符合最后打开的游标的行，或最后打开的游标已被关闭或被释放
n	游标已完全填充。返回值是在游标中的总行数

例：
验证@@CURSOR_ROWS函数的使用方法。
具体语句如下：
　　USE HrSystem
　　DECLARE 男员工SCROLL CURSOR
　　FOR SELECT * FROM Employees WHERESex='男'
　　-- 没有打开游标时，@@CURSOR_ROWS返回值为0
　　IF @@CURSOR_ROWS = 0
　　 PRINT '没有打开的游标'
　　OPEN男员工
　　-- 打开游标后，@@CURSOR_ROWSR返回值是当前游标中的总行数
　　IF @@CURSOR_ROWS > 0
　　PRINT @@CURSOR_ROWS
　　GO
执行结果为：
没有打开的游标
6



关闭游标：
　　CLOSE语句的功能是关闭一个打开的游标。关闭游标将完成以下工作：
释放当前结果集。
解除定位于游标行上的游标锁定。
　　不允许在关闭的游标上提取、定位和更新数据，直到游标重新打开为止。CLOSE语句的语法结构如下：
　　CLOSE{ { [ GLOBAL ] cursor_name } |cursor_variable_name}
例：
关闭游标后不能创建同名游标的示例：
　　USE HrSystem
　　GO
　　DECLARE Employee_Cursor2 CURSOR
　　FOR SELECT * FROM Employees WHERE Sex = '男'
　　OPEN Employee_Cursor2
　　CLOSE Employee_Cursor2
　　GO
　　DECLARE Employee_Cursor2 CURSOR
　　FOR SELECT Emp_Name, Title FROM Employees WHERE Sex='男'
　　GO
运行结果为：
　　消息16915，级别16，状态1，第2行
　　名为'Employee_Cursor2'的游标已存在。
　　

　　

获取游标的状态和属性：
使用CURSOR_STATUS函数可以获取指定游标的状态，其基本语法如下：
　　CURSOR_STATUS(<游标类型>, <游标名称或游标变量>)

　　CUdsfsc RSOR_STATUS函数的返回值:
　　

返回值	说明
1	游标的结果集中至少存在一行数据
0	游标的结果集为空
-1	游标被关闭
-2	游标不适用
-3	指定名称的游标不存在

例：使用下面的脚本可以检测声明游标前、打开游标后和关闭游标后游标的状态。
　　USEHrSystem;
　　GO
　　SELECT CURSOR_STATUS('global', 'Cursor1') AS '声明前状态'
　　DECLARE Cursor1 CURSOR FOR
　　SELECT Emp_id FROM Employees ;
　　OPEN Cursor1;
　　SELECT CURSOR_STATUS('global', 'Cursor1') AS '打开状态'
　　CLOSE Cursor1;
　　DEAL LOCATECursor1;
　　SELECT CURSOR_STATUS('global', 'Cursor1') AS '关闭后状态'
　　GO


修改游标结果集中的行：
UPDATE语句可以修改表中数据，也可以和游标相结合，修改当前游标指定的数据，基本语法如下：
　　UPDATE<表名> SET
　　WHERECURRENT OF <游标名>
例：
下面的脚本中可以使用游标来修改表Employees中的姓名为张三的员工记录，将其职务修改为总经理。
　　USEHrSystem;
　　GO
　　DECLARE MyEmpCursor CURSOR FOR
　　SELECT Emp_id FROM Employees
　　WHERE Emp_name = '张三';
　　OPEN MyEmpCursor;
　　FETCH FROM MyEmpCursor;
　　UPDATE Employees SET Title = '总经理'
　　WHERE CURRENT OF MyEmpCursor;
　　CLOSE MyEmpCursor;
　　DEAL LOCATE MyEmpCursor;
　　GO
　　

　　

删除游标结果集中的行：
使用DELETE语句可以删除表中数据，也可以和游标相结合，删除当前游标指定的数据，基本语法如下：
　　DELETEFROM <表名>
　　WHERECURRENT OF <游标名>
例：

下面的脚本中可以使用游标来删除表Employees中的姓名为张三的员工。
　　USEHrSystem;
　　GO
　　DECLARE MyEmpCursor CURSOR FOR
　　SELECT Emp_id FROM Employees
　　WHERE Emp_name = '张三';
　　OPEN MyEmpCursor;
　　FETCH FROM MyEmpCursor;
　　DELETE FROM Employees
　　WHERE CURRENT OF MyEmpCursor;
　　CLOSE MyEmpCursor;
　　DEAL LOCAT EMyEmpCursor;
　　GO
　　

　　

删除游标：
DEALLOCATE语句的功能是删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQL Server释放。
DEALLOCATE语句的语法结构如下：
　　DEALLOCATE{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
例：
如果增加DEAL LOCATE语句，则可以创建新的同名游标。脚本如下：
　　USEHrSystem
　　GO
　　DECLARE Employee_Cursor3 CURSOR
　　FOR SELECT * FROM Employees WHERE Sex = '男'
　　OPEN Employee_Cursor3
　　CLOSE Employee_Cursor3
　　DEAL LOCATE Employee_Cursor3
　　GO
　　DECLARE Employee_Cursor3 CURSOR
　　FOR SELECT Emp_Name, Title FROM Employees WHERE Sex='男'
　　GO
执行此脚本，可以看到在删除游标后，可以创建同名游标。