第06章游标及其应用

第06章

游标的概念及应用6.1利用游标处理结果集

6.2管理大对象类型数据

6.3小结游标的概念及特点通常情况下，执行查询得到的是一个包含多条记录的、存放在客户机内存中的结果集。当用户需要访问一个结果集中的某条具体记录时，要在数据库服务器端实现该功能，就要用到游标。存放查询结果的存储区域及其相应的数据结构称为游标(Cursor)。为了逐个取出该区域中的行，需要一个指示器，指示已取行的位置，每取一个行指示器向前推进一个位置。游标提供了在结果集中向前或向后浏览数据的能力并能基于游标的当前位置同步读取或修改及删除表或视图中的行。使用游标的基本步骤声明游标打开游标从游标中反复提取行，进行相应操作关闭游标当不再使用游标时，释放游标声明游标1、使用Transact-SQL扩展语法声明游标2、使用SQL-92标准语法声明游标1、使用Transact-SQL扩展语法声明游标【格式】DECLARE游标名CURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC][FAST_FORWARD][READ_ONLY][SCROLL_LOCKS|OPTIMISTIC]FORselect_statement[FORUPDATE[OF列名[,...n]]]Select_Statement：是定义游标结果集的SELECT语句。在声明游标的select语句内不允许使用关键字COMPUTE(BY)和INTO。指定游标作用域的参数Local：声明局部游标，其作用域局限在其所在的批处理、存储过程或触发器中。当建立游标的批处理、存储过程或触发器执行结束后，游标被自动释放。Global：声明全局游标，其作用域是整个用户连接时间，包括从用户登录SQLSERVER服务器到脱离SQLSERVER服务器的时间段。DECLAREGoodScorecurCURSORLOCALFORSELECT*FROMSCoreWHEREfinal>=90

GO（批处理结束）【例】定义一个局部游标从SCore表中提取成绩优秀的所有选课记录反复执行不会有问题将上列改为定义全局游标DECLAREGoodScorecurCURSORGLOBALFORSELECT*FROMSCoreWHEREfinal>=90

GO游标相关的数据库选项每个数据库都有若干个决定该数据库特点的数据库级选项。只有系统管理员、数据库所有者和db_owner等数据库角色的成员才能修改这些选项。这些选项对于每个数据库都是唯一的，而且不影响其它数据库。可使用ALTERDATABASE语句的SET子句、sp_dboption系统存储过程，或者在某些情况下使用对象资源管理器设置数据库选项。使用系统存储过程sp_dboption设置数据库选项【格式】sp_dboption'database','option_name','value‘下表列出了sp_dboption设置的选项：数据库选项defaulttolocalcursor若声明游标时并未明确指定local和global，则由数据库选项defaulttolocalcursor决定游标作用域。如果defaulttolocalcursor设为true，游标是局部的，若此选项设为false则游标是全局的。defaulttolocalcursors选项默认为FALSE，可通过系统存储过程sp_dboption重置：EXECsp_dboption‘teaching','defaulttolocalcursor','true'指定游标方向的参数FORWARD_ONLY：声明游标为只向前型游标，只能从第一行依次滚动到最后一行，NEXT是唯一的取数选项。SCROLL：声明滚动游标，说明该游标具有下表所示全部取数功能：选项含义FIRST提取游标中的第一行数据LAST提取游标中的最后一行数据PRIOR提取游标当前位置的上一行数据NEXT提取游标当前位置的下一行数据RELATIVEn提取游标当前位置之前或之后的第n行数据(n为正表示向后，n为负表示向前)ABSULUTEn提取游标中的第n行数据指定游标类型的参数STATIC

：声明静态游标。将Select_Statement对应的查询结果放在Tempdb数据库的一个临时表中；对游标的所有数据请求都从该临时表中得到应答；因此对该游标进行数据提取不能实时反映基表的更新，也不能通过该游标更新基表数据。KEYSET：声明键值驱动游标。将Select_Statement对应的查询结果中每行数据的主键构成一个名为keyset表，放在tempdb数据库中，对游标的所有数据请求通过该keyset表中得到应答。使用KEYSET游标：外界插入新行在游标中是不可视的。外界对基表的非键修改，在游标中是可视的。其他用户从游标外更新键值后，含有新键值的行不可视，对含有旧键值的行的提取操作将返回@@FETCH_STATUS值-2（说明该行不存在）DYNAMIC：声明动态游标，游标直接与基表数据关联能实时的反映外界对基表所做的更新。FAST_FORWARD:声明一个优化了的快速只向前型只读型游标。

