SQL游标文档格式.docx

SQL游标文档格式.docx

《SQL游标文档格式.docx》由会员分享，可在线阅读，更多相关《SQL游标文档格式.docx（6页珍藏版）》请在冰豆网上搜索。

1、SQL-92标准的游标声明

SQL-92标准中，声明游标的语句格式为：

DECLARE游标名[INSENSITIVE][SCROLL]CURSOR

FORSELECT语句

[FOR{READONLY|UPDATE[OF列名表]}]

其中：

游标名：

是与某个查询结果集相联系的符号名，要符合SQLServer标识符命名规则。

INSENSITIVE：

指定系统将创建供所定义的游标使用的数据的临时复本，对游标的所有请求都从tempdb中的该临时表中得到应答；

因此，在对该游标进行提取操作时返回的数据中不反映对基本表所做的修改，并且该游标不允许修改。

如果省略INSENSITIVE，则任何用户对基本提交的删除和更新都反映在后面的提取中。

SCROLL：

说明所声明的游标可以前滚、后滚，可使用所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）。

如果省略SCROLL，则只能使用NEXT提取选项。

SELECT语句：

由该查询产生与所声明的游标相关联的结果集。

READONLY：

说明所声明的游标为只读的。

UPDATE指定游标中可以更新的列，若有参数[OF列名表]，则只能修改给出的这些列，若在UPDATE中未指出列，则可修改所有列。

例：

DECLARExs_cur1CURSOR

FOR

SELECTxh,xm,xb,dz

FROMstudent

WHERExb=’男’

FORREADONLY

2、T-SQL扩展的游标声明

T-SQL扩展的游标声明的语句格式为：

DECLARE游标名CURSOR

[LOCAL|GLOBAL]/*游标的作用域*/

[FORWARD_ONLY|SCROLL]/*游标的移动方向*/

[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]/*游标的类型*/

[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]/*访问的属性*/

[TYPE_WARNING]/*类型转换警告信息*/

FORSELECT语句/*SELECT查询语句*/

[FORUPDATE[OF列名表]]/*可修改的列*/

其中：

LOCAL和GLOBAL：

说明游标的作用域。

LOCAL说明所声明的游标是局部游标，其作用域为创建它的批处理、存储过程或触发器，该游标名称仅在这个作用域内有效。

GLOBAL说明所声明的游标是全局游标，它在由连接执行的任何存储过程或批处理中都可以使用，在连接释放时游标自动释放。

若两者均未指定，则默认值由defaulttolocalcursor数据库选项的设置控制。

FORWARD_ONLY和SCROLL：

说明游标的移动方向。

FORWARD_ONLY表示游标只能从第一行滚动最后一行，即NEXT。

而SCROLL可以任意方向。

STATIC|KEYSET|DYNAMIC|FAST_FORWARD：

用于定义游标的类型，T-SQL扩展游标有以下四种类型：

（1）STATIC说明的静态游标，它与SQL-92标准的INSENSITIVE关键字功能相同。

（2）KEYSET键集驱动游标，这种游标是由称为键的列或列的组合控制的。

打开键集驱动游标时，其中的成员和行顺序是固定的。

键集驱动游标中数据行的键值在游标打开时建立在tempdb中。

可以通过键集驱动游标修改基本表中的非关键字列的值，但不能插入数据。

（3）DYNAMIC说明的动态游标，动态游标能反映对结果集中所做的修改。

结果集中的行数据值、顺序和成员在每次提取时都会改变，所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标反映出来。

（4）FAST_FORWARD定义一个快速只进游标，它是优化的只进游标。

只进游标只支持游标从头到尾顺序提取数据。

对所有由当前用户发出或由其他用户提交，并影响结果集中的行的INSERT、UPDATE和DELETE语句，对数据的修改在从游标中提取时是可立即反映出来。

但因只进游标不能向后滚动，所以在行提取后对行所做的更改对游标是不可见的。

游标类型与移动方向之间的关系：

（1）FAST_FORWARD不能与SCROLL一起使用，且FAST_FORWARD与FORWARD_ONLY只能选用一个。

（2）若指定了移动方向为FORWARD_ONLY，而没有用STATIC、KEYSET或DYNAMIC关键字指定游标类型，则默认所定义的游标为动态游标。

（3）若移动方向FORWARD_ONLY和SCROLL都没有指定，那么移动方向关键字的默认值由以下条件决定：

若指定了游标类型为STATIC、KEYSET或DYNAMIC，则移动方向默认为SCROLL；

若没有用STATIC、KEYSET或DYNAMIC关键字指定游标类型，则移动方向默认值为FORWARD_ONLY。

READ_ONLY|SCROLL_LOCKS|OPTIMISTIC说明游标或基表的访问属性。

