SQL Server游标

发表评论 / 作者： /

在SQL Server关系数据库中的操作会对整个行集起作用。

例如，由SELECT语句返回的行集包括满足该语句的where子句中条件的所有行。

这种由语句返回的完整行集称为结果集。

应用程序特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。

这些应用程序需要一种机制以便每次处理一行或一部分行。

游标就是提供这种机制的对结果集的一种扩展。

1. 游标概述

游标是一种定位并控制结果集的机制，可以减少客户端应用程序的工作量和访问数据库的次数，通常在存储过程中使用。

在存储过程中使用SELECT语句查询数据库时，查询返回的数据存放在结果集中。

用户在得到结果集后，需要逐行逐列地获取其中包含的数据，从而在应用程序中使用这些值。

用数据库语言来描述，游标是映射结果集并在结果集内的单个行上建立一个位置的实体。

有了游标，用户就可以访问结果集中的任意一行数据了。

在将游标放置到某行之后，可以在该行或从该位置开始的行块上执行操作，而指向游标结果集中某一条记录的指针叫做游标位置。

游标具有以下功能：

  • 允许定位在结果集的特定行。
  • 从结果集的当前位置检索一行或多行。
  • 支持对结果集中当前位置的行进行数据修改。
  • 如果其他用户需要对显示在结果集中的数据库数据进行修改，游标可以提供不同级别的可见性支持。
  • 提供脚本、存储过程、触发器中使用的、访问结果集中的数据的T-SQL语句。

2. 游标的类型

SQL Server支持3种类型的游标：T-SQL游标、API游标和客户游标。

①T-SQL游标

T-SQL游标是由declare cursor语法定义，主要用在T-SQL脚本、存储过程和触发器中。

T-SQL游标主要用在服务器上，由从客户端发送给服务器的T-SQL语句或是批处理、存储过程、触发器中的T-SQL进行管理。

T-SQL游标不支持提取数据块或多行数据。

②API游标

API游标支持在OLE DB、ODBC以及DB_library中使用游标函数，主要用在服务器上。

每一次客户端应用程序调用API游标函数，SQL Server的OLE DB提供者、ODBC驱动器或DB_library的动态链接库(DLL)都会将这些客户请求传送给服务器以对API游标进行处理。

③客户游标

客户游标主要是当在客户机上缓存结果集时才使用。

在客户游标中，有一个默认的结果集被用来在客户机上缓存整个结果集。

客户游标仅支持静态游标而非动态游标。

由于服务器游标并不支持所有的T-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。

因为在一般情况下，服务器游标能支持绝大多数的游标操作。

由于T-SQL游标和API游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户游标被称为前台游标。

服务器游标包括以下4种：静态游标、动态游标、只进游标、键集驱动游标。

①静态游标

静态游标的完整结果集将打开游标时建立的结果集存储在临时表中。

静态游标始终是只读的，总是按照打开游标时的原样显示结果集；

静态游标不反映数据库中做的任何修改，也不反映对结果集行的列值所做的更改；

静态游标不显示打开游标后在数据库中新插入的行；

静态游标组成结果集的行被其他用户更新，新的数据值不会显示在静态游标中；

但是静态游标会显示打开游标以后从数据库中删除的行。

②动态游标

动态游标与静态游标相反，当滚动游标时，动态游标反映结果集中的所有更改。

结果集中的行数据值、顺序和成员每次提取时都会改变。

③只进游标

只进游标不支持滚动，它只支持游标从头到尾顺序提取数据行。

只进游标也反映对结果集所做的所有更改。

④键集驱动游标

键集驱动游标同时具有静态游标和动态游标的特点。

当打开游标时，该游标中的成员以及行的顺序是固定的，键集在游标打开时也会存储在临时工作表中，对非键集列的数据值的更改在用户游标滚动的时候可以看见，在游标打开以后对数据库中插入的行是不可见的，除非关闭后重新打开游标。

3. 游标的使用

使用游标的基本步骤有如下5步，分别是声明游标、打开游标、提取数据、关闭游标、释放游标。