FAST_FORWARD和FORWARD_ONLY,forupdate互斥，两者任选一种指定游标类型的参数指定游标访问属性的参数SCROLL_LOCKS：声明滚动锁游标。指明将与游标相关的行读入游标时，SQLServer会锁定这些行，外界不能更新基表中的这些数据行。OPTIMISTIC：声明乐观控制游标。当将行读入游标时SQLServer不锁定行。如果行被读入游标后被外界更新，则通过游标进行的定位更新或定位删除不成功如果还指定了FAST_FORWARD，则不能指定SCROLL_LOCKS或OPTIMISTIC。指定游标是否更新的参数READONLY

：声明此游标为只读游标，不允许通过它对基表数据进行更新。FORUPDATE[OF列名[,...n]]

：声明可通过游标修改基表中的数据；[OF列名[,...n]]指明游标可更新的列，如果没有[OF列名[,...n]]

，则游标里所有的列均可更新注意若STATIC、KEYSET和DYNAMIC均未指定时，默认为DYNAMICSTATIC、KEYSET和DYNAMIC游标默认为SCROLL若FORWARD_ONLY和SCROLL及STATIC、KEYSET和DYNAMIC均未指定时，默认为NEXT是唯一支持的取数选项。DECLAREsinfo_cursorCURSORLOCALSCROLLDYNAMICFORSELECT*FROMSFORUPDATEOF电话【例】定义一个局部、滚动、动态游标sinfo_cursor用来从S表中提取学生信息，可通过该游标对“电话”字段进行修改。练习【例】定义一个全局、静态只读游FM_cursor，提取所有女生信息DECLAREFM_cursorCURSORGlobalSTATICREADONLYFORSELECT*FROMSWhere性别='女'2．使用SQL-92标准的语法声明游标【格式】DECLARE游标名[INSENSITIVE][SCROLL]CURSORFORSelect_Statement[FOR{READONLY|UPDATE[OF列名[,...n]]}]说明1、INSENSITIVE

声明静态游标将Select_Statement的查询结果放在Tempdb数据库的一个临时表中，通过游标进行的操作也在此临时表里进行，所有对基本表的改动都不会在游标中体现出来，如果省略此关键字，那么用户对基本表所做的任何改动，都将在游标中体现出来。2、SCROLL：声明滚动游标说明此游标具有如下所有取数功能：

SCROLL选项含义FIRST提取游标中的第一行数据LAST提取游标中的最后一行数据PRIOR提取游标当前位置的上一行数据NEXT提取游标当前位置的下一行数据(默认)RELATIVEn提取游标当前位置之前或之后的第n行数据(n为正表示向后，n为负表示向前)ABSULUTEn提取游标中的第n行数据3、Select_Statement：是定义游标结果集的SELECT语句。在游标声明的select_statement内不允许使用关键字COMPUTE、COMPUTEBY和INTO。4、READONLY

：声明此游标为只读游标，不允许通过它进行数据更新。5、UPDATE[OF列名[,...n]]

：定义此游标可更新的列，如果没有[OF列名[,...n]]

，那么游标里所有的列都可以被更新示例【例】定义一个游标从S表中提取计算机系学生信息包括（学号,姓名,性别），此游标为静态、滚动，只读游标。DECLAREjsjxINSENSITIVESCROLLCURSORFORSelect

学号,姓名,性别FromSWhere系='计算机'FORREADONLY打开游标【格式】

OPEN

游标名|游标变量名【说明】：打开游标时，服务器将执行游标定义中SELECT语句，获取游标中的数据；游标打开时指针位于第一行数据之前。若游标以STATIC选项声明的，系统将创建一个临时表来放置结果集。如果游标是以KEYSET选项声明的，系统将创建一个临时表来放置关键字值。这些临时表都储存在tempdb数据库中。【功能】打开由‘游标名’指定的游标或由游标变量名引用的游标游标打开后，可以使用全局变量@@CURSOR_ROWS返回当前游标可操作的数据行数。取值有四种情况：

