解读SQL Server游标使用实例_Mssql数据库教程

推荐：在SQL Server中通过.NET远程的执行SQL文件
在项目开发过程中，操作数据库的过程中经常会遇到对表结构、存储过程等进行修改，这时一般的操作都是通过直接在SQL Server中的企业管理器进行操作，或者查询分析器里来执行相关的语句

问题

在我的T-SQL代码中，我经常使用基于集合的运算。我被告知这些运算的类型正是SQL Server设计所处理的，并且它应该比串行处理要快些。我知道游标的存在但是我不确定如何使用它们。您能提供一些游标的例子吗?您能否给一些关于何时使用游标的指导?我假定微软由于某种原因创建了它们，所以它们必须有以高效方式使用的地方。

专家解答

在某些圈子中，游标从未被使用过，在其他圈子中，它们是一种最后手段，而在其它组中它们经常被使用。在每个组中，它们由于不同原因而使用游标。无论你对游标使用原因有什么样的立场，可能它们在一个特定环境中具有用武之地而在另一个环境中却没有用处。所以它归结于你对这个技术的理解，然后归结于随及而来对问题的理解导致做出一个决定，这个决定就是基于游标的过程是否恰当。让我们先来做以下几件事情：

l 看一个游标实例

l 分解游标的组成部分

l 分析使用游标的优缺点

游标实例

以下是一个游标实例，讲述了备份在串行状态下如何执行。

以下为引用的内容： 　　DECLARE@nameVARCHAR(50)--databasename 　　DECLARE@pathVARCHAR(256)--pathforbackupfiles 　　DECLARE@fileNameVARCHAR(256)--filenameforbackup 　　DECLARE@fileDateVARCHAR(20)--usedforfilename 　　SET@path='C:Backup' 　　SELECT@fileDate=CONVERT(VARCHAR(20),GETDATE(),112) 　　DECLAREdb_cursorCURSORFOR 　　SELECTname 　　FROMmaster.dbo.sysdatabases 　　WHEREnameNOTIN('master','model','msdb','tempdb') 　　OPENdb_cursor 　　FETCHNEXTFROMdb_cursorINTO@name 　　WHILE@@FETCH_STATUS=0 　　BEGIN 　　SET@fileName=@path @name '_' @fileDate '.BAK' 　　BACKUPDATABASE@nameTODISK=@fileName 　　FETCHNEXTFROMdb_cursorINTO@name 　　END 　　CLOSEdb_cursor 　　DEALLOCATEdb_cursor

 

游标组成

根据以上的实例，游标包括这些组成部分：

l DECLARE语句 – 声明在代码块中使用的变量

l SETSELECT语句 – 把这些变量初始化为某个具体的值

l DECLARE CURSOR语句 – 在游标中填充将要求取的值。

注意 – DECLARE CURSOR FOR语句和SELECT语句中具有相同数量的变量。变量数目可能是一个或者很多个并且有相关联的列。

l OPEN语句 – 打开游标，开始进行数据处理

l FETCH NEXT语句 – 从游标中把具体的值分配给变量

注意： 这个逻辑用于WHILE语句之前的初始对象，接着作为WHILE语句的一部分在这个过程的每个循环中再次执行。

l WHILE语句 – 作为开始的条件，继续处理数据

l BEGIN...END语句 – 代码块的开始和结束

注意：按照不同的数据处理，可以使用不同的BEGIN...END语句

l 数据处理 – 在这个例子中，逻辑是用来把一个数据库备份到具体的路径和文件名，但是这可以只与任何DML或者管理逻辑有关。

l CLOSE语句 – 释放当前的数据和相关联的锁，但是允许游标重新打开。

l DEALLOCATE语句 – 破坏游标

游标分析

以下分析旨在更深入了解基于游标的逻辑是否有利：

l 联机事务处理(OLTP)- 在大多数的联机事务处理环境中，基于集合的逻辑适合简短的事务。我们的团队已经遇到第三方应用，这个应用在它所有的过程中使用游标，这将导致一些问题，但这是一个很罕见的现象。通常情况下，基于集合的逻辑足够灵活而且游标很少被用到。

l 报表 – 基于报表的设计和潜在设计，通常情况下游标是不需要的。尽管如此，我们的团队遇到报表要求，而参照完整性没有存在于潜在的数据库中，同时需要使用游标来正确计算报表值。当需要下游过程的合计数据时，我们面临相同的状况，一个基于游标的方法可以用一种可接受的方式快速形成和执行来满足要求。

 

l 串行化过程 – 如果你需要以串行化方式完成一个过程，那么游标是一个可行的选项。

l 管理员任务 – 很多管理员任务需要在串行状态下执行，这与基于游标的逻辑很相符，但是基于其他系统的对象存在来满足这种需要。在某些情况下，游标用来完成这一进程。

l 大型数据集 – 在大型数据集的情况下，你可能会遇到以下任何一种情况：

基于游标的逻辑可能没有足够大的规模满足需求。

在服务器上具有大型数据集的运算而内存又很小，数据可能会被分页或独占SQL Server，这将是很费时间的，可能会导致争夺和内存的问题。

有些固有的工具把数据存储到一个隐藏的文件中，所以在内存中处理数据可能会也可能不会是实际的情况。

如果数据能够在一个中间数据库中处理，那么对生产环境的影响只有在最后数据被处理时，所以服务器上所有的资源可用于ETL过程，然后引入最后的数据。

SSIS支持批处理数据，这将解决把大量数据分解成更多可管理的大小和比用按行方法执行效果更好的总体需求。

根据游标或者SSIS逻辑如何编码，它可能基于检查点或者在游标的每一行做标记来在失败时重新启动。尽管如此，用可能并非事实的基于集合的方法直到获得数据的整个集合是完整的。同样地，解决行的问题会更加困难。

游标的替代

下面列出了可以替代能够满足相同需求的基于游标的逻辑：

l 基于集合的逻辑

l SSIS或者数据转换服务

l WHILE循环

l COALSCE

l sp_MSforeachdb

l sp_MSforeachtable

l CASE表达

l 用GO命令重复一批数据

分享：解析Microsoft SQL Server数据库日志截断
参考 压缩日志及数据库文件大小 /*--注意事项 请按步骤进行,未进行前面的步骤,请不要做后面的步骤，否则可能损坏你的数据库. 一般情况下不建议做第4,6两步。 第4步不安全,