SQL数据库开发--TSQL—10第十章游标

SQL数据库开发--TSQL—10第十章游标

10.1 游标概述

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

游标通过以下方式来扩展结果处理：

允许定位在结果集的特定行。

从结果集的当前位置检索一行或一部分行。

支持对结果集中当前位置的行进行数据修改。

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

提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句。

 

游标是面向行的，它会使开发人员变懒，懒得去想用面向集合的查询方式实现某些功能。
　　在性能上，游标会迟更多的内存，减少可用的并发，占用带宽，锁定资源，当然还有更多的代码量。
　　用一个比喻来说明为什么游标会占用更多的资源。当你从ATM机取款的时候，是一次取1000的效率更高呢，还是10次100呢？

10.2 认识游标

游标是 SQL Server的一种数据访问机制，它允许用户访问单独的数据行。用户可以对每一行进行单独的处理，从而降低系统开销和潜在的阻隔情况，用户也可以使用这些数据生成的SQL代码并立即执行或输出。

1.游标的概念

游标是一种处理数据的方法，主要用于存储过程，触发器和  T_SQL脚本中，它们使结果集的内容可用于其它T_SQL语句。在查看或处理结果集中向前或向后浏览数据的功能。类似与C语言中的指针，它可以指向结果集中的任意位置，当要对结果集进行逐条单独处理时，必须声明一个指向该结果集中的游标变量。

 SQL Server 中的数据操作结果都是面向集合的，并没有一种描述表中单一记录的表达形式，除非使用WHERE子句限定查询结果，使用游标可以提供这种功能，并且游标的使用和操作过程更加灵活、高效。

2.游标的优点

 SELECT 语句返回的是一个结果集，但有时候应用程序并不总是能对整个结果集进行有效地处理，游标便提供了这样一种机制，它能从包括多条记录的结果集中每次提取一条记录，游标总是与一跳SQL选择语句相关联，由结果集和指向特定记录的游标位置组成。使用游标具有一下优点：

(1).允许程序对由SELECT查询语句返回的行集中的每一次执行相同或不同的操作，而不是对整个集合执行同一个操作。

(2).提供对基于游标位置中的行进行删除和更新的能力。

(3).游标作为数据库管理系统和应用程序设计之间的桥梁，将两种处理方式连接起来。

3.游标的分类

  SQL Server支持3中游标实现：

(1).Transact_SQL游标

基于  DECLARE CURSOR 语法，主要用于T_SQL脚本，存储过程和触发器。T_SQL游标在服务器上实现，并由从客户端发送到服务器的T_SQL语句管理，它们还可能包含在批处理，存储过程或触发器中。

(2).应用程序编程接口(API)服务器游标

支持  OLE DB和ODBC中的API游标函数，API服务器游标在服务器上实现。每次客户端应用程序调用API游标函数时，SQL Server Native Client OLE DB访问接口或ODBC驱动程序会把请求传输到服务器，以便对API服务器游标进行操作。

(3).客户端游标

由  SQL Server Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。客户端游标通过在客户端高速缓存所有结果集中的行来实现。每次客户端应用程序调用API游标函数时，SQL Server Native Client ODBC驱动程序或ADO DLL会对客户端上告诉缓存的结果集中的行执行游标操作。

由于T_SQL游标和服务器游标都在服务器上实现，所以它们统称为服务器游标。

10.3 T-SQL中游标的生命周期及使用

游标的生命周期通常为5个部分

1 定义游标，声明游标。通过 DECLARE CURSOR 参数进行指定。

2 打开游标，声明游标打开cursor_name。

3 使用游标

3.1 提取行    FETCH  SQLFetch或SQLFetchScroll

3.2 定位的更新   UPDATE 或 DELETE 中的 WHERE CURRENT OF 子句   SQLSetPos

4 关闭游标，关闭cursor_name DEALLOCATE。

5 释放游标。

10.3.1 定义游标

定义 Transact-SQL 服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。 DECLARE CURSOR 既接受基于 ISO 标准的语法，也接受使用一组 Transact-SQL 扩展的语法。

1 语法

ISO Syntax 

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR  

     FOR select_statement  

     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ] 

[;] 

Transact-SQL Extended Syntax 

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 ] ] ] 

[;]

2 参数说明

cursor_name:是所定义的T_SQL 服务器游标的名称。

LOCAL：对于在其中创建批处理、存储过程或触发器来说，该游标的作用域是局部的。

GLOBAL：指定该游标的作用域是全局的

