实验27 使用游标

实验27 使用游标

27.1目标

 理解并能简单使用游标

27.2背景知识

由SELECT语句返回的行集包括满足该语句的WHERE子句中条件的所有行，称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行，游标提供了这种机制。

27.3实验内容

1.游标的功能

游标通过以下方式来扩展对结果集的处理能力：

 允许在结果集中定位特定行

 从结果集的当前位置检索一行或一部分行  支持对结果集中当前位置的行进行数据修改

 为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持

2.T_SQL游标的类型

基于数据库服务器的DECLARE CURSOR创建的T_SQL游标主要用于T_SQL脚本、存储过程和触发器。SQL Server支持四种API服务器游标类型：静态游标、动态游标、只进游标、由键集驱动的游标。T-SQL 游标类型主要由DECLARE CURSOR命令定义时指定不同的选项决定，下面是该命令的语法：

DECLARE cursor_name CURSOR [LOCAL|GLOBAL]

[FORWARD_ONLY|SCROLL]

[STATIC|KEYSET|DYNAMIC|FAST_FORWARD] [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]

[TYPE_WARNING]

FOR select_statement

[FOR UPDATE [ OF column_name[,...n]]][;] 这些游标检测结果集变化的能力和消耗资源（如在tempdb中所占的内存和空间）的情况各不相同。游标检测这些变化的能力也受事务隔离级别的影响。静态游标在滚动期间很少或根本检测不到变化，消耗的资源相对较少；动态游标在滚动期间能检测到所有变化，但消耗的资源也较多；由键集驱动的游标介于二者之间。

3.在脚本、存储过程或触发器中使用游标

在脚本、存储过程或触发器中使用T-SQL游标的典型过程为：

（1）声明T-SQL变量包含游标返回的数据。为每个结果集列声明一个变量。声明足够

大的变量来保存列返回的值，并声明变量的类型为可从列数据类型隐式转换得到的数据类型。

（2）使用DECLARE CURSOR语句将T-SQL游标与 SELECT 语句相关联。另外，DECLARE CURSOR语句还定义游标的特性。

（3）使用OPEN语句执行SELECT语句并填充游标。 （4）使用FETCH INTO语句提取单个行，并将每列中的数据移至指定的变量中。然后，其他 T-SQL 语句可以引用那些变量来访问提取的数据值。T-SQL 游标不支持提取行块。

（5）使用CLOSE语句结束游标的使用。关闭游标可以释放某些资源，例如游标结果集及其对当前行的锁定，但如果重新发出一个OPEN语句，则该游标结构仍可用于处理。由于游标仍然存在，此时还不能重新使用该游标的名称。DEALLOCATE语句则完全释放分配给游标的资源，包括游标名称。释放游标后，必须使用DECLARE语句来重新生成游标。

4.在脚本中使用游标示例

USE AdventureWorks

SET NOCOUNT ON

DECLARE @vendor_id int, @vendor_name nvarchar(50),@message varchar(80), @product nvarchar(50)

PRINT '-------- Vendor Products Report --------' DECLARE vendor_cursor CURSOR FOR

SELECT VendorID, Name FROM Purchasing.Vendor

WHERE PreferredVendorStatus = 1 ORDER BY VendorID OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN

PRINT ' ' SELECT @message = '----- Products From Vendor: ' +@vendor_name PRINT @message -- Declare an inner cursor based on vendor_id from the outer cursor.

DECLARE product_cursor CURSOR FOR

SELECT v.Name

FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID =

@vendor_id

OPEN product_cursor

FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS <> 0 PRINT ' <>' WHILE @@FETCH_STATUS = 0 BEGIN

SELECT @message = ' ' + @product;PRINT @message; FETCH NEXT FROM product_cursor INTO @product END

CLOSE product_cursor

DEALLOCATE product_cursor -- Get the next vendor.

FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name END

CLOSE vendor_cursor DEALLOCATE vendor_cursor