SQL sever2008中的游标

﹏ヽ暗。殇╰゛Y 2024-02-17 08:58 33阅读 0赞

**目录**

一、游标概述

二、游标的实现

三、优缺点

3.1优点：

3.2缺点：

四、游标类型

4.1静态游标

4.2动态游标

4.3只进游标

4.4键集驱动游标

4.5显示游标：

4.6隐式游标

五、游标基本操作

5.1声明游标

5.1.1.IS0标准语法

5.1.1.1语法格式如下：

5.1.1.2示例应用：

5.1.2.Transact-.SQL扩展的语法

5.1.2.1语法格式如下：

5.1.2.2示例应用

5.2打开游标

5.2.1语法格式如下：

5.2.2示例应用：

5.3读取游标中数据

5.3.1语法格式如下：

5.3.2示例应用

5.4关闭游标

5.4.1语法格式如下：

5.4.2示例应用

5.5释放游标

5.5.1语法格式如下：

5.5.2示例应用

六、游标查看

6.1sp\_cursor\_list

6.1.1语法格式如下：

6.1.2示例应用

6.2sp\_describe\_cursor

6.2.1语法格式如下：

6.2.2示例应用

七、隐式与显示游标

7.1显示游标

7.1.1语法格式：

7.1.2示例应用

7.2隐式游标

7.2.1语法格式：

7.2.2示例应用

--------------------

## 一、游标概述 ##

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

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

*  允许定位在结果集的特定行。
 *  从结果集的当前位置检索一行或一部分行。
 *  支持对结果集中当前位置的行进行数据修改。
 *  为由其他用户对显示在结果集中的数据库数据所作的更改提供不同级别的可见性支持。
 *  提供脚本、存储过程和触发器中用于访问结果集中的数据的Transact-SQL语句。

**游标（Cursor）是一种用于在 SQL Server 数据库中遍历查询结果集的机制。它允许逐行处理查询结果，类似于一个指针或迭代器，可以通过游标控制在结果集中移动**。使用游标可以逐行处理数据，并提供更高级别的操作。

## 二、游标的实现 ##

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

*  **(1)声明游标。**
 *  **(2)打开游标。**
 *  **(3)从一个游标中查找信息。**
 *  **(4)关闭游标。**
 *  **(5)释放游标。**

## 三、优缺点 ##

### 3.1优点： ###

*  游标可以处理复杂的查询逻辑和业务流程，使得数据处理更加灵活。
 *  允许逐行处理查询结果集，对每一行进行特定的操作。允许对查询结果集进行自定义的操作、更新和删除。
 *  可以在结果集中进行随意导航和遍历。
 *  允许在不同的行之间跳跃，以满足特定需求。

### 3.2缺点： ###

*  使用游标需要占用额外的资源和内存，在处理大型数据集时可能会导致性能问题。
 *  游标通常比集合操作的性能较差，因为它们需要逐行处理数据。
 *  错误的使用游标可能导致死锁或性能瓶颈。

## 四、游标类型 ##

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

### 4.1静态游标 ###

静态游标的完整结果集在游标打开时建立在tempdb中。静态游标总是按照游标打开时的原样显示结果集。静态游标在滚动期间很少或根本检测不到变化，虽然它在tempdb中存储了整个游标，但消耗的资源很少。尽管动态游标使用tempdb的程度最低，在滚动期间它能够检测到所有变化，但消耗的资源也更多。键集驱动游标介于二者之间，它能检测到大部分的变化，但比动态游标消耗更少的资源。这意味着在游标打开时，它会被缓存，并且**不会受到对源数据的更改的影响**。

### 4.2动态游标 ###

动态游标与静态游标相对。当滚动游标时，动态游标反映结果集中所作的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会改变。所有用户做的全部UPDATE、NSERT和DELETE语句均通过游标可见。这意味着当**使用动态游标时，结果集可能会发生变化**。

### 4.3只进游标 ###

**只进游标不支持滚动，它只支持游标从头到尾顺序提取。只在从数据库中提取出来后才能进行检索**。对所有由当前用户发出或由其他用户提交，并影响结果集中的行的NSERT、UPDATE和DELETE语句，其效果在这些行从游标中提取时是可见的。可以使用 `FORWARD_ONLY` 游标类型创建只进游标。

### 4.4键集驱动游标 ###