声明游标

和使用其他类型的变量一样，使用一个游标之前，首先应当声明它。

游标的声明包括两部分：游标的名称和这个游标所用到的SQL语句。

声明游标有两种方式：SQL-92方式和T-SQL扩展方式。

SQL-92方式语法格式如下：

SQL
declare cursor_name[insensitive] [scroll] cursor
for select_statement
[for{read only| update [of column_name[,..n]]}]

其中，各参数说明如下：

  1. cursor_name：游标名。
  2. insensitive：表示声明一个静态游标。
  3. scroll：表示声明一个滚动游标，可以使用所有的提取选项滚动，包括first、last、prior、next、relative和absolute
  4. select_statement：表示select语句。
  5. read only：表示声明一个只读游标。
  6. update：指定游标中可以更新的列。如果有of column_name，则只能修改指定的列。如果没有，则可以修改所有的列。

例子1：声明一个名为student_cursor的游标，用以查询姓名为ICHI的学生的信息。

SQL
DECLARE STUDENT_CURSOR CURSOR FOR
  SELECT *
  FROM   STUDENT
  WHERE  NAME = 'ICHI'

单击’执行’，显示命令已成功完成。

T-SQL扩展方式：

T-SQL扩展方式提供了声明游标语句declare cursor，语法格式如下：

SQL
declare cursor_name cursor
[local|global]
[foeward_only|scroll]
[static|keyset|dynamic|fast_forward]
[read_only|scroll_locks|optimistic]
[type_warning]
for select_list
[for update [of column_name[,...n]]]

其中，各参数说明如下：

  1. local：定义游标的作用域仅限在其所在的存储过程、触发器或批处理中。当建立游标的存储过程执行结束后，游标会被自动释放。
  2. global：定义游标的作用域，说明所声明的游标是全局游标，作用于整个会话层中。只有当用户脱离数据库时，该游标才会被自动释放。如果既未使用global，也未使用local，那么SQL Server将默认为local。
  3. forward_only：指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用fetch next操作。
  4. static：与insensitive选项一样，SQL Server会将游标定义选取出来的数据记录存放在一个临时表内。对该游标的读取操作皆由临时表来完成。
  5. keyset：指出当游标被打开时，游标中列的顺序是固定的，并且SQL Server会在tempdb内建立一个表，该表即为keyset的键值，可以唯一识别游标中的某行数据。
  6. dynamic：指明基础表的变化将反映到游标中，使用这个选项会最大限度地保证数据的一致性。然而，与keyset和static类型游标相比，此类型需要大量的游标资源。
  7. fast_forward：指明一个forward_only、read_only型游标。此选项已为执行进行了优化。如果scroll或for_update选项被定义，则fast_forward选项不能被定义。
  8. sroll_locks：指明锁被放置在游标结果集所使用的数据上。当数据被读入游标中时，就会出现锁。这个选项确保对一个游标进行的更新和删除操作总能被成功执行。如果fast_forward选项被定义，则不能选择该选项。另外，由于数据被游标锁定，所以如果要考虑数据并处理时，应避免使用该选项。
  9. optimistic：指明在数据被读入游标后，如果游标中的某行数据已发生变化，那么对游标数据进行更新或删除可能导致失败。如果使用了fast_forward选项，则不能使用该选项。
  10. type_warning：指明若游标类型被修改，与用户定义的类型不同时，将发送一个警告信息给客户端。

例子2：声明一个名为student2_cursor的游标，用以姓名为ICHI的学生信息。要求游标是动态的、可前后滚动的，其中sex列数据可以修改。其中age列数据可以修改。

SQL
USE ichi

GO

DECLARE STUDENT2_CURSOR CURSOR DYNAMIC FOR
  SELECT *
  FROM   STUDENT
  WHERE  NAME = 'ICHI'
  FOR UPDATE OF AGE

打开游标

声明了游标后，在做其他操作之前，必须打开它。

打开一个T-SQL服务器游标使用open命令，其语法规则如下：

SQL
open {{[global]cursor_name}|cursor_variable_name}