FORWARD_ONLY:指定游标只能从第一行滚动到最后一行。FETCH NEXT是唯一支持的提取选项，如果在指定FORWARD_ONLY时不指定STATIC，KEYSET和DYNAMIC关键字，则游标作为DYNAMIC游标进行操作，如果FORWARD_ONLY和SCROLL均为指定，则除非指定STATIC，KEYSET和DYNAMIC关键字，否则默认为FORWARD_ONLY。STATIC，KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同，STATIC，KEYSET和DYNAMIC T_SQL游标支持FORWARD_ONLY。

STATIC：定义一个游标，以创建将又该游标使用的数据临时复本，对游标的所有请求都从tempdb中的这以临时表中不得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

KEYSET:指定当游标打开时，游标重的行的成员身份和顺序已经固定。对行进行唯一标识的键值内置在tempdb内一个称为keyset的表中。

DYNAMIC:定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改，动态游标不支持ABSOLUTE提取选项。

FAST_FORWARD：指定启动了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE,则不能指定FAST_FORWARD。

SCROLL_LOCKS：指定通过游标进行的定位更新或删除一定会成功。将行读入游标时SQL Server将锁定这些行，以确保随后可对它们进行修改，如果还指定了FAST_FORWARD或STATIC，则不能指定SCROLL_LOCKS。

OPTIMISTIC：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，SQL Server不锁定行，它改用timestamp列值比较结果来确定行读入游标后是否发生了修改，如果表不包含timestamp列，它改用校验和值进行确定，如果以修改该行，则尝试进行的定位更新或删除将失败，如果还指定了FAST_FORWARD，则不能指定OPTIMISTIC。

TYPE_WARNING:指定游标从所请求的类型隐式转换为另一种类型时，向客户端发送警告消息。

select_statement：是定义游标结果集中的标准SELECT语句。

3 举例

自己举例： DECLARE  authors_cursor  CURSOR FOR  Select [name]   from sysobjects  wher e  xtype='u'  order  by  id

官方举例：DECLARE vendor_cursor CURSOR FOR  

SELECT  VendorID, Name 

FROM  Purchasing.Vendor 

WHERE PreferredVendorStatus = 1 

ORDER BY VendorID; 

10.3.2 打开游标

打开 Transact-SQL 服务器游标，然后通过执行在 DECLARE CURSOR 或 SET cursor_variable 语句中指定的 Transact-SQL 语句填充游标。

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name } 

 

DECLARE Employee_Cursor CURSOR FOR 

SELECT LastName, FirstName 

FROM AdventureWorks2012.HumanResources.vEmployee 

WHERE LastName like 'B%'; 

 

OPEN Employee_Cursor; 

 

FETCH NEXT FROM Employee_Cursor; 

WHILE @@FETCH_STATUS = 0 

BEGIN 

    FETCH NEXT FROM Employee_Cursor 

END; 

 

CLOSE Employee_Cursor; 

DEALLOCATE Employee_Cursor; 

10.3.3 使用游标

1 语法

FETCH  

          [ [ NEXT | PRIOR | FIRST | LAST  

                    | ABSOLUTE { n | @nvar }  

                    | RELATIVE { n | @nvar }  

               ]  

               FROM  

          ]  

{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }  

[ INTO @variable_name [ ,...n ] ]  

2 参数说明

参数

NEXT
紧跟当前行返回结果行，并且当前行递增为返回行。 如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。 NEXT 为默认的游标提取选项。

PRIOR
返回紧邻当前行前面的结果行，并且当前行递减为返回行。 如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

FIRST
返回游标中的第一行并将其作为当前行。

LAST
返回游标中的最后一行并将其作为当前行。

ABSOLUTE { n| @nvar}
如果 n 或 @nvar 为正，则返回从游标起始处开始向后的第 n 行，并将返回行变成新的当前行。 如果 n 或 @nvar 为负，则返回从游标末尾处开始向前的第 n 行，并将返回行变成新的当前行。 如果 n 或 @nvar 为 0，则不返回行。 n 必须是整数常量，并且 @nvar 必须是 smallint、tinyint 或 int。

RELATIVE { n| @nvar}
如果 n 或 @nvar 为正，则返回从当前行开始向后的第 n 行，并将返回行变成新的当前行。 如果 n 或 @nvar 为负，则返回从当前行开始向前的第 n 行，并将返回行变成新的当前行。 如果 n 或 @nvar 为 0，则返回当前行。 在对游标进行第一次提取时，如果在将 n 或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。 n 必须是整数常量，并且 @nvar 必须是 smallint、tinyint 或 int。

