第10章 SQL Server数据库应用开发技术

第10章游标及事务知识技能目标:1．理解游标的概念，掌握利用游标逐行处理数据行的方法。2．理解事务的概念，掌握事务的使用方法。引导案例：通过在教务管理数据库中的学生表上建立触发器实现了对班级表中相应班级人数的自动更新。但是当一次修改多条记录时，触发器却只被触发一次，即人数只增加或减少1。如何解决这个问题呢？这就需要使用本章介绍的游标技术。在某些应用中需要把一些操作作为一个整体，或者都做，或者都不做，本章介绍利用事务解决此类问题的方法。第10章游标及事务10.1游标10.2事务小结第10章游标及事务10.1游标在某些时候，对查询结果集中的不同行，需要不同的处理方式，也就是需要逐行访问数据，而不能将整个结果集作为一个单元来按照相同的方式处理，此时，就需要用到游标技术。比如，当用INSERT_SELECT语句向学生表中插入多条记录，这多条记录的班级可能不全相同，这时，需要修改班级表中的相关班级的人数，就需要逐条处理学生表中插入的记录。返回目录10.1游标游标是一种可以从包含多条记录的结果集中逐条访问数据行的机制，利用游标，可以定位、检索或修改特定的行。游标由结果集和结果集中指向特定记录的游标位置（可以理解为一个位置指针）组成。Transact-SQL游标主要用在存储过程、触发器和Transact-SQL脚本中。返回目录10.1.1游标的概念10.1游标10.1.2游标的优点（1）允许应用程序对查询语句SELECT返回的行结果集中每一行数据进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；（2）提供对基于游标位置对表中数据进行删除或更新的能力；（3）作为面向集合的RDBMS和面向行的程序设计之间的桥梁，使这两种处理方式通过游标联系起来。返回目录10.1游标10.1.3游标的使用步骤1．声明游标语法格式：DECLARE游标名CURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|DYNAMIC][READ_ONLY]FORSELECT语句[FORUPDATE[OF列名[,...n]]]功能：声明游标，定义游标的属性。返回目录10.1游标10.1.3游标的使用步骤说明：1）游标名：所定义的Transact-SQL服务器游标的名称。2）SELECT语句：定义游标结果集的标准SELECT语句。3）FORWARD_ONLY：指定游标只能从第一行滚动到最后一行，不能向后滚动。4）SCROLL：游标可以向前、向后滚动。5）READONLY：禁止通过该游标更新结果集中的数据。6）UPDATE[OF列名[,...n]]：定义游标中可更新的结果集中的列。7）LOCAL：指定对于在其中创建的批处理、存储过程或触发器来说，该游标的作用域是局部的。返回目录10.1游标10.1.3游标的使用步骤说明：8）GLOBAL：指定该游标的作用域对连接来说是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。9）STATIC：在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。10）DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。注意：在游标声明的SELECT语句内不允许使用关键字COMPUTE、COMPUTEBY、FORBROWSE和INTO。返回目录10.1游标10.1.3游标的使用步骤2．打开游标语法格式：OPEN游标名说明：使用OPEN语句执行SELECT语句并填充游标结果集。打开游标后，可以使用@@CURSOR_ROWS函数返回游标中行的数目。返回目录10.1游标10.1.3游标的使用步骤3．利用游标访问数据语法格式：FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEn][INTO@变量名[,...n]]功能：从结果集提取（即检索）单个行，并将每列中的数据移至指定的变量中，以便其他Transact-SQL语句引用那些变量来访问提取的数据值。根据需要，可以对游标中当前位置的行执行修改操作（更新或删除）。返回目录10.1游标10.1.3游标的使用步骤说明：1）NEXT：当前行的下一行成为新的当前行返回。如果FETCHNEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT是默认的游标提取选项。2）PRIOR：当前行的上一行成为新的当前行返回。如果FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。3）FIRST：返回游标中的第一行并将其作为当前行。4）LAST：返回游标中的最后一行并将其作为当前行。返回目录10.1游标10.1.3游标的使用步骤说明：5）ABSOLUTEn：如果n为正数，则返回从游标头开始的第n行，并将返回行变成新的当前行。如果n或@nvar为负数，则返回从游标末尾开始的第n行，并将返回行变成新的当前行。如果n或@nvar为0，则不返回行。6）RELATIVEn：如果n为正数，则返回从当前行开始之后的第n行，并将返回行变成新的当前行。如果n为负数，则返回当前行之前第n行，并将返回行变成新的当前行。如果n为0，则返回当前行。可以用@@FETCH_STATUS函数报告上一个FETCH语句的状态，其中0：FETCH语句成功；-1：FETCH语句失败或行不在结果集中；-2：提取的行不存在。返回目录10.1游标10.1.3游标的使用步骤4．关闭游标语法格式：CLOSE游标名说明：当使用游标的时候，会锁定与其关联的记录集，所以当不用游标的时候，要关闭游标以释放游标结果集及其对当前行的锁定。但CLOSE将保留游标结构以便重新打开，在重新打开游标之前，不允许提取和定位更新。当重新发出一个OPEN语句，该游标结构仍然可用。返回目录10.1游标10.1.3游标的使用步骤5．释放游标语法格式：DEALLOCATE[GLOBAL]游标名说明：DEALLOCATE语句完全释放分配给游标的资源，如游标名称。释放游标后，就不能再使用，除非使用DECLARE语句重新生成游标。返回目录10.1游标10.1.3游标的使用步骤【例10.1】使用游标逐行显示数据。【例10.2】创建触发器“增加学生”，以保证在向学生表插入记录时，班级表中的学生人数相应正确更新。当用INSERT_SELECT语句在学生表中插入多条记录，用第9章介绍的插入触发器不能正确地修改班级表中的人数。本例解决上述问题，在触发器中使用游标逐行处理数据。返回目录10.1游标10.1.4使用游标修改数据如果游标没有声明为只读游标，就可以利用游标修改游标基表中当前行的字段值。用于游标时，一个UPDATE语句只能修改一行游标基表中的数据。语法格式：UPDATE表名SET列名=表达式WHERECURRENTOF游标名【例10.3】使用游标更新“班级表”，将班主任“马力”改为“马利”。返回目录10.1游标10.1.5使用游标删除数据如果游标没有声明为只读游标，也可以利用游标删除游标基表中当前行。用于游标时，一个DELETE语句只能删除一个游标基表中的数据。语法格式：DELETEFROM表名WHERECURRENTOF游标名【10.4】利用游标删除班级表中的第二行数据。返回目录10.2事务在实际应用中，有些任务需要若干步操作，这若干步操作需要作为一个整体，要么都做，要么都不做，不允许做了一半，另一半不做的情况发生。但是在任务执行的过程中，因为各种意外，可能突然被中断，必须要有某种机制来保证数据的一致性，这种机制就是事务。返回目录10.2事务10.2.1事务的概念事务是用户定义的一组操作序列，是由若干个T_SQL语句组成的一个工作单元。这个单元中的操作，要么都被执行，要么都不被执行：如果某一事务成功，则在该事务中进行的所有数据修改均会被提交，成为数据库中的永久组成部分；如果事务因为某种原因遇到错误且必须取消或回滚，则事务中所有的数据修改均被清除，就像什么都没有做一样，恢复到执行事务之前的状态。事务日志记录着整个事务的所有操作步骤，必要的时候靠日志重新开始事务或者回滚事务。不管出现什么状况，通过事务日志都能保证事务的完整性。返回目录10.2事务10.2.2事务的特性(ACID)（1）原子性(Atomic)：事务必须是原子工作单元。要么全都执行提交，要么全不执行回滚，取消所有的数据修改。（2）一致性(Consistent)：事务在完成时，必须使所有的数据都保持一致状态，保持所有数据的完整性。（3）隔离性(Isolated)：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。即一个事务能看到另一个并发事务修改之后的数据或者修改之前的数据，但是看不到另一个事务正在修改的数据。（4）持久性(Durable)：事务完成之后，它对于系统的影响是永久性的，对数据的修改已经保存到数据库中，不会再被取消。返回目录10.2事务10.2.3事务的模式事务模式按连接级别进行管理。一个连接的事务模式对其他连接的事务模式没有影响。1．显式事务（ExplicitTransactions）每个事务均以BEGINTRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。【例10.5】显式事务的例子。返回目录10.2事务10.2.3事务的模式说明：（1）可以使用COMMIT或ROLLBACK语句来结束事务。COMMIT：如果事务成功，则提交。ROLLBACK：如果事务中出现错误，或用户决定取消事务，则回滚该事务。返回目录10.2事务10.2.3事务的模式（2）指定当Transact-SQL语句出现运行时错误时，SQLServer是否自动回滚当前事务。语法格式：SETXACT_ABORTON|OFF当SETXACT_ABORT为ON时，如果执行Transact-SQL语句产生运行时错误，则整个事务将终止并回滚。当SETXACT_ABORT为OFF时，有时只回滚产生错误的Transact-SQL语句，而事务将继续进行处理。OFF是默认设置。返回目录10.2事务10.2.3事务的模式2.隐式事务（ImplicitTransactions）在SETIMPLICIT_TRANSACTIONSON命令之后的第一条语句开始，就开始一个新的事务，直到遇到COMMIT或ROLLBACK语句结束这个事务，下一个语句又是一个新的事务，同样直到遇到COMMIT或ROLLBACK语句结束这个事务。这样形成了一个事务链。【例10.6】隐式事务的例子本例有三个事务，第一个和第三个提交，第二个回滚，最后有四条记录被添加到班级表2。返回目录10.2事务10.2.3事务的模式3．自动提交事务（AutocommitTransactions）自动提交事务模式下，每条单独的语句都是一个事务，成功了就提交，这句语句执行错误就回滚这条语句，对其他语句的执行不产生影响。【例10.7】自动提交事务。当执行第三个插入语句时，由于字段过长而出错，本句回滚。但是不影响其它语句。返回目录10.2事务10.2.3事务控制1．事务设置语句（1）设置自动回滚模式SETXACT_ABORTON|OFF设置当事务中有一条语句产生运行错误时，取消整个事务（ON）还是只回滚错误语句（OFF）。（2）设置隐形事务模式SETIMPLICIT_TRANSACTIONSON|OFF设置启动（ON）/关闭（OFF）隐形事务模式。返回目录10.2事务10.2.3事务控制2．事务控制语句（1）BEGINTRAN[SACTION]显式定义一个事务的开始（2）COMMITTRAN[SACTION]事务名或COMMIT[WORK]提交事务，对数据的修改将永久保存到数据库中，不能再回滚。（3）SAVETRAN[SACTION]保护点名在事务内设置保护点。当发生错误时，可以使事务返回到保护点。（4）ROLLBACKTRAN[SACTION]事务名|保护点名或ROLLBACK[WORK]回滚事务，清除自事务的起点或从某保护点起所