各参数说明如下：

  1. global：定义游标为一个全局游标
  2. cursor_name：声明的游标的名字。如果一个全局游标和一个局部游标都使用同一个游标名，则使用global表明其为全局游标，否则表明其为局部游标。
  3. cursor_variable_name：游标变量。当打开一个游标后，SQL Server首先检查声明游标的语法是否正确，如果游标声明中有变量，则将变量值带入。

例子3：打开student_cursor

SQL
USE ichi

GO

OPEN STUDENT_CURSOR

提取数据

当游标被成功打开以后，就可以从游标中逐行地读取数据，以进行相关处理。

从游标中读取数据主要使用fetch命令。

其语法格式如下：

SQL
fetch [[next|prior|first|last|
absolute{n|@nvar}|relative{n|@nvar}]
from]
{{[global]cursor_name}|cursor_variable_name}
[into @variable_name[,..n]]

各参数说明如下：

  1. next：返回结果集中当前行的下一行，并增加当前行数为返回行行数。如果fetch next是第一次读取游标中数据，则返回结果集中的是第一行而不是第二行。
  2. prior：返回结果集中当前行的前一行，并减少当前行数为返回行行数。如果fetch prior是第一次读取游标中的数据，则无数据记录返回，并把游标位置设为第一行。
  3. first：返回游标中的第一行
  4. last：返回游标中的第一行
  5. absolute：如果n或@nvar为正数，则表示从游标中返回的数据行数；如果n或@nvar为负数，则返回游标内从最后一行数据算起的第n或@nvar行数据。若n或@nvar超过游标的数据子集范畴，则@@fetch_stars返回-1，在该情况下，如果n或@nvar为负数，则执行fetch next命令会得到第一行数据，如果n或@nvar为正值，执行fetch prior命令则会得到最后一行数据。n或@nvar可以是一个固定值，也可以是一个smallint、tinyint或int类型的变量。
  6. relative{n|@nvar}：若n或@nvar为正数，则读取游标当前位置起向后的第n或@nvar行数据；如果n或@nvar为负数，则读取游标当前位置向前的第n或@nvar行数据。若n或@nvar超过游标的数据子集范畴，则@@fetch_stars返回-1，在该情况下，如果n或@nvar为负数，则执行fetch next命令会得到第一行数据，如果n或@nvar为正值，执行fetch prior命令则得到最后一行数据。n或@nvar可以是一个固定值，也可以是一个smallint、tinyint或int类型的变量。
  7. into@variable_name[,…n]：允许将使用fetch命令读取的数据存放在多个变量中，在变量行中的每个变量必须与游标结果集中响应的列对应，每一变量的数据类型也要与游标中数据列的数据类型相匹配。

例子4：从STUDENT2_CURSOR中读取数据

SQL
USE ichi

GO

FETCH NEXT FROM STUDENT2_CURSOR

关闭游标

在处理完游标中的数据后，必须关闭游标来释放数据结果集和定位于数据记录上的锁。

可以使用close语句关闭游标，但此语句不释放游标占用的数据结构。

其关闭游标的语法格式如下：

SQL
close {{[global] cursor_name}|cursor_variable_name}

其中，参数的含义与打开游标的命令相同。

例子5：关闭STUDENT2_CURSOR游标

SQL
USE ichi

GO

CLOSE STUDENT2_CURSOR

释放游标

不再需要使用游标时，要释放游标。

使用deallocate语句可以释放数据结构的游标所加的锁。

释放游标的语句格式如下：

SQL
deallocate {{[global] cursor_name}|cursor_variable_name}

其中，参数的含义与打开游标的命令相同。

例子6：释放STUDENT2_CURSOR游标。

SQL
USE ichi

GO

DEALLOCATE STUDENT2_CURSOR
浏览量 4
相关文章：

SQL Server触发器

SQL Server索引

SQL Server存储过程

SQL Server 数据库的并发控制原理-更新以及删除所利用的锁

订阅评论
提醒
0 评论
最旧
最新 最多投票
内联反馈
查看所有评论
滚动至顶部