打开游标时，键集驱动游标中的成员和行顺序是固定的。键集驱动游标由一套被称为键集的唯一标识符（键）控制。键由以唯一方式在结果集中标识行的列构成。键集是游标打开时来自所有适合SELECT语句的行中的一系列键值，键集驱动游标的键集在游标打开时建立在tempdb中。对非键集列中的数据值所做的更改（由游标所有者更改或其他用户提交）在用户滚动游标时是可见的，在游标外对数据库所做的插入在游标内是不可见的，除非关闭并重新打开游标。

> 游标同样是可以分为显示游标（Explicit Cursor）和隐式游标（Implicit Cursor）。

### 4.5显示游标： ###

显示游标是通过使用 `DECLARE CURSOR` 语句显式声明的游标。可以指定游标的名称、类型以及其他属性，例如数据访问方式、是否可滚动、是否只读等。显示游标需要手动控制其生命周期，包括打开、关闭和获取结果集中的行。详细用法查看第七节内容。

### 4.6隐式游标 ###

隐式游标是在特定的 SQL 语句中隐含地创建的游标。它通常与 `SELECT` 语句一起使用，在执行SELECT语句时自动创建一个默认游标。即当查询返回结果集时，隐式游标会自动被创建和管理。隐式游标无需手动声明或控制，系统会自动处理游标的生命周期，并在需要时进行打开、遍历和关闭。 隐式声明游标是一种简单的方式，它这种方式在处理较简单的游标需求时比较方便。详细用法查看第七节内容。

## 五、游标基本操作 ##

### 5.1声明游标 ###

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

#### 5.1.1.IS0标准语法 ####

##### **5.1.1.1语法格式如下：** #####

DECLARE cursor_name [ INSENSITIVE ][ SCROLL ] CURSOR
    FOR select_statement
    FOR {READ ONLY | UPDATE [ OF column_name [,...n ]]}

**参数说明：**

*  **DECLARE cursor\_name**:指定一个游标名称，其游标名称必须符合标识符规则。
 *  **NSENSITIVE**:定义一个游标，以创建将由该游标使用的数据的临时副本。对游标的所有请求都从tempdb中的临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所作的修改，并且该游标不允许修改。使用SQL-92语法时，如果省略NSENSITIVE,(任何用户)对基表提交的删除和更新都反映在后面的提取中。
 *  **SCROLL**:指定所有的提取选项(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)均可用。

>  *  FRST:取第一行数据。
>  *  LAST:取最后一行数据
>  *  PRIOR:取前一行数据。
>  *  NEXT:取后一行数据。
>  *  RELATIVE:按相对位置取数据。
>  *  ABSOLUTE:按绝对位置取数据。
> 
> 如果未指定SCROLL,则NEXT是唯一支持的提取选项。

*  **select\_statement**:定义游标结果集的标准SELECT语句。在游标声明的select statement内不允许使用关键字COMPUTE、COMPUTE BY、FOR BROWSE和NTO.
 *  **READ ONLY**:表明不允许游标内的数据被更新，尽管在默认状态下游标是允许更新的。在UPDATE或DELETE语句的WHERE CURRENT OF子句中不允许引用游标。
 *  **UPDATE\[OF column\_name\[,n\]\]**:定义游标内可更新的列.如果指定OF column\_name\[,n\]参数，则只允许修改所列出的列。如果在UPDATE中未指定列的列表，则可以更新所有列。

##### **5.1.1.2示例应用：** #####

以下是声明游标的部分示例：

EMP表数据：

![12ed07cb4d6b42a2a10f7c4827c90f94.png][]

**【例1】创建一个名为Cur\_Emp的标准游标。SQL语句如下：**

USE test;
    DECLARE Cur_Emp CURSOR FOR
    SELECT * FROM Emp
    Go

**【例2】创建一个名为Cur\_Emp01的只读游标。SQL语句如下：**

USE test 
    DECLARE Cur_Emp_01 CURSOR FOR
    SELECT * FROM Emp
    FOR READ ONLY --只读游标
    Go

**【例3】创建一个名为Cur\_Emp\_02的更新游标,默认更新所有列。SQL语句如下：**

USE test
    DECLARE Cur_Emp_02 CURSOR FOR
    SELECT Ename,Eage,Esal FROM Emp
    FOR UPDATE --更新游标
    GO