-m、-1、0、n。【格式】CLOSE游标名|游标变量名【功能】清除该游标的数据结果集和加在数据行上的锁，但不释放该游标占用的存储空间（游标数据结构依旧存在），要想再次使用该游标，不需声明直接打开即可。

关闭游标

释放游标【格式】DEALLOCATE游标名|游标变量名

【功能】

释放游标占用的存储空间，要想再次使用该游标，必须重新声明使用游标操作基表数据使用游标读取基表数据使用游标修改基表数据使用游标删除基表数据使用游标读取数据【格式】FETCH[NEXT|PRIOR|FIRST|LAST

|ABSOLUTE{n|@nvar}

|RELATIVE{n|@nvar}

]

FROM

游标名|游标变量名

[INTO@变量名[,...n]]INTO@变量名[,...n]：将从游标中读取出的数据保存在变量中以备使用。其中游标中的行有多少个字段就应有相同个数的变量保存各字段内容，且变量的数据类型必须与游标中取出各数据的类型完全匹配。若INTO@变量名[,...n]省略，则取出的各行数据显示在查询设计器的结果窗格中。注意每次取数前都应检查全局量@@FETCH_STATUS的值，以确保新取数位置的有效性。@@FETCH_STATUS的取值及含义如下表：若@@FETCH_STATUS的值不为0，取数操作无效值描述0FETCH命令已成功执行-1FETCH命令失败或者数据行己超出了结果集范围-2所读取的数据已经不存在【例】声明一个全局，滚动静态游标GoodScorecur，提取成绩大于90分的所有选课记录；打开该游标读取游标中的指定行，最后关闭释放该游标。--声明游标DECLAREGoodScorecurCURSORGLOBALSCROLLSTATICFORSELECT*FROMSCWHERE成绩>90--打开游标OPENGoodScorecur示例游标取数：FetchlastfromGoodScorecur结果为：FetchpriorfromGoodScorecur结果为：Fetchabsolute2fromGoodScorecur结果为：Fetchrelative3fromGoodScorecur结果为：Fetchrelative-2fromGoodScorecur结果为：上例若要求逐行输出游标中的数据，最后关闭释放该游标，应如何实现？while(@@fetch_status=0)

FetchnextfromGoodScorecur示例

若要求逐行输出游标中的数据到局部变量中，最后关闭释放该游标，应如何实现？

--输出提示信息

PRINT'学号'+''+'课程号'+''+'成绩'--声明取数变量DECLARE@XHchar(5),@KCHchar(3),@CJtinyint--游标打开时指针向下推一位到第一行数据

FETCHNEXTFROMGoodScorecurINTO@XH,@KCH,@CJ--逐行取数，并输出

WHILE@@FETCH_STATUS=0BEGINPRINT@XH+''+@KCH+''+CONVERT(char(3),@CJ)FETCHNEXTFROMGoodScorecurINTO@XH,@KCH,@CJEND--最后关闭、释放游标CLOSEGoodScorecurDEALLOCATEGoodScorecurGO游标与基表的外部更新若在上述静态游标GoodScorecur的取数过程中，外界更新了SC表，例如向SC表中插入一新行：updateSCset成绩=100where学号=‘J0401'and课程号='c02'观察该行能否在此游标中读到？若将此游标修改成dynamic或keyset游标呢？试验--声明静态游标GoodScorecurDECLAREGoodScorecurCURSORGLOBALSCROLLSTATICFORSELECT*FROMSCWHERE成绩>90--打开游标OPENGoodScorecur--输出提示信息

PRINT'学号'+''+'课程号'+''+'成绩'--声明取数变量

DECLARE@XHchar(5),@KCHchar(3),@CJint--取第一行数据FETCHNEXTFROMGoodScorecurINTO@XH,@KCH,@CJif(@@FETCH_STATUS=0)PRINT@XH+''+@KCH+''+CONVERT(char(3),@CJ)elsePrint@@FETCH_STATUSgo/*外界修改基表SC满足要求的下一条记录的成绩值*/

