SQL server 游标与循环 sql server游标的作用

一.游标的实现过程

游标提供了一种从表中检索数据并进行操作的灵活手段，游标主要用在服务器上，处理由客户端发送给服务器端的SQL语句，或是批处理、存储过程、触发器中的数据处理请求。游标的优点在于它可以定位到结果集中的某一行，并可以对该行数据执行特定操作，为用户在处理数据的过程中提供了很大方便。一个完整的游标由5部分组成，并且这5个部分应符合下面的顺序。
（1）声明游标。
（2）打开游标。
（3）从一个游标中查找信息。
（4）关闭游标。
（5）释放游标。

二.游标分类

SQL Server提供了4种类型的游标，静态游标、动态游标、只进游标和键集驱动游标。这些游标检测结果集变化的能力和内存占用的情况都有所不同，数据源没有办法通知游标当前提取行的更改。游标检测这些变化的能力也受事务隔离级别的影响。

1. 静态游标
   静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。静态游标在滚动期间很少或根本检测不到变化，虽然它在tempdb中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。键集驱动游标介于二者之间，它能检测到大部分的变化，但比动态游标消耗更少的资源。
2. 动态游标
   动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中做的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标可见。
3. 只进游标
   只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能进行检索。对所有由当前用户发出或由其他用户提交、并影响结果集中的行的INSERT、UPDATE和DELETE语句，其效果在这些行从游标中提取时是可见的。
4. 键集驱动游标
   打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的惟一标识符（键）控制。键由以惟一方式在结果集中标识行的列构成。键集是游标打开时来自所有适合SELECT语句的行中的一系列键值。键集驱动游标的键集在游标打开时建立在tempdb中。对非键集列中的数据值所做的更改（由游标所有者更改或其他用户提交）在用户滚动游标时是可见的。在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。

三.声明游标

声明游标可以使用DECLARE CURSOR语句。此语句有两种语法声明格式，分别为ISO标准语法和TRANSACT-SQL 扩展的语法，下面将分别介绍声明游标的两种语法格式。
A.ISO标准语法

1. 语法如下：

1. DECLARE CURSOR_NAME [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR SELECT_STATEMENT
FOR { READ ONLY | UPDATE [ OF COLUMN_NAME [ ,…N ] ] } ]

2. 参数说明：
   （1）DECLARE CURSOR_NAME：指定一个游标名称，其游标名称必须符合标识符规则。
   （2）INSENSITIVE：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从TEMPDB中的临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。
   （3）SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果未指定SCROLL，则NEXT是惟一支持的提取选项。
   （4）SELECT_STATEMENT：定义游标结果集的标准SELECT语句。在游标声明的SELECT_STATEMENT内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和INTO。
   （5）READ ONLY：表明不允许游标内的数据被更新，尽管在默认状态下游标是允许更新的。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不允许引用游标。
   （6）UPDATE [ OF COLUMN_NAME [ ,…N ] ]：定义游标内可更新的列。如果指定OF COLUMN_NAME [,…N]参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列

Transact-SQL 扩展的语法

1. 语法如下：

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,…n ] ] ]

四.使用OPEN打开游标

打开一个声明的游标可以使用OPEN命令。

1. 语法如下：

OPEN { { [ GLOBAL ] CURSOR_NAME } | CURSOR_VARIABLE_NAME }

2. 参数说明：
   GLOBAL：指定CURSOR_NAME为全局游标。
   CURSOR_NAME：已声明的游标名称，如果全局游标和局部游标都使用CURSOR_NAME作为其名称，那么如果指定了GLOBAL，CURSOR_NAME指的是全局游标，否则，CURSOR_NAME指的是局部游标。
   CURSOR_VARIABLE_NAME：游标变量的名称，该名称引用一个游标。
   说明：
   如果使用INSENSITIV或STATIC选项声明了游标，那么OPEN将创建一个临时表以保留结果集。如果结果集中任意行的大小超过SQL SERVER表的最大行大小，OPEN将失败。如果使用KEYSET选项声明了游标，那么OPEN将创建一个临时表以保留键集。临时表存储在TEMPDB中。

五.读取游标中的数据

当打开一个游标之后，就可以读取游标中的数据。可以使用FETCH命令读取游标中的某一行数据。

1. 语法如下：

1. FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,…n ] ]

2. FETCH命令的参数及说明。
   
   说明：
   在前两个参数中，包含了n和@nvar其表示游标相对与作为基准的数据行所偏离的位置。

六.关闭游标

当游标使用完毕之后，使用CLOSE语句可以关闭游标，但不释放游标占用的系统资源。

1. 语法如下：

1. CLOSE { { [ GLOBAL ] CURSOR_NAME } | CURSOR_VARIABLE_NAME }

2. 参数说明：
   （1）GLOBAL：指定CURSOR_NAME为全局游标。
   （2）CURSOR_NAME：开放游标的名称。如果全局游标和局部游标都使用CURSOR_NAME作为它们的名称，那么当指定GLOBAL时，CURSOR_NAME引用全局游标；否则，CURSOR_NAME引用局部游标。
   （3）CURSOR_VARIABLE_NAME：与开放游标关联的游标变量名称。
   例：声明一个名为“CLOSECURSOR”的游标，并使用CLOSE语句关闭游标，SQL语句及运行结果如图。

USE DB_2012
DECLARE CLOSECURSOR CURSOR FOR	 --声明游标
SELECT * FROM  STUDENT
FOR READ ONLY		--只读游标
OPEN CLOSECURSOR		--打开游标
CLOSE CLOSECURSOR		--关闭游标

七.释放游标

当游标关闭之后，并没有在内存中释放所占用的系统资源，所以可以使用DEALLOCATE命令删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQL SERVER释放。

1. 语法如下：
   DEALLOCATE { { [ GLOBAL ] CURSOR_NAME } | @CURSOR_VARIABLE_NAME }
2. 参数说明：
   （1）CURSOR_NAME：已声明游标的名称。当全局和局部游标都以CURSOR_NAME作为它们的名称存在时，如果指定GLOBAL，则CURSOR_NAME引用全局游标，如果未指定GLOBAL，则CURSOR_NAME引用局部游标。
   （2）@CURSOR_VARIABLE_NAME：CURSOR变量的名称。@CURSOR_VARIABLE_NAME必须为CURSOR类型。
   （3）当使用DEALLOCATE @CURSOR_VARIABLE_NAME来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程和触发器的范围。
   例：使用DEALLOCATE命令释放名为“FREECURSOR”的游标，SQL语句及运行结果如图。

USE DB_2012
DECLARE FREECURSOR CURSOR FOR	--声明游标
SELECT * FROM STUDENT
OPEN FREECURSOR		--打开游标
CLOSE FREECURSOR		--关闭		
DEALLOCATE FREECURSOR	--释放游标