READ_ONLY声明的游标为只读。

SCROLL_LOCKS说明通过游标完成的定位更新或定位删除可以成功。

如果声明中已指定了关键字FAST_FORWARD，则不能指定SCROLL_LOCKS。

OPTIMISTIC说明如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。

如果声明中已指定了关键字FAST_FORWARD，则不能指定OPTIMISTIC。

TYPE_WARNING：

指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。

SELECT查询语句，由该查询产生与所声明的游标相关联的结果集。

FORUPDATE：

指定游标中可以更新的列，若有参数[OF列名表]，则只能修改给出的这些列，若在UPDATE中未指出列，则可修改所有列。

例：

DECLAREXS_CUR2CURSOR

DYNAMIC

SELECTxh,xm,csny

FORUPDATEOFcsny

三、打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。

在T-SQL中，使用OPEN语句打开游标，其格式为：

OPEN{{[GLOBAL]游标名}|游标变量名}

GLOBAL说明打开的是全局游标，否则打开局部游标。

OPENxs_cur1

打开游标后，可以使用全局变量@@CURSOR_ROWS查看游标中数据行的数目。

全局变量@@CORSOR_ROWS中保存着最后打开的游标中的数据行数。

当其值为0时，表示没有游标打开；

当其值为-1时，表示游标为动态的；

当其值为-m（m为正整数）时，游标采用异步方式填充，m为当前键集中已填充的行数；

当其值为m（m为正整数）时，游标已被完全填充，m是游标中的数据行数。

四、读取数据

游标打开后，就可以使用FETCH语句从中读取数据。

FETCH语句的格式为：

FETCH

[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|

RELATIVE{n|@nvar}]FROM]

{{[GLOBAL]CURSOR}|@cursor_variable_name}

[INTO@variable_name[,…n]]

cursor_name:

要从中提取数据的游标名;

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

NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：

用于说明读取数据的位置。

NEXT说明读取当前行的下一行，并且使其置为当前行。

如果FETCHNEXT是对游标的第一次提取操作，则读取的是结果集第一行。

NEXT为默认的游标提取选项。

FRIOR说明读取当前行的前一行，并且使其置为当前行。

如果FETCHPRIOR是对游标的第一次提取操作，则无值返回且游标置于第一行之前。

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

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

ABSOLUTE{n|@nvar}和RELATIVE{n|@nvar}:

给出读取数据的位置与游标头或当前位置的关系，其中n必须为整型常量，变量@nvar必须为smallint、tinyint或int类型。

ABSOLUTE{n|@nvar}:

若n或@nvar为正数，则读取从游标头开始的第n行，并将读取的行变成新的当前行；

若n或@nvar为负数，则读取从游标尾之前的第n行，并将读取的行变成新的当前行；

若n或@nvar为0，则没有行返回。

RELATIVE{n|@nvar}:

若n或@nvar为正数，则读取当前行之后的第n行，并将读取的行变成新的当前行；

若n或@nvar为负数，则读取当前行之前的第n行，并将读取的行变成新的当前行；

若n或@nvar为0，则读取当前行。

如果对游标的第一次提取操作时将FETCHRELATIVE中的n或@nvar指定为负数或0，则没有行返回。

INTO：

说明将读取的游标数据存放到指定的变量中。

GLOBAL：

全局游标。

从游标xs_cur1中提取数据，假设该游标已经打开。

FETCHNEXTFROMXS_CUR1

从游标xs_cur2中提取数据，假设该游标已经打开。

FETCHFIRSTFROMXS_CUR2

FETCHNEXTFROMXS_CUR2

FETCHPRIORFROMXS_CUR2

FETCHLASTFROMXS_CUR2

FETCHRELATIVE-2FROMXS_CUR2

FETCH语句的执行状态保存在全局变量@@FETCH_STATUS中，其值为0，表示上一个FETCH执行成功；

为-1，表示所要读取的行不在结果集中；

为-2，表示被提取的行已不存在（已被删除）。

例如若接着上例继续执行如下语句：

FETCHRELATIVE3FROMXS_CUR2

SELECT‘FETCH执行情况’=@@FETCH_STATUS

结果为：

FETCH执行情况

-1

五、关闭游标

游标使用完以后，要及时关闭。

关闭游标使用CLOSE语句，格式为：

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

其参数的含义与OPEN语句中相同。

如：

CLOSEXS_CUR2

将关闭游标XS_CUR2

六、删除游标

游标关闭后，其定义仍在，需要时可用OPEN语句打开它再使用。

若确认游标不再需要，就要释放其定义占用的系统空间，即删除游标。

删除游标使用DEALLOCATE语句，格式为：

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

其参数的含义与OPEN和CLOSE语句中相同。

DEALLOCATEXS_CUR2

将删除游标XS_CUR2