第8章游标的设计和使用1

在T-SQL中，CURSOR不仅仅可以用来浏览查询结果，还可以用UPDATE语句修改CURSOR的当前行或用DELETE命令删除当前行。相应的UPDATE命令格式相应DELETE命令的格式是DELETEFROM表名WHERECURRENTOF游标名；利用WHERECURRENTOF游标名进行的修改或删除只影响当前行UPDATE{表名|视图名}SET列名={表达式|DEFAULT|NULL}[,列名={表达式|DEFAULT|NULL}…]WHERECURRENTOF游标名；第八章数据库游标的设计和使用1、DECLARECURSOR命令(SQL-92标准）说明DECLARE游标名[INSENSITIVE][SCROLL]CURSORFORSELECT查询[FOR{READONLY|UPDATE[OF列名[,列名…]]}]格式（1）INSENSITIVE指出所声明的游标为不敏感游标，即静态游标。这种游标所使用的数据被临时拷贝到tempdb数据库中，对这种游标的所有操作都基于tempdb数据库中的临时表。因此游标结果集合填充后，所有用户对游标基表数据的修改都不能反映到当前游标结果集合中。此外，这种游标也不允许执行数据修改操作。当省略INSENSITIVE选项时，已提交的游标基表修改和删除操作能够反映到其后的游标提取结果中。（2）SCROLL指出该游标可以用FETCH命令里定义的所有方法来存取数据，允许删除和更新（假定没有使用INSENSITIVE选项）；如果没有SCROLL选项，DECLARE语句声明的游标则只能使用NEXT选项，即每次只能读取下一行数据。（3）SELECT查询语句决定游标结果集合，但在其中不能使用COMPUTE、COMPUTEBY、FORBROWSE和INTO等关键字。（4）FORREADONLY或FORUPDATE说明游标为只读的或可修改的。默认是可修改的。（5）UPDATE[OF列名[,列名…]]定义可以修改的列。如果省略OF列名[,列名…]，则允许修改所有列。说明例如、下面语句声明游标title_cur1:DECLAREtitle_cur1SCROLLCURSORFORSELECT*FROMtitlesWHEREtype=‘business’2、DECLARECURSOR命令(Transact-SQL）DECLARE游标名CURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWAR][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORSELECT查询[FORUPDATE[OF列名[,列名…]]]格式说明其中，SCROLL、UPDATE[OF列名…]参数与SQL-92声明中的同名参数的作用相同，READ_ONLY与SQL-92声明中的READONLY相同。（1）LOCAL和GLOBAL选项分别说明DECLARECURSOR语句所声明的游标为局部游标和全局游标。局部游标的作用域为声明该游标的批、存储过程或触发器，全局游标的作用域为当前连接。在作用域外，游标是不可见的。当局部游标用于存储过程的返回参数时，只有当存储过程调用者中接收该返回参数的局部变量被释放时，局部游标才随之释放，而不是在存储过程运行结束时释放局部游标。而全局游标则在声明全局游标的连接断开时被自动释放。说明如果LOCAL和GLOBAL选项都没有指定，则其默认值由数据库选项defaulttolocalcursor决定。如果defaulttolocalcursor被设置为true,则默认是局部游标，否则defaulttolocalcursor为false时，默认是全局游标。SQLSERVER2000中，数据库选项默认值是false.（2）FORWARD_ONLY选项声明只进游标，即FETCH语句中只能使用NEXT选项。当不指出FAST_FORWARD、FORWARD_ONLY和SCROLL时，静态游标、键集游标和动态游标的默认设置为SCROLL，而其它游标的默认设置为FORWARD_ONLY。当DECLARE语句中只指出FORWARD_ONLY选项，而没有使用STATIC、KEYSET和DYNAMIC参数说明游标类型时，则默认为DYNAMIC游标。（3）STATIC与SQL-92声明中的INSENTIVE关键字的功能相同，它将游标声明为静态游标，禁止应用程序通过它修改基表数据。（4）KEYSET关键字声明键集驱动游标，键集游标中的数据行及其顺序是固定的。SQLServer将能够唯一标识游标中各数据行的关键字集合存储到tempdb数据库中的一个临时表中，形成键集。对于键集游标：¤应用程序不能通过键集游标向其基表插入数据。键集游标填充后，它无法看到其他用户向基表中插入的数据行。¤应用程序可以修改基表中的非键集列值，并能够看到其他用户或游标修改后的非键集列值。¤读取键集游标中被删除的数据行时，@@FETCH_STATUS参数将返回-2。¤修改游标中的键集列相当于删除旧行，插入新行。读取被删除的旧行时，@@FETCH_STATUS参数将返回-2，而插入的新行对键集游标是不可见的。¤可以看到通过WHERECURRENTOF子句所修改的数据。（5）DYNAMIC将游标声明为动态游标，也就是说，其结果结合是动态变化的，能够随时反映用户已提交的更改结果。提取动态游标数据时，不能使用ABSOLUTE提取选项定位游标指针。（6）FAST_FORWARD指出起用优化的FORWARD_ONLY和READ_ONLY游标。如果使用FAST_FORWARD选项，则不能使用SCROLL和FORUPDATE选项，此外，FAST_FORWARD和FORWARD_ONLY选项是互斥的，这两个选项不能同时使用。（7）SCROLL_LOCKS选项要求SQLServer在将数据读入游标的同时锁定基表中的数据行，以确保以后能够通过游标成功地对基表进行定位删除和修改操作。在同一个游标声明语句中，不能同时使用FAST_FORWARD选项和SCROLL_LOCKS选项。（8）OPTIMISTIC说明在填充游标时不锁定基表中的数据行。这时，应用程序通过游标对基表进行定位修改或删除操作时，SQLServer首先检测游标填充之后表中数据是否被修改，如果数据已被修改，SQLServer将停止应用程序的定位修改或删除操作。SQLServer检查表中数据行是否被修改的方法是比较时间戳列值，或行数据的奇偶校验和（无时间戳列时）。游标声明语句中如果指定了FAST_FORWARD选项，则不能使用OPTIMISTIC选项。（9）OPTIMISTIC选项的这种处理方式叫做乐观并发控制方式。与之对应的是悲观并发控制，悲观并发控制在整个事务的持续时间内锁定资源。所以在这种方式下，除非出现死锁，否则事务肯定会成功完成。（10）TYPE_WARNING指出在声明游标过程中，如果无法建立用户指定类型的游标而隐式转换为另一类型时，给客户端发出警告消息。例如、下面声明一个游标title_cur2:DECLAREtitle_cur2CURSORLOCALSCROLLDYNAMICTYPE_WARNINGFORSELECT*FROMtitlesWHEREtype=‘business’3、管理游标在Transact_SQL程序中，执行以下系统存储过程和函数能够检索游标属性信息和状态信息：sp_cursor_list:检索当前连接所有可见游标sp_describe_cursor：检索游标属性信息，如作用域、名称、类型、状态和行数。sp_describe_cursor_columns:检索游标结果集合中的列属性。sp_describe_cursor_tables：检索游标锁引用的基表信息。@@CURSOR_STATUS：读取游标状态或检查游标变量是否与游标相关联。@@FETCH_STATUS:读取最后一次游标数据提取操作结果状态。0:取操作成功；-1：取操作失败，所指定的位置超出了范围；-2：要取的行不在记录集内，已从集合中删除。@@CURSOR_ROWS：显示游标集合中的行数。-n：正在向游标中载入数据，反映的是结果集当前的数据行数；n：结果集合的行数；0：结果集中没有匹配的行；-1：指出游标是动态的。4、游标变量游标变量声明的格式：DECLARE@cursor_variable_nameCURSOR游标变量声明后，必须和某个游标相关联才能实现游标操作。有两种方法建立游标和游标变量之间的关联：第一种：先声明游标和游标变量，之后用SET语句将游标赋给游标变量。例如：DECLARE@cur_varCURSORDECLAREC1CURSORFORSELECT*FROMtitlesSET@cur_var=C1第二种：不声明游标，直接在SET语句中将各种游标定义赋给游标变量。例如：DECLARE@cur_varCURSORSET@cur_var=CURSORFORSELECT*FROMtitles当游标变量与游标相关联之后，在Transact-SQL游标语句中就可以使用游标变量代替游标名，实现各种操作。例如：OPEN@cur_var5、OPEN命令格式OPEN{{[GLOBAL]游标名}|游标变量}当应用程序定义了同名的全局游标和局部游标时，使用GLOBAL选项说明所打开的是全局游标，不指定GLOBAL选项时，打开的是局部游标。如果打开的游标为INSENSITIVE游标或STATIC游标，OPEN语句将在tempdb数据库中产生一个临时表，将定义的游标结果集合拷贝到临时表中。当打开键集游标时，OPEN语句将在tempdb数据库中建立临时表以保存键集。游标打开后，可以调用@@CURSOR_ROWS函数读取游标结果集合中的行数。-n：正在向游标中载入数据，反映的是结果集当前的数据行数；n：结果集合的行数；0：结果集中没有匹配的行或指定游标没有打开或被释放；-1：指出游标是动态的。6、FETCH命令格式FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]游标名}|cursor_variable_name}[INTO@变量名1,@变量名2…]说明（1）NEXT说明如果是在OPEN后第一次执行FETCH命令，则返回结果集的第一行，否则使游标（指针）指向结果集的下一行；NEXT是默认的选项，也是最常用的一种方法（2）PRIOR、FIRST、LAST、ABSOLUTE{n|@nvar}、RELATIVE{n|@nvar}等各项，只有在定义游标时使用了SCROLL选项才可以使用；PRIOR是返回结果集当前行的前一行；FIRST是返回结果集的第一行；LAST是返回结果集的最后一行；ABSOLUTEn是返回结果集的第n行，如果n是负数，则返回倒数第n行；RELATIVEn是返回当前行后的第n行，如果n是负数，则返回从当前行开始倒数据的第n行（3）对于Transact-SQL语法声明的游标:如果游标被定义为只进游标或快速只进游标，在FETCH语句中只能使用NEXT选项。如果未指定DYNAMIC、FORWARD_ONLY或FAST_FORWARD选项，但指定了KEYSET、STATIC或SCROLL中的任一个时，FETCH语句将支持所有定位选项。DYNAMICSCROLL游标支持除ABSOLUTE之外的所有定位选项调用@@FETCH_STATUS，可检查提取操作的执行状态。0:取操作成功；-1：取操作失败，所指定的位置超出了范围；-2：要取的行不在记录集内，已从集合中删除。7、游标定位修改和删除操作用于游标操作时，UPDATE语句和DELETE语句的格式分别为：UPDATEtable_nameSET