第13章游标及事务.ppt

第13章游标及事务13.1游标的定义及优点13.2游标的使用13.3使用游标修改数据13.4事务的使用返回目录213.1游标的定义及其优点前面介绍的数据检索方法可以得到数据库中有关表的数据，但这些数据是作为一个结果集得到的，用户可以把这个结果集保存到一个文件里，或生成一个新表以便于以后使用。这种查询是非常重要的。但这种查询形式有一个很大的缺点，它不能对结果集中每一行的数据进行处理。使用游标可以实现对查询结果集中的数据逐行处理。13.1.1游标的概念游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。313.1.2使用游标的优点从游标定义可以得知游标具有如下的优点:•允许程序对由查询语句SELECT返回的行集合中的每一行数据执行相同或不同的操作，而不是对整个行集合执行同一个操作•提供对基于游标位置的表中的行进行删除和更新的能力•游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来本章首页413.2游标的使用13.2.1使用游标的步骤使用游标的工作流程如下图13-1所示。具体地说，有如下几个步骤：•①创建游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。•②打开游标。•③从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。•④对游标中的数据逐行操作。•⑤关闭和释放游标。513.2.2游标的定义及使用过程1.声明游标声明游标是指用DECLARE语句声明或创建一个游标。声明游标的语法如下：DECLAREcursor_name[SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name_list]}]其中：•cursor_name:是游标的名字，为一个合法的SQLServer标识符，游标的名字必须遵循SQLServer命名规范。•SCROLL：表示取游标时可以使用关键字NEXT、PRIOR、FIRST、LAST、ABSOLUTE、RELATIVE。每个关键字的含义将在介绍FETCH子句时讲解。•select_statement：是定义游标结果集的标准SELECT语句，它可以是一个完整语法和语义的Transact-SQL的SELECT语句。6•但是这个SELECT语句必须有FROM子句，不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。•FORREADONLY：指出该游标结果集只能读，不能修改。•FORUPDATE：指出该游标结果集可以被修改。•OFcolumn_name_list：列出可以被修改的列的名单。应该注意:•①游标有且只有两种方式：FORREADONLY或FORUPDATE。•②当游标方式指定为FORREADONLY时，游标涉及的表不能被修改。•③当游标方式指定为FORUPDATE时，可以删除或更新游标涉及的表中的行。通常，这也是缺省方式。•④声明游标的DECLARECURSOR语句必须是在该游标的任何OPEN语句之前。72.打开游标打开游标是指打开已被声明但尚未被打开的游标，打开游标使用OPEN语句。打开游标的语法如下：OPENcursor_name其中：•cursor_name是一个已声明的尚未打开的游标名。注意：•①当游标打开成功时，游标位置指向结果集的第一行之前。•②只能打开已经声明但尚未打开的游标。83.从打开的游标中提取行游标被打开后，游标位置位于结果集的第一行前，此时可以从结果集中提取（FETCH）行。SQLServer将沿着游标结果集一行或多行向下移动游标位置，不断提取结果集中的数据，并修改和保存游标当前的位置，直到结果集中的行全部被提取。从打开的游标中提取行的语法如下：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE]FROM]cursor_name[INTOfetch_target_list]其中：•cursor_name：为一已声明并已打开的游标名字。•NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是NEXT，即向下移动。•NEXT：取下一行数据。•PRIOR：取前一行数据。•FIRST：取第一行数据。9LAST：取最后一行数据。ABSOLUTE：按绝对位置取数据。RELATIVE：按相对位置取数据。游标位置确定了结果集中哪一行可以被提取，如果游标方式为FORUPDATE的话，也就确定该位置一行数据可以被更新或删除。•INTOfetch_target_list：指定存放被提取的列数据的目的变量清单。这个清单中变量的个数、数据类型、顺序必须与定义该游标的select_statement的SELECT_list中列出的列清单相匹配。为了更灵活地操纵数据，可以把从已声明并已打开的游标结果集中提取的列数据，分别存放在目的变量中。10有两个全局变量提供关于游标活动的信息：①@@FETCH_STATUS保存着最后FETCH语句执行后的状态信息，其值和含义如下：•0：表示成功完成FETCH语句。•-1：表示FETCH语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中不再有数据。•-2：提取的行不存在。②@@rowcount保存着自游标打开后的第一个FETCH语句，直到最近一次的FETCH语句为止，已从游标结果集中提取的行数。每个打开的游标都与一特定的@@rowcount有关，关闭游标时，该@@rowcount变量也被删除。在FETCH语句执行后查看这个变量，可得知从游标结果集中已提取的行数。114.关闭游标关闭(Close)游标是停止处理定义游标的那个查询。关闭游标并不改变它的定义，可以再次用open语句打开它，SQLServer会用该游标的定义重新创建这个游标的一个结果集。关闭游标的语法如下：CLOSEcursor_name其中：•cursor_name：是已被打开并将要被关闭的游标名字。•在如下情况下，SQLServer会自动地关闭已打开的游标：当你退出这个SQLServer会话时从声明游标的存储过程中返回时125.释放游标释放(Deallocate)游标是指释放所有分配给此游标的资源，包括该游标的名字。释放游标的语法是：DEALLOCATECURSORcursor_name其中：•cursor_name：将要被DEALLOCATE释放的游标名字。如果释放一个已打开但未被关闭的游标，SQLServer会自动先关闭这个游标，然后再释放它。注意一下关闭游标与释放游标的区别：关闭游标并不改变游标的定义，一个游标关闭后，不需要再次声明，就可以重新打开并使用它。但一个游标释放后，就释放了与该游标有关的一切资源，也包括游标的声明，游标释放后就不能再使用该游标了，如需再次使用游标，就必须重新定义。13【例13-1】定义一个游标，将教师表teacher中所有教师的教师姓名、教师职称显示出来。USEjwglGODECLARE@teach_nameVARCHAR(8),@teach_professionVARCHAR(16)DECLAREteacher_coursorSCROLLCURSORFORSELECTteacher_name,professionFROMteacherFORREADONLYOPENteacher_coursorFETCHFROMteacher_coursorINTO@teach_name,@teach_profession14WHILE@@FETCH_STATUS=0BEGINPRINT‘教师姓名:’+@teach_name+‘’+‘职称:‘+@teach_professionFETCHFROMteacher_coursorINTO@teach_name,@teach_professionENDCLOSEteacher_coursorDEALLOCATEteacher_coursor本章首页1513.3使用游标修改数据用户可以在UPDATE或DELETE语句中使用游标来更新、删除表或视图中的行，但不能用来插入新行。13.3.1更新数据通过在UPDATE语句中使用游标可以更新表或视图中的行。被更新的行依赖于游标位置的当前值。更新数据语法形式如下：UPDATE{table_name|view_name}SET[[{table_name.|view_name.}]column_name={new_value}[...n]WHERECURRENTOFcursor_name其中：•紧跟UPDATE之后的table_name|view_name：要更新的表名或视图名，可以加或不加限定。但它必须是声明该游标的SELECT语句中的表名或视图名。16•column_name：是要更新的列的列名，可以加或不加限定。但它们必须是声明游标的SELECT语句中UPDATEOFcolumn_name_list的子集•new_value：为被更新列的新值，它可以是一个表达式、空值或子查询。•WHERECURRENTOF：使SQLServer只更新由指定游标的游标位置当前值确定的行。cursor_name：是已声明为FORUPDATE方式并已打开的游标名。17注意：•①使用UPDATE...CURRENTOF语句一次只能更新当前游标位置确定的那一行，OPEN语句将游标位置定位在结果集第一行前，可以使用FETCH语句把游标位置定位在要被更新的数据行处。•②用UPDATE...WHERECURRENTOF语句更新表中的行时，不会移动游标位置，被更新的行可以再次被修改，直到下一个FETCH语句的执行。•③UPDATE...WHERECURRENTOF语句可以更新多表视图或被连接的多表，但只能更新其中一个表的行，即所有被更新的列都来自同一个表。18【例13-2】通过游标将教师表teacher中记录号为3的章红老师的职称由“讲师”改为“教授”。USEjwglGODECLAREteacher_coursorSCROLLCURSORFORSELECTteacher_name,professionFROMteacherFORUPDATEOFprofessionOPENteacher_coursorFETCHABSOLUTE3FROMteacher_coursorUPDATEteacherSETprofession='教授'WHERECURRENTOFteacher_coursorFETCHABSOLUTE3FROMteacher_coursorCLOSEteacher_coursorDEALLOCATEteacher_coursor1913.3.2删除数据通过在DELETE语句中使用游标来删除表或视图中的行。被删除的行依赖于游标位置的当前值。删除数据语法形式如下：DELETE[FROM][[database.]owner.]{table_name|view_name}WHERECURRENTOFcursor_name其中：•table_name|view_name：为要从其中删除行的表名或视图名，可以加或不加限定。但它必须是定义该游标的SELECT语句中的表名或视图名。•cursor_name：为已声明并已打开的游标名。•WHERECURRENTOF：它使SQLServer只删除由指定游标的游标位置当前值确定的行。20注意：•①使用游标的DELETE语句，一次只能删除当前游标位置确定的那一行。OPEN语句将游标位置定位在结果集第一行之前，可以用FETCH语句把游标位置定位在要被删除的行