**【例4】创建一个名为Cur\_Emp\_03的更新游标，指定更新的列。SQL语句如下：**

USE test
    DECLARE Cur_Emp_03 CURSOR FOR
    SELECT Ename,Eage,Esal FROM Emp
    FOR UPDATE OF Ename --更新游标,允许对 Ename 列进行更新操作
    GO

#### 5.1.2.Transact-.SQL扩展的语法 ####

##### **5.1.2.1语法格式如下**： #####

**DECLARE CURSOR语句的参数及说明如下所示**：

*  **DECLARE cursor\_name**：指定一个游标名称，其游标名称必须符合标识符规则；
 *  **LOCAL**：定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。当建立游标在存储过程执行结束后，游标会被自动释放；
 *  **GLOBAL**：指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放；
 *  F**ORWARD\_ONLY**：指定游标只能从第一行滚动到最后一行。FETCH NEXT 是唯一受支持的提取选项，非指定STATIC、KEYSET 或DYNAMIC 关键字否则默认为 FORWARD\_ONLY。STATIC、KEYSET 和DYNAMIC游标默认为 SCROLL。与ODBC 和ADO 这类数据库 API 不同，STATIC、KEYSET 和 DYNAMICTransact-SOL游标支持FORWARD\_ONLY。FAST\_FORWARD和FORWARD\_ONLY 是互斥的，如果指定一个，则不能指定另一个；
 *  **STATIC**：定义一个游标，以创建将由该游标使用的数据的临时副本。对游标的所有请求都从tempdb中的该临时表中得到应答：因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改；
 *  **KEYSET**：指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行唯一标识的键集内置在tempdb内一个称为keyset的表中。对基表中的非键值所做的更改（由游标所有者更改或由其他用户提交)在用户滚动游标时是可视的。其他用户进行的插入是不可视的（不能通过Transact-SQL服务器游标进行插入)。如果某行已别除，则对该行的提取操作将返回@@FETCH STATUS值-2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视，对含有旧值的行的提取操作将返回@@FETCH STATUS值-2。如果通过指定WHERE CURRENT OF子句用游标完成更新，则新值可视；
 *  **DYNAMIC**：定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据的更改。行的数据值、顺序和成员在每次提取时都会更改。动态游标不支持ABSOLUTE提取选项；
 *  **FAST\_FORWARD**：指明一个FORWARD\_ONLY、READ\_ONLY型游标；
 *  **SCROLL\_LOCKS**：指定确保通过游标完成的定位更新或定位删除可以成功。将行读入游标以确保它们可用于以后的修改时，SQL Server会锁定这些行。如果还指定了FAST\_FORWARD,则不能指定SCROLL\_LOCKS；
 *  **OPTIMISTIC**：指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败；
 *  **TYPE\_WARNING**：指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息；

##### 5.1.2.2示例应用 #####

以下是声明游标的部分示例：

**【例1】局部只进只读游标**

DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY READ_ONLY
    FOR SELECT column1, column2 FROM table_name;

示例声明一个局部的只进只读游标，它从 `table_name` 表中选择 `column1` 和 `column2` 列，并且只能以向前遍历的方式访问结果集。

**【例2】全局可滚动可更新游标**

DECLARE cursor_name CURSOR GLOBAL SCROLL 
    FOR UPDATE OF column1, column2
    FOR SELECT column1, column2 FROM table_name;

示例声明一个全局的可滚动游标，它从 `table_name` 表中选择 `column1` 和 `column2` 列，并允许对这两列进行更新操作。

**【例3】静态只读游标**

DECLARE cursor_name CURSOR STATIC READ_ONLY
    FOR SELECT * FROM table_name;

示例声明一个静态只读游标，它从 `table_name` 表中选择所有列，并且只能以向前遍历的方式访问结果集。

**【例4】动态可滚动乐观锁游标**

DECLARE cursor_name CURSOR DYNAMIC SCROLL OPTIMISTIC
    FOR SELECT column1, column2 FROM table_name
    FOR UPDATE OF column1;

示例声明一个动态可滚动乐观锁游标，它从 `table_name` 表中选择 `column1` 和 `column2` 列，并且允许对 `column1` 列进行更新操作。

**【例5】局部快速向前仅警告游标**

DECLARE cursor_name CURSOR LOCAL FAST_FORWARD TYPE_WARNING
    FOR SELECT column1, column2 FROM table_name
    FOR UPDATE OF column1, column2;

