SQL Server 中游标的基本操作

对于游标的基本操作主要有以下内容：

声明游标

打开游标

读取游标数据

关闭游标

获取游标的状态和属性

修改游标结果集中的行

删除游标结果集中的行

删除游标


声明游标：

        可以使用 DECLARE CURSOR 语句来声明 Transact-SQL 服务器游标和定义游标的特性，例如游标的滚动行为和结果集的查询方式等。 DECLARE CURSOR 的语法结构如下：

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

执行此脚本，可以看到在删除游标后，可以创建同名游标。