第11章 事务与游标

数据库实用教程——SQLServer2008第11章事务和游标本章学习目标：•理解事务的概念•掌握事务控制的使用•理解游标的概念•掌握游标的定义和使用方法11.1事务•在SQLServer中，事务是完成一个应用处理的最小单元，由一个或多个对数据库操作的语句组成。事务作为一个完整的执行单元，如果执行成功，事务中进行的数据更新会全部提交；如果事务中有一个语句执行失败，则取消全部操作，并将数据库恢复到事务执行之前的状态。11.1.1事务概述在SQLServer中，事务作为单个逻辑工作单元来执行一系列操作，具有4个特点（ACID属性）：原子性、一致性、隔离性和持久性。•原子性：事务必须是原子工作单元，事务中的操作要么全都执行，要么全都不执行。•一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据都必须是正确的。•隔离性：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务操作数据时数据的状态，要么是另一个并发事务修改它之前的状态，要么是另一个事务修改它之后的状态，事务不会处理中间状态的数据。•持久性：事务完成之后，它对于系统的影响是永久性的。11.1.2自动提交事务•自动提交模式是SQLServer数据库引擎的默认事务管理模式。每个Transact-SQL语句在完成时，都被提交或回滚。如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。只要没有显式事务或隐性事务覆盖自动提交模式，与数据库引擎实例的连接就以此默认模式操作。在与SQLServer连接之后，直接进入自动事务模式，直到使用BeginTransaction语句启动一个显式事务，或执行SetIMPLICIT_TRANSACTIONSOn语句将隐式事务模式开启为止。•当提交或回滚显式事务，或执行SetIMPLICIT_TRANSACTIONSOff关闭隐性事务模式时，连接又返回到自动提交模式。11.1.3显式事务•显示事务是指由用户通过Transact-SQL事务语句定义的事务。常用的Transact-SQL事务语句有：•BeginTransaction语句：标记一个本地事务的开始。•CommitTransaction语句：标记一个显式事务或隐式事务的结束，表明事务已经成功执行，并将事务内所做的全部修改保存到数据库中。•RollbackTransaction：回滚显式事务或隐式事务到事务的起点或事务内部的保存点。•SaveTransaction语句：在事务内部设置保存点，这个保存点是在取消事务的某一部分后，该事务可以返回的一个位置。1.BeginTransaction•BeginTransaction语句定义一个本地显式事务的起点，并将全局变量@@TranCount的值加1，具体的语法格式如下：•BeginTran|Transaction[transaction_name|@tran_name_variable]•说明：•transaction_name：事务的名称。transaction_name必须符合标识符规则，但标识符所包含的字符数不能大于32。在一系列嵌套的事务中，用一个事务名或多个事务名对该事务并没有什么影响，系统仅登记第一个（最外层）事务名。•@tran_name_variable：由用户定义的、含有有效事务名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明变量。如果传递给该变量的字符多于32个，则仅使用前面的32个字符，其余的字符将被截断。2.CommitTransaction•CommitTransaction语句标志一个事务成功执行的结束。如果全局变量@@TranCount的值为1，则CommitTransaction将提交从事务开始以来所执行的所有数据修改，释放事务处理所占用的资源，并使@@TranCount的值为0。如果@@TranCount的值大于1，则CommitTransaction命令将使@@TranCount的值减1，并且事务将保持活动状态。具体的语法为：•CommitTran|Transaction[transaction_name|@tran_name_variable]•说明：•transaction_name：transaction_name指定由前面的BeginTransaction分配的事务名称。transaction_name必须符合标识符规则，但不能超过32个字符。transaction_name通过向程序员指明CommitTransaction与哪些BeginTransaction相关联，可作为帮助阅读的一种方法。•@tran_name_variable：由用户定义的、含有有效事务名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明变量。如果传递给该变量的字符多于32个，则仅使用前面的32个字符；其余的字符将被截断。•当在嵌套事务中使用时，内部事务的提交并不释放资源或使其修改成为永久修改。只有在提交了外部事务时，数据修改才具有永久性，而且资源才会被释放。当@@TranCount大于1时，每发出一个CommitTransaction命令只会使@@TranCount按1递减。当@@TRANCOUNT最终递减为0时，将提交整个外部事务。•当@@TranCount为0时发出CommitTransaction将会导致出现错误，因为没有相应的BeginTransaction。3.RollbackTransaction•RollbackTransaction语句回滚显式事务或隐式事务到事务的起始位置，或事务内部的保存点，同时释放由事务控制的资源。•RollbackTran|Transaction[transaction_name|@tran_name_variablesavepoint_name|@savepoint_variable]•说明：•transaction_name：transaction_name和@tran_name_variable的含义和BeginTransaction语句中的含义相同。•savepoint_name：SaveTransaction语句定义的保存点的名称。savepoint_name必须符合标识符规则。当回滚只影响事务的一部分时，可使用savepoint_name。•@savepoint_variable：是用户定义的、包含有效保存点名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明变量。•不能在发出CommitTransaction命令之后回滚事务，因为数据修改已经成为了数据库的一个永久部分。4.SaveTransaction•SaveTransaction语句在事务内设置一个保存点，当事务执行到该保存点时，SQLServer存储所有被修改的数据到数据库中，具体的语法格式为：•SaveTran|Transactionsavepoint_name|@savepoint_variable•说明：•savepoint_name：定义的保存点的名称。savepoint_name必须符合标识符规则。•@savepoint_variable：是用户定义的、包含有效保存点名称的变量的名称。必须用char、varchar、nchar或nvarchar数据类型声明变量。•用户可以在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的Transact-SQL语句和CommitTransaction语句，或者必须通过将事务回滚到起始点完全取消事务。•在事务中允许有重复的保存点名称，但指定保存点名称的RollbackTransaction语句只将事务回滚到使用该名称的最近的SaveTransaction。11.1.4隐式事务•当连接以隐性事务模式进行操作时，SQLServer将在提交或回滚当前事务后自动启动新事务。因此，隐式事务不需要使用BeginTransaction语句标志事务的开始，只需要用户使用RollbackTransaction语句或CommitTransaction语句回滚或提交事务。•当使用Set语句将IMPLICIT_TRANSACTIONS设置为On将隐性事务模式打开之后，SQLServer执行下列任何语句都会自动启动一个事务：AlterTable、Create、Delete、Drop、Fetch、Grant、Insert、Open、Revoke、Select、TruncateTable、Update。在发出Commit或Rollback语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行以上任何语句时，数据库引擎实例都将自动启动一个新事务。该实例将不断地生成隐性事务链，直到隐性事务模式关闭为止。11.1.5错误处理语句•SQLServer提供了Try…Catch语句捕获系统错误，与业务处理相关的语句包含在Try块中，如果Try块内部发生错误，系统会将控制传递给Catch块中的语句。Try…Catch语句的具体语法格式为：•BeginTry•sql_statement|statement_block•EndTry•BeginCatch•[sql_statement|statement_block]•EndCatch说明：•sql_statement：任何Transact-SQL语句。•statement_block：批处理或包含于Begin…End块中的任何Transact-SQL语句组。•Try块后必须紧跟相关联的Catch块。在EndTry和BeginCatch语句之间放置任何其他语句都将生成语法错误。•如果Try块所包含的代码中没有错误，则当Try块中最后一个语句完成运行时，会将控制传递给紧跟在相关联的EndCatch语句之后的语句。如果Try块所包含的代码中有错误，则会将控制传递给相关联的Catch块的第一个语句。如果EndCatch语句是存储过程或触发器的最后一个语句，控制将回到调用该存储过程或运行该触发器的语句。•当Catch块中的代码完成时，会将控制传递给紧跟在EndCatch语句之后的语句。由Catch块捕获的错误不会返回到调用应用程序。如果错误消息的任何部分都必须返回到应用程序，则Catch块中的代码必须使用Select结果集或Raiserror和Print语句之类的机制执行此操作。•Try…Catch构造可以嵌套。11.2游标由Select语句返回的行集包括满足该语句的Where子句中条件的所有行，这种由语句返回的完整行集称为结果集。但是有时候应用程序并不总能将整个结果集作为一个单元来有效地处理，这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展。使用游标具有以下优点：•允许程序对由查询语句Select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。•提供对基于游标位置的表中的行进行删除和更新的能力。•游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。11.2.1游标类型MicrosoftSQLServer2008支持两种请求游标的方法：•Transact-SQL：使用Transact-SQL语句定义的游标。•数据库应用程序编程接口（API）游标函数：SQLServer支持以下数据库API的游标功能：•ADO（MicrosoftActiveX数据对象）•OLEDB•ODBC（开放式数据库连接）•根据游标检测结果集变化的能力和消耗资源（如在tempdb中所占的内存和空间）的情况的不同，SQLServer支持的API服务器游标又分为四种：•静态游标：该游标使用数据的临时复本。对游标的所有