示例声明一个局部快速向前仅警告游标，它从 `table_name` 表中选择 `column1` 和 `column2` 列，并且允许对这两列进行更新操作 .

**【例6】静态可滚动游标**

DECLARE cursor_name CURSOR STATIC SCROLL 
    FOR SELECT column1, column2 FROM table_name;

示例声明一个静态可滚动游标，从 `table_name` 表中选择 `column1` 和 `column2` 列。

**【例7】键集游标**

DECLARE cursor_name CURSOR KEYSET 
    FOR SELECT column1, column2 FROM table_name;

示例声明一个键集游标，从 `table_name` 表中选择 `column1` 和 `column2` 列。键集游标在打开时获取结果集的键值，并根据这些键值定位结果集中的行。

**【例8】动态只进游标**

DECLARE cursor_name CURSOR DYNAMIC FORWARD_ONLY 
    FOR SELECT column1, column2 FROM table_name;

示例声明一个动态只进游标，从 `table_name` 表中选择 `column1` 和 `column2` 列。动态只进游标每次都会重新执行查询，而不是缓存结果集。

**【例9】全局并发锁游标**

DECLARE cursor_name CURSOR GLOBAL SCROLL SCROLL_LOCKS 
    FOR SELECT column1, column2 FROM table_name;

示例声明一个全局并发锁游标，从 `table_name` 表中选择 `column1` 和 `column2` 列。全局并发锁游标允许其他事务以共享或排他方式访问相同的数据行。

**【例10】局部快速向前仅警告游标**

DECLARE cursor_name CURSOR LOCAL FAST_FORWARD TYPE_WARNING
    FOR SELECT column1, column2 FROM table_name;

示例声明一个局部快速向前仅警告游标，从 `table_name` 表中选择 `column1` 和 `column2` 列。该游标以快速向前的方式遍历结果集，但在某些情况下会生成警告消息。

> **注意：**
> 
> 一定要根据自己的数据库结构和需求来编写适合的 SELECT 语句和游标名称。
> 
> 同时，还应该根据具体情况调整是否需要 LOCAL 或 GLOBAL、FORWARD\_ONLY 或 SCROLL、STATIC、KEYSET、DYNAMIC 或 FAST\_FORWARD 等选项。

### 5.2打开游标 ###

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

#### 5.2.1语法格式如下： ####

OPEN{
       {[GLOBAL ] cursor_name } | cursor_variable_name}

**参数说明**：

*  **GLOBAL**:指定cursor name为全局游标。
 *  **cursor\_name**:已声明的游标名称，如果全局游标和局部游标都使用cursor name作为其名称，那么如果指定了GLOBAL,cursor name指的是全局游标，否则cursor name指的是局部游标。
 *  **cursor\_variable\_name**:游标变量的名称，该名称引用一个游标。

> **说明：**  
> 如果使用NSENSITIV或STATIC选项声明了游标，那么OPEN将创建一个临时表以保留结果集。如果结果集中任意行的大小超过SQLServer表的最大行大小，OPEN将失败。如果使用  
> KEYSET选项声明了游标，那么OPEN将创建一个临时表以保留键集。临时表存储在tempdb中。

#### 5.2.2示例应用： ####

**【例1】首先声明一个名为Emp\_01的游标，然后使用OPEN命令打开该游标。SQL语句如下：**

USE test
    DECLARE Emp_01 CURSOR FOR  --声明游标
    SELECT * FROM Emp
    WHERE ID ='1'
    OPEN Emp_01 --打开游标
    GO

### 5.3读取游标中数据 ###

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

#### 5.3.1语法格式如下： ####

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

**FETCH命令的参数及说明如下所示：**