--取下一行数据DECLARE@XHchar(5),@KCHchar(3),@CJintFETCHNEXTFROMGoodScorecurINTO@XH,@KCH,@CJif(@@FETCH_STATUS=0)PRINT@XH+''+@KCH+''+CONVERT(char(3),@CJ)elsePrint@@FETCH_STATUS观察：取出的值为修改前的旧值练习1、声明一个局部，滚动游标m_cursor

，从S表中提取男生的学号、姓名、所在系；打开该游标，逐行输出游标中的数据，最后关闭释放该游标。--声明游标DECLAREs_cursorcursorLOCALSCROLLForSelect学号,姓名,系FromsWhere性别=‘男’--打开游标Opens_cursor--逐行取数Fetchnextfroms_cursorWhile@@FETCH_STATUS=0

Begin

FetchNextFroms_cursor

End--关闭、释放游标CLOSEs_cursorDEALLOCATEs_cursorGO使用游标修改数据若要用游标对基表的数据行进行更新，需要在声明游标时使用“FORUPDATE[OF列名]”选项。使用游标修改数据时，须使用“WHERECURRENTOF游标名”来定位要修改的数据行。

【例】声明一个可更新的局部、滚动游标GoodScorecur从SC表中提取成绩大于等于90分的记录，并设定可更新的字段为成绩。然后利用该游标将基表中的所有大于等于90分的记录成绩减5分。--声明游标

DECLAREGoodScorecurCURSORLOCALSCROLLFORSELECT*FROMSCWHERE成绩>=90

FORUPDATEOF成绩--打开游标

OPENGoodScorecur--游标指针向下推进一位，指向第一行数据

FETCHNEXTFROMGoodScorecur--游标取数，逐行修改成绩WHILE@@FETCH_STATUS=0BEGINUPDATESCSET成绩=成绩-5

WHERECURRENTOFGoodScorecur

FETCHNEXTFROMGoodScorecurEND--关闭、释放游标CLOSEGoodScorecurDEALLOCATEGoodScorecurGO使用游标删除数据若要用游标删除基表的数据行，需要在声明游标时使用FORUPDATE[OF列名]选项。由于删除数据行时涉及到该行的所有字段，因此OF列名应包括所有字段，或省略OF列名列表。用游标删除基表的数据行时，须使用“WHERECURRENTOF游标名”来定位要删除的数据行。删除完毕后，基表中相应的数据行被删除。【例】声明一个可更新的游标，从SC表中提取成绩大于等于90分的记录。然后利用该游标将基表中的所有大于等于90分的记录删除。--声明游标

DECLAREGoodScorecurCURSORLOCALSCROLLFORSELECT*FROMSCWHERE成绩>=90

FORUPDATE--打开游标

OPENGoodScorecur--游标指针向下推进一位，指向第一行数据

FETCHNEXTFROMGoodScorecur--游标取数，逐行删除

WHILE@@FETCH_STATUS=0BEGINDELETEFROMSC

WHERECURRENTOFGoodScorecur

FETCHNEXTFROMGoodScorecurEND--关闭、释放游标CLOSEGoodScorecurDEALLOCATEGoodScorecurGO使用cursor数据类型的参数