GLOBAL
指定 cursor_name 是指全局游标。

cursor_name
要从中进行提取的开放游标的名称。 当同时存在以 cursor_name 作为名称的全局游标和局部游标时，如果指定 GLOBAL，则 cursor_name 指全局游标，如果未指定 GLOBAL，则指局部游标。

@cursor_variable_name
游标变量名，引用要从中进行提取操作的打开的游标。

INTO @variable_name[ ,...n]
允许将提取操作的列数据放到局部变量中。 列表中的各个变量从左到右与游标结果集中的相应列相关联。 各变量的数据类型必须与相应的结果集列的数据类型匹配，或是结果集列数据类型所支持的隐式转换。 变量的数目必须与游标选择列表中的列数一致。

3 示例

USE AdventureWorks2012; 

GO 

-- Declare the variables to store the values returned by FETCH. 

DECLARE @LastName varchar(50), @FirstName varchar(50);  

 

DECLARE contact_cursor CURSOR FOR 

SELECT LastName, FirstName FROM Person.Person 

WHERE LastName LIKE 'B%' 

ORDER BY LastName, FirstName; 

 

OPEN contact_cursor; 

 

-- Perform the first fetch and store the values in variables. 

-- Note: The variables are in the same order as the columns 

-- in the SELECT statement.  

 

FETCH NEXT FROM contact_cursor 

INTO @LastName, @FirstName; 

 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch. 

WHILE  @@FETCH_STATUS = 0 

BEGIN 

 

   -- Concatenate and display the current values in the variables. 

   PRINT 'Contact Name: ' + @FirstName + ' ' +  @LastName 

 

   -- This is executed as long as the previous fetch succeeds. 

   FETCH NEXT FROM contact_cursor 

   INTO @LastName, @FirstName; 

END 

 

CLOSE contact_cursor; 

DEALLOCATE contact_cursor; 

GO 

10.3.4释放游标

释放当前结果集，然后解除定位游标的行上的游标锁定，从而关闭一个开放的游标。 CLOSE 将保留数据结构以便重新打开，但在重新打开游标之前，不允许提取和定位更新。 必须对打开的游标发布 CLOSE；不允许对仅声明或已关闭的游标执行 CLOSE。

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name } 

CLOSE Employee_Cursor; 

DEALLOCATE Employee_Cursor; 

10.3.5 关闭游标-删除游标

删除游标引用。 当释放最后的游标引用时，组成该游标的数据结构由 Microsoft SQL Server 释放。

EALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name } 

cursor_name
已声明游标的名称。 当同时存在以 cursor_name 作为名称的全局游标和局部游标时，如果指定 GLOBAL，则 cursor_name 指全局游标，如果未指定 GLOBAL，则指局部游标。

@cursor_variable_name
cursor 变量的名称。 @cursor_variable_name 必须为 cursor 类型。

DEALLOCATE @MyCursor;

10.4 应用游标-示例

10.4.1 重建所有表的索引

　　　USE T_BRANCH;

DECLARE @tablename varchar(100)

DECLARE @command nvarchar(4000)

 

　　　　DECLARE authors_cursor CURSOR FOR  Select [name]   from sysobjects where xtype='u' order by id

 

　　　　OPEN authors_cursor

 

　　　　FETCH NEXT FROM authors_cursor  INTO @tablename

 

　　　　WHILE @@FETCH_STATUS = 0

BEGIN   

   　   SET @command = N'ALTER INDEX ' + N' ALL ' + N' ON '  + @tablename + N' REBUILD';

         EXEC (@command);

        PRINT N'Executed: ' + @command;

   　　　FETCH NEXT FROM authors_cursor     INTO @tablename

END

        CLOSE authors_cursor

　　　 deallocate authors_cursor

10.4.2 查看所有表的行数与大小

create table #tabledb(

tabname varchar(100),

rowsNum varchar(100),

reserved varchar(100),

data varchar(100),

index_size varchar(100),

unused_size varchar(100)

)

 

declare @name varchar(100)

DECLARE tablecu CURSOR   FOR select name from sysobjects where xtype='u' order by name

open tablecu

FETCH  NEXT     FROM   tablecu into @name

while @@fetch_status=0

begin

    insert into #tabledb

    exec sp_spaceused @name

    FETCH  NEXT     FROM   tablecu into @name

end

 

 

 

select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'

from #tabledb