*  **NEXT**：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果FETCH NEXT为对游标
 *  的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项；
 *  **PRIOR**：返回紧临当前行前面的结果行，并且当前行递减为结果行。如果FETCH PRIOR为对游
 *  标的第一次提取操作，则没有行返回并且游标置于第一行之前；
 *  **FIRST**：返回游标中的第一行并将其作为当前行；
 *  **LAST**：返回游标中的最后一行并将其作为当前行；
 *  **ABSOLUTE\{ n |@nvar\}**：如果n或@nvar为正数，返回从游标头开始的第n行，并将返回的行变成新的当前行。如果n或@nvar为负数，返回游标尾之前的第n行，并将返回的行变成新的当前行。如果n或@nvar为O,则没有行返回;
 *  **RELATIVE (n |@nvar\}**：如果n或@nvar为正数，返回当前行之后的第n行，并将返回的行变成新的当前行。如果n或@nvar为负数，返回当前行之前的第n行，并将返回的行变成新的当前行。如果n或@nvar为O,返回当前行。如果对游标的第一次提取操作时将FETCHRELATIVE的n或@nvar指定为负数或O,则没有行返回。n必须为整型常量且@nvar必须为smallint、tinyint或int；
 *  **GLOBAL**：指定cursor\_name为全局游标;
 *  **cursor\_name：**要从中进行提取的开放游标的名称。如果同时以cursor\_name作为名称的全局和局部游标存在，若指定为GLOBAL,则cursor\_name对应于全局游标，未指定GLOBAL,则对应于局部游标;
 *  **@cursor\_ariable\_name**：游标变量名，引用要进行提取操作的打开的游标；
 *  **INTO @variable name\[,...n\]**：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致；
 *  **@@FETCH\_STATUS**：返回上次执行FETCH命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，决定如何进行下一步处理。@@FETCH\_STATUS变量有3个不同的返回值，说明如下：(1)返回值为O:FETCH语句成功：(2)返回值为-1：FETCH语句失败或此行不在结果集中：(3)返回值为-2：被提取的行不存在；

> **说明：**
> 
> 在前两个参数中，包含了n和@nvar其表示游标相对与作为基准的数据行所偏离的位置。
> 
> 当使用SQL-92语法来声明一个游标时，没有选择SCROLL选项，则只能使用FETCH NEXT命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行。由于不能使用FIRST、LAST、PRIOR,所以无法回滚读取以前的数据。如果选择了SCROLL选项，则可以使用所有的FETCH操作。

#### 5.3.2示例应用 ####

**【例1】用@@FETCH\_STATUS控制一个WHLE循环中的游标活动。SQL语句如下：**

USE test
    go
    DECLARE Readcursor CURSOR FOR
    SELECT * FROM emp
    OPEN ReadCursor
    FETCH NEXT FROM Readcursor
    WHILE @@FETCH_STATUS=0 
    BEGIN
        FETCH NEXT FROM ReadCursor
    END

运行结果如图所示：从游标中读取数据

![46d27b6889614d7fae2fc4b75b588d5b.png][]

### 5.4关闭游标 ###

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

#### 5.4.1语法格式如下： ####

CLOSE{
       {[GLOBAL] cursor_name }|cursor_variable_name}

**参数说明：**

*  GLOBAL:指定cursor name为全局游标。
 *  cursor\_name:开放游标的名称。如果全局游标和局部游标都使用cursor name作为它们的名称，那么当指定GLOBAL时，cursor\_name引用全局游标；否则cursor\_name引用局部游标。
 *  cursor\_variable\_name:与开放游标关联的游标变量名称。

#### 5.4.2示例应用 ####

**【例1】声明一个名为CloseCursor的游标，并使用Close语句关闭游标，SQL语句如下：**

USE test
    DECLARE CloseCursor Cursor FOR
    SELECT * FROM emp
    FOR READ ONLY
    OPEN CloseCursor
    CLOSE CloseCursor

### 5.5释放游标 ###

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

#### 5.5.1语法格式如下： ####

