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

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值