SQL Server 中流标的基本操作

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 HrSystemGODECLARE Employee_Cursor CURSORFOR 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

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

查看更多 下一篇