存储过程只能将cursor数据类型用于OUTPUT参数如果为某个参数指定了cursor数据类型，也必须指定VARYING和OUTPUT参数。OUTPUT游标参数用来将存储过程的局部游标传回调用该存储过程的批处理、存储过程或触发器。示例首先，创建存储过程CJCX_cursor，在SC表上声明并打开一个游标，记录满足要求的记录信息：接下来，声明一个局部游标变量@MyCursor，执行上述存储过程CJCX_cursor将它的输出参数赋值给@MyCursor，然后从游标@MyCursor中提取行【例】编写一个带有输入参数和游标类型输出参数的存储过程CJCX_cursor,功能是给定一个成绩@score，查询出SC表中成绩大于@score的选课信息，执行存储过程CJCX_cursor，逐行输出满足要求的行。--创建存储过程CJCX_cursorIFEXISTS(SELECTnameFROMsysobjectsWHEREname='CJCX_cursor'andtype='P')DROPPROCEDURECJCX_cursorGOCREATEPROCEDURECJCX_cursor@SCOREINT,@CJCX_curCURSORVARYINGOUTPUTASDeclareCCURSORFORWARD_ONLYSTATICFORSELECT*FROMSCWhere成绩>=@ScoreSET@CJCX_cur=COPEN@CJCX_curGO--声明局部变量DECLARE@MyCursorCURSOR,@scoreintDECLARE@XHchar(9),@KCHchar(4),@CJtinyint--给局部变量赋值Set@score=90--执行存储过程，将游标型的输出参数赋给局部游标EXECcjcx_cursor@score,@MyCursorOUTPUT--读取游标，按行取数FETCHNEXTFROM@MyCursorINTO@XH,@KCH,@CJPRINT'学号'+''+'课程号'+''+'成绩'WHILE(@@FETCH_STATUS=0)BEGINPRINT@XH+''+@KCH+''+CONVERT(char(3),@CJ)FETCHNEXTFROM@MyCursorINTO@XH,@KCH,@CJEND--关闭、释放游标CLOSE@MyCursorDEALLOCATE@MyCursorGO修改用户定义的数据类型用户定义的数据类型一经定义，无法修改，只能删除，然后按照新的要求重新创建用户定义的数据类型。故用户定义的数据类型应适用于那些数据规则和默认值相对统一，而且数据宽度、数据类型很少变化的情况。例如：身份证号码、电话号码、邮政编码等若在一个系统中确需对已被多个表引用的用户定义的数据类型进行修改，例如，由于系统升级，身份证号码字段的数据宽度发生变化。此时，若采用手工方法逐个表修改，效率较低，如何编写程序实现对用户定义的数据类型的自动修改？分析

在SQLServer数据库中：所有的数据库对象（包括约束、默认值、日志、规则、存储过程等）的基本信息保存在系统表sysobjects中。每个对象在表sysobjects中占一行所有的列定义保存在系统表syscolumns中，每个表或视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行sysobject和syscolumns两个表通过ID列（系统为每个数据库对象分配的一个唯一的标示号）关联分析所有的数据类型的定义保存在系统表systypes中，每种系统提供的数据类型或用户定义的数据类型，占一行信息。该表的主要字段如下：systypes系统表与系统表syscolumns通过xusertype字段关联。系统数据类型的Xusertype的取值小于等于256,用户定义的数据类型Xusertype取值大于256systypes表的主要字段解决思路基于此，通过这三个表关联查询，就可以获取当前数据库中所有引用了某个指定用户定义的数据类型的表及其列信息。--添加用户定义的数据类型EXECsp_addtype'POSTCODE','char(7)','NOTNULL'GO--创建引用用户定义类型的表CREATETABLEt1(PCPOSTCODE)CREATETABLEt2(PCPOSTCODE)GO--三表连接查询确定当前库中所有引用了某个指定用户定义的数据类型的表及其列信息。SELECT,FROMsysobjectso,syscolumnsc,systypestWHEREo.type='u'ANDt.Name='postcode'ANDo.id=c.idANDc.xusertype=t.xusertype解决思路已知所有引用该用户定义的数据类型的表及对应列，可构建SQL语句，修改该列数据类型为新数据类型ALTERTABLEtable_nameALTERCOLUMNcolumn_name

new_data_type[(precision[,scale])]由于引用该用户定义的数据类型的表及对应列不止一个，三表连接查询返回的结果不止一个，可结合游标操作，逐个修改各表的相关列为新的数据类型【演示】如何修改已被表t1和t2引用的数据类型postcode为新的数据类型postcode2：

--创建一个新的用户定义类型‘POSTCODE2’,长度为8，可为空：

EXECsp_addtype'POSTCODE2','CHAR(8)'DECLARE@tnamenvarchar(100),@colnamenvarchar(100),@sqlnvarchar(1000)--声明一个游标C，返回引用指定用户定义的数据类型的表名及对应的列名DECLARECCURSORLOCALFORSELECT,FROMsysobjectso,syscolumnsc,systypestWHEREo.type='u'ANDt.Name=‘postcode'ANDo.id=c.idANDc.xusertype=t.xusertype--打开游标OPENC--游标取数，对取出的数据做相应的修改FETCHNEXTFROMCINTO@tname,@colnameWHILE@@FETCH_STATUS=0BEGIN

set@sql='ALTERTABLE'+@tname+'ALTERCOLUMN'+@colname+'postcode2'EXEC(@sql)