DEALLOCATE{
       {[GLOBAL cursor_name @cursor_variable_name

**参数说明：**

*  **cursor name**:已声明游标的名称。当全局和局部游标都以cursor name作为它们的名称存在时，如果指定GLOBAL,则cursor name引用全局游标，如果未指定GLOBAL,则cursor name引用局部游标。
 *  **@cursor\_variable\_name**:cursor变量的名称。@cursor variable name必须为cursor类型。

当使用DEALLOCATE@cursor\_variable\_name来删除游标时，游标变量并不会被释放，除非超过使用该游标的存储过程和触发器的范围。

#### 5.5.2示例应用 ####

**【例1】使用DEALLOCATE命令释放名为FreeCursor的游标。SQL语句如下：**

USE test
    DECLARE FreeCursor Cursor FOR
    SELECT * FROM EMP
    OPEN FreeCursor
    Close FreeCursor
    DEALLOCATE FreeCursor

## 六、游标查看 ##

创建游标后，通常使用sp\_cursor\_list和sp\_describe\_cursor查看游标的属性。sp\_cursor\_list用来报告当前为连接打开的服务器游标的属性，sp\_describe\_cursor用于报告服务器游标的属性。下面将详细介绍这两个系统过程。

### 6.1sp\_cursor\_list ###

sp\_cursor\_list报告当前为连接打开的服务器游标的属性。

#### 6.1.1语法格式如下： ####

sp_cursor_list [@cursor_return =]cursor_variable_name OUTPUT,[@cursor_scope =]cursor_scope

**参数说明**：

*  **\[@cursor\_return=\]cursor\_variable\_name OUTPUT**:已声明的游标变量的名称。cursor\_variable\_name的数据类型为cursor,无默认值。游标是只读的可滚动动态游标。
 *  **\[@cursor\_scope=\]cursor\_scope**:指定要报告的游标级别。cursor\_scope的数据类型为int,无默认值，可取值如下表所示。

<table style="width:500px;">  cursor_scope可取值  
 <tbody> 
  <tr> 
   <td style="text-align:center;">值</td> 
   <td style="text-align:center;">说明</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">1</td> 
   <td style="text-align:center;">报告所有本地游标</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">2</td> 
   <td style="text-align:center;">报告所有全局游标</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">3</td> 
   <td> <p style="text-align:center;">报告本地游标和全局游标&nbsp;</p> </td> 
  </tr> 
 </tbody> 
</table>

#### 6.1.2示例应用 ####

**【例1】声明一个游标，并使用sp\_cursor\_Iist报告该游标的属性,SQL语句如下：**

USE test 
    GO
    DECLARE Cur_Emp CURSOR FOR
    SELECT EName
    FROM Emp
    WHERE EName LIKE'李%'
    OPEN Cur_Emp
    DECLARE @Report CURSOR
    EXEC master.dbo.sp_cursor_list @cursor_return=@Report OUTPUT,
           @cursor_scope =2
    FETCH NEXT from @Report
    WHILE(@@FETCH_STATUS <>-1)
    BEGIN
      FETCH NEXT from @Report
    END
    CLOSE @Report
    DEALLOCATE @Report
    GO
    CLOSE Cur_Emp
    DEALLOCATE Cur_Emp
    GO

执行结果如下：

![1c35ccdd14bf4c108e732bd02fa5f0b4.png][]

### 6.2sp\_describe\_cursor ###

sp\_describe\_cursor用于报告服务器游标的属性。

#### 6.2.1语法格式如下： ####

sp_describe_cursor[@cursor_return =]output_cursor_variable OUTPUT
       {[,[@cursor_source=]N'local'
       ,[@cursor_identity =N'local_cursor_name']
       |[,[@cursor_source =]N'global'
      ,[@cursor_identity =]N'global_cursor_name']
      |[,[@cursor_source =]N'variable'
        ,[@cursor_identity =]N'input_cursor_variable']
        }

**sp\_describe\_cursor语句的参数及说明如下所示：**

*  **\[@cursor return=\]output\_cursor\_variable OUTPUT** ：用于接收游标输出的声明游标变量的名称。output\_cursor\_)variable的数据类型为cursor,无默认值。调用sp\_describe\_cursor时，该参数不得与任何游标关联。返回的游标是可滚动的动态只读游标;
 *  **\[,\[@cursor\_source=\]N'local' |N'global' | N'variable'\}**：指定是使用局部游标的名称、全局游标的名称还是游标变量的名称来指定要报告的游标。该参数的类型为nvarchar(30);
 *  **\[@cursor\_identity =N'local\_cursor\_name'\]**：由具有LOCAL关键字或默认设置为LOCAL的DECLARE CURSOR语句创建的游标名称。local\_cursor\_name的数据类型为nvarchar(128);
 *  **\[@cursor\_identity =\]N'global\_cursor\_name'\]**：由具有GLOBAL关键字或默认设置为GLOBAL的DECLARE CURSOR语句创建的游标名称。global\_cursor\_name的数据类型为nvarchar(l28);
 *  **\[@cursor\_identity =\]N'input\_cursor\_variable'\]**：与所打开游标相关联的游标变量的名称。input\_cursor\_variable的数据类型为nvarchar(128);

#### 6.2.2示例应用 ####

**【例1】声明一个游标，并使用sp\_describe\_cursor报告该游标的属性。SQL语句如下：**

USE test 
    GO
    DECLARE Cur_Emp CURSOR STATIC FOR
    SELECT EName
    FROM Emp
    OPEN Cur_Emp
    DECLARE @Report CURSOR
    EXEC master.dbo.sp_describe_cursor @cursor_return= @Report OUTPUT,
           @cursor_source= N'global',@cursor_identity =N'Cur_Emp'
    FETCH NEXT from @Report
    WHILE(@@FETCH_STATUS<>-1)
    BEGIN
      FETCH NEXT from @Report
    END
    
    CLOSE @Report
    DEALLOCATE @Report
    GO
    CLOSE Cur_Emp
    DEALLOCATE Cur_Emp
    GO

执行结果如下：

![415bcf6a712146e5b0d7d6b1cff0db02.png][]

## 七、隐式与显示游标 ##

### 7.1显示游标 ###

显式声明游标允许我们为游标设置更多的选项和参数以满足特定的需求，但它需要更多的代码。

#### 7.1.1语法格式： ####

--1. 声明游标：
    DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
       [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement;
    
    --2. 打开游标：
       OPEN cursor_name;
    
    
    --3. 获取游标中的行数据：
       FETCH NEXT FROM cursor_name INTO variable1, variable2, ...;
    
    
    --4. 关闭游标：
       CLOSE cursor_name;
    
    
    --5. 释放游标的资源：
       DEALLOCATE cursor_name;

#### 7.1.2示例应用 ####

**声明一个名为CustomerCursor的游标，并设置一些选项，如游标的定位、类型和可滚动性等：**

DECLARE @CustomerId INT
    DECLARE @CustomerName VARCHAR(100)
    
    DECLARE CustomerCursor CURSOR 
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR 
    SELECT CustomerId, CustomerName FROM Customers
    
    OPEN CustomerCursor
    FETCH NEXT FROM CustomerCursor INTO @CustomerId, @CustomerName
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 在这里处理游标数据
        ...
        
        FETCH NEXT FROM CustomerCursor INTO @CustomerId, @CustomerName
    END
    
    CLOSE CustomerCursor
    DEALLOCATE CustomerCursor

以Customers表为准，使用游标来处理 Customers 表中数据的示例：

![8238293415014efeadc4e5bef034b38d.png][]

SQL语句如下：

--显示游标
    -- 声明变量
    DECLARE @CustomerId INT;
    DECLARE @CustomerName VARCHAR(100);
    
    -- 执行查询并循环处理每一行数据
    DECLARE cur CURSOR FOR 
    SELECT CustomerId, CustomerName 
    FROM Customers;
    
    OPEN cur;
    
    FETCH NEXT FROM cur INTO @CustomerId, @CustomerName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 在这里处理游标数据
    
        -- 例如，打印客户ID和名称
        PRINT 'Customer ID: ' + CAST(@CustomerId AS VARCHAR(10));
        PRINT 'Customer Name: ' + @CustomerName;
    
        -- 获取下一行数据
        FETCH NEXT FROM cur INTO @CustomerId, @CustomerName;
    END
    
    CLOSE cur;
    DEALLOCATE cur;

输出结果如下：

![2acf230fbee649a38db1a98a37799185.png][]

> **注意：**
> 
> 在使用显式游标时，需要显式地打开、关闭和释放游标。这与隐式游标不同，隐式游标在执行查询时会自动创建和释放。

### 7.2隐式游标 ###

隐式游标与具体的查询语句相关，并且不需要显式声明或控制。例如，在使用 `SELECT` 查询时，系统会自动创建一个隐式游标，并将结果集存储在该游标中。

#### 7.2.1语法格式： ####

SELECT column1, column2, ...
    FROM table_name
    WHERE condition;

使用SELECT语句来查询需要遍历的数据，并指定需要选择的列（column1, column2, …）。我们还可以使用WHERE子句来指定条件，以筛选需要的行。

隐式声明游标的关键在于使用SELECT语句来选择数据，并将结果集作为游标的数据源。执行SELECT语句时，SQL Server会自动创建一个默认的游标，将结果集存储在游标中以供后续处理。

> **注意：**
> 
> 隐式声明游标是基于SELECT语句的结果集的，因此它的功能相对简单。我们可以在SELECT语句中使用复杂的查询和条件来获取所需的数据，并随后使用遍历循环（如WHILE循环）来逐行处理这些数据。
> 
> 在使用隐式游标时，我们无需显式打开、关闭或释放游标。系统会自动管理游标的生命周期，并在需要时获取结果集中的行数据。

#### 7.2.2示例应用 ####

**【例1】检索Customers1表中城市为"New York"的客户的CustomerId和CustomerName列**

-- 查询需要遍历的数据
    SELECT CustomerId, CustomerName
    FROM Customers1
    WHERE City = 'New York';

执行这个SELECT语句后，SQL Server会自动创建一个隐式游标，并将查询结果存储在游标中。

**【例2】使用遍历循环（例如WHILE循环）来逐行处理游标中的数据，以实现对结果集的逐行操作**

-- 使用 WHILE 循环遍历隐式游标中的行
    
    DECLARE @variable1 data_type, @variable2 data_type, ...;
     
    -- 执行查询并获取结果集中的行数据
    SELECT @variable1 = column1, @variable2 = column2, ...
    FROM table_name
    WHERE condition;
    
    -- 循环处理每一行数据
    WHILE @@FETCH_STATUS = 0
    BEGIN
       -- 在这里处理行数据
       ...
    
       -- 获取下一行数据
       SELECT @variable1 = column1, @variable2 = column2, ...
       FROM table_name
       WHERE condition;
    END

依旧以Customers表为准，使用隐式游标while循环来处理 Customers 表中数据，SQL语句如下：

-- 声明变量
    DECLARE @CustomerId INT;
    DECLARE @CustomerName VARCHAR(100);
    
    -- 执行查询并循环处理每一行数据
    SELECT @CustomerId = MIN(CustomerId) FROM Customers;
    
    WHILE @CustomerId IS NOT NULL
    BEGIN
        -- 获取当前行的其他列数据
        SELECT @CustomerName = CustomerName 
        FROM Customers 
        WHERE CustomerId = @CustomerId;
    
        -- 在这里处理当前行数据
    
        -- 例如，打印客户ID和名称
        PRINT 'Customer ID: ' + CAST(@CustomerId AS VARCHAR(10));
        PRINT 'Customer Name: ' + @CustomerName;
        PRINT 'Customer Name (uppercase): ' + UPPER(@CustomerName);
    
        -- 获取下一行的CustomerID
        SELECT @CustomerId = MIN(CustomerId) 
        FROM Customers 
        WHERE CustomerId > @CustomerId;
    END

在示例中，我是首先使用SELECT语句获取Customers表中最小的CustomerId，并将其赋值给变量@CustomerId。然后，进入一个WHILE循环，只要@CustomerId不为NULL，就会一直执行循环体内的操作。

在循环体内，我使用SELECT语句根据当前的@CustomerId获取对应的CustomerName，并将其赋值给变量@CustomerName。

接下来，我可以在循环体内处理当前行数据，例如打印客户ID和名称。在示例中，我是使用PRINT语句来打印变量的值，并且打印每一行的客户ID和大写的客户名称。

最后，我再使用SELECT语句获取下一个大于当前@CustomerId的最小CustomerId，并将其赋值给@CustomerId，以准备处理下一行数据。

通过循环遍历Customers表中的每一行数据，并在循环体内处理每一行的数据，例如打印客户ID和名称。

执行结果如下：

![c16bd69ece28481e8022c48706c8d0ed.png][]

[12ed07cb4d6b42a2a10f7c4827c90f94.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/704647ca188240d8822de62259612d0e.png
[46d27b6889614d7fae2fc4b75b588d5b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/799dc2344a5b4131bf2bf5c2c9d616c1.png
[1c35ccdd14bf4c108e732bd02fa5f0b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/4437e13c717546ed9282a301bf347e83.png
[415bcf6a712146e5b0d7d6b1cff0db02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/751fcb51ba0943ccbc79a8e2a99cc6c8.png
[8238293415014efeadc4e5bef034b38d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/785c8ddfeb1443f687e38f44845321e6.png
[2acf230fbee649a38db1a98a37799185.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/1f25aff021854e25ac128742a94ec83b.png
[c16bd69ece28481e8022c48706c8d0ed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/53ab2820b9d04f50a9b1c7850fe01d3e.png