《SQL Server数据库应用开发技术》课件第10章.ppt

1、第第1010章章 游标及事务游标及事务 知识技能目标知识技能目标:1理解游标的概念，掌握利用游标逐行处理数理解游标的概念，掌握利用游标逐行处理数 据行的方法。据行的方法。2理解事务的概念，掌握事务的使用方法。理解事务的概念，掌握事务的使用方法。引导案例：引导案例：通过在教务管理数据库中的学生表上建立触发通过在教务管理数据库中的学生表上建立触发器实现了对班级表中相应班级人数的自动更新。但器实现了对班级表中相应班级人数的自动更新。但是当一次修改多条记录时，触发器却只被触发一次，是当一次修改多条记录时，触发器却只被触发一次，即人数只增加或减少即人数只增加或减少1 1。如何解决这个问题呢？这就。如何解

2、决这个问题呢？这就需要使用本章介绍的游标技术。需要使用本章介绍的游标技术。在某些应用中需要把一些操作作为一个整体，在某些应用中需要把一些操作作为一个整体，或者都做，或者都不做，本章介绍利用事务解决此或者都做，或者都不做，本章介绍利用事务解决此类问题的方法。类问题的方法。第第1010章章 游标及事务游标及事务 10.1 10.1 游标游标 10.2 10.2 事务事务小结小结第第1010章章 游标及事务游标及事务 10.1 10.1 游标游标 在某些时候，对查询结果集中的不同行，需要不同的处理方式，也就是需要逐行访问数据，而不能将整个结果集作为一个单元来按照相同的方式处理，此时，就需要用到游标技

3、术。比如，当用INSERT_SELECT语句向学生表中插入多条记录，这多条记录的班级可能不全相同，这时，需要修改班级表中的相关班级的人数，就需要逐条处理学生表中插入的记录。返回目录10.1 10.1 游标游标 游标是一种可以从包含多条记录的结果集中逐条访问数据行的机制，利用游标，可以定位、检索或修改特定的行。游标由结果集和结果集中指向特定记录的游标位置（可以理解为一个位置指针）组成。Transact-SQL 游标主要用在存储过程、触发器和 Transact-SQL 脚本中。返回目录10.1.1 游标的概念游标的概念 10.1 10.1 游标游标10.1.2 游标的优点游标的优点 （1）允许应用

4、程序对查询语句SELECT 返回的行结果集中每一行数据进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；（2）提供对基于游标位置对表中数据进行删除或更新的能力；（3）作为面向集合的RDBMS和面向行的程序设计之间的桥梁，使这两种处理方式通过游标联系起来。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 1 1声明游标声明游标 语法格式：语法格式：DECLARE 游标名 CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|DYNAMIC READ_ONLY FOR SELECT语句 FOR UPDATE OF 列名

5、 ,.n 功能：功能：声明游标，定义游标的属性。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 说明：说明：1）游标名：所定义的Transact-SQL 服务器游标的名称。2）SELECT语句：定义游标结果集的标准 SELECT 语句。3）FORWARD_ONLY：指定游标只能从第一行滚动到最后一行，不能向后滚动。4）SCROLL：游标可以向前、向后滚动。5）READ ONLY：禁止通过该游标更新结果集中的数据。6）UPDATE OF 列名 ,.n ：定义游标中可更新的结果集中的列。7）LOCAL：指定对于在其中创建的批处理、存储过程或触发器来说，该游标的作用域

6、是局部的。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 说明：说明：8）GLOBAL：指定该游标的作用域对连接来说是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在断开连接时隐式释放。9）STATIC：在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。10）DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改。注意：在游标声明的SELECT语句内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE

7、和 INTO。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 2 2打开游标打开游标 语法格式：语法格式：OPEN 游标名 说明：使用 OPEN 语句执行 SELECT 语句并填充游标结果集。打开游标后，可以使用 CURSOR_ROWS 函数返回游标中行的数目。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 3 3利用游标访问数据利用游标访问数据 语法格式：语法格式：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n INTO 变量名 ,.n 功能：功能：从结果集提取（即检索）单个行

8、，并将每列中的数据移至指定的变量中，以便其他 Transact-SQL 语句引用那些变量来访问提取的数据值。根据需要，可以对游标中当前位置的行执行修改操作（更新或删除）。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 说明：说明：1）NEXT：当前行的下一行成为新的当前行返回。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 是默认的游标提取选项。2）PRIOR：当前行的上一行成为新的当前行返回。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。3）FIRST：返回游标中的第一行并将

9、其作为当前行。4）LAST：返回游标中的最后一行并将其作为当前行。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 说明：说明：5）ABSOLUTE n：如果 n为正数，则返回从游标头开始的第 n 行，并将返回行变成新的当前行。如果 n 或 nvar 为负数，则返回从游标末尾开始的第 n 行，并将返回行变成新的当前行。如果 n 或 nvar 为 0，则不返回行。6）RELATIVE n：如果 n为正数，则返回从当前行开始之后的第 n 行，并将返回行变成新的当前行。如果 n为负数，则返回当前行之前第 n 行，并将返回行变成新的当前行。如果 n为 0，则返回当前行。可

10、以用FETCH_STATUS 函数报告上一个FETCH 语句的状态，其中0：FETCH 语句成功；-1：FETCH 语句失败或行不在结果集中；-2：提取的行不存在。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 4 4关闭游标关闭游标语法格式：语法格式：CLOSE 游标名说明：说明：当使用游标的时候，会锁定与其关联的记录集，所以当不用游标的时候，要关闭游标以释放游标结果集及其对当前行的锁定。但CLOSE 将保留游标结构以便重新打开，在重新打开游标之前，不允许提取和定位更新。当重新发出一个 OPEN 语句，该游标结构仍然可用。返回目录10.1 10.1 游标游标1

11、0.1.3 游标的使用步骤游标的使用步骤 5 5释放游标释放游标语法格式：语法格式：DEALLOCATE GLOBAL 游标名说明：说明：DEALLOCATE 语句完全释放分配给游标的资源，如游标名称。释放游标后，就不能再使用，除非使用 DECLARE 语句重新生成游标。返回目录10.1 10.1 游标游标10.1.3 游标的使用步骤游标的使用步骤 【例例10.110.1】使用游标逐行显示数据。使用游标逐行显示数据。【例例10.210.2】创建触发器创建触发器“增加学生增加学生”，以保证在向学生表插入记，以保证在向学生表插入记录时，班级表中的学生人数相应正确更新。录时，班级表中的学生人数相应正

12、确更新。当用INSERT_SELECT语句在学生表中插入多条记录，用第9章介绍的插入触发器不能正确地修改班级表中的人数。本例解决上述问题，在触发器中使用游标逐行处理数据。返回目录10.1 10.1 游标游标10.1.4 使用游标修改数据使用游标修改数据 如果游标没有声明为只读游标，就可以利用游标修改游标基表中当前行的字段值。用于游标时，一个UPDATE语句只能修改一行游标基表中的数据。语法格式：语法格式：UPDATE 表名 SET 列名=表达式 WHERE CURRENT OF 游标名【例例10.310.3】使用游标更新使用游标更新“班级表班级表”，将班主任，将班主任“马力马力”改为改为 “马

13、利马利”。返回目录10.1 10.1 游标游标10.1.5 使用游标删除数据使用游标删除数据 如果游标没有声明为只读游标，也可以利用游标删除游标基表中当前行。用于游标时，一个DELETE语句只能删除一个游标基表中的数据。语法格式：语法格式：DELETE FROM 表名 WHERE CURRENT OF 游标名【10.410.4】利用游标删除班级表中的第二行数据。利用游标删除班级表中的第二行数据。返回目录10.2 10.2 事务事务 在实际应用中，有些任务需要若干步操作，这若干步操作需要作为一个整体，要么都做，要么都不做，不允许做了一半，另一半不做的情况发生。但是在任务执行的过程中，因为各种意外

14、，可能突然被中断，必须要有某种机制来保证数据的一致性，这种机制就是事务。返回目录10.2 10.2 事务事务 10.2.1 事务的概念事务的概念 事务是用户定义的一组操作序列，是由若干个T_SQL语句组成的一个工作单元。这个单元中的操作，要么都被执行，要么都不被执行：如果某一事务成功，则在该事务中进行的所有数据修改均会被提交，成为数据库中的永久组成部分；如果事务因为某种原因遇到错误且必须取消或回滚，则事务中所有的数据修改均被清除，就像什么都没有做一样，恢复到执行事务之前的状态。事务日志记录着整个事务的所有操作步骤，必要的时候靠日志重新开始事务或者回滚事务。不管出现什么状况，通过事务日志都能保证

15、事务的完整性。返回目录10.2 10.2 事务事务 10.2.2 事务的特性事务的特性(ACID)（1）原子性(Atomic)：事务必须是原子工作单元。要么全都执行提交，要么全不执行回滚，取消所有的数据修改。（2）一致性(Consistent)：事务在完成时，必须使所有的数据都保持一致状态，保持所有数据的完整性。（3）隔离性(Isolated)：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。即一个事务能看到另一个并发事务修改之后的数据或者修改之前的数据，但是看不到另一个事务正在修改的数据。（4）持久性(Durable)：事务完成之后，它对于系统的影响是永久性的，对数据的修改已经保存

16、到数据库中，不会再被取消。返回目录10.2 10.2 事务事务 10.2.3 事务的模式事务的模式 事务模式按连接级别进行管理。一个连接的事务模式对其他连接的事务模式没有影响。1 1显式事务（显式事务（Explicit TransactionsExplicit Transactions）每个事务均以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。【例例10.510.5】显式事务的例子。显式事务的例子。返回目录10.2 10.2 事务事务 10.2.3 事务的模式事务的模式 说明：说明：（1）可以使用 COMMIT 或 ROLLBACK

17、语句来结束事务。COMMIT：如果事务成功，则提交。ROLLBACK：如果事务中出现错误，或用户决定取消事务，则回滚该事务。返回目录10.2 10.2 事务事务 10.2.3 事务的模式事务的模式（2）指定当 Transact-SQL 语句出现运行时错误时，SQL Server 是否自动回滚当前事务。语法格式：语法格式：SET XACT_ABORT ON|OFF n当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。n当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句

18、，而事务将继续进行处理。OFF 是默认设置。返回目录10.2 10.2 事务事务 10.2.3 事务的模式事务的模式 2.2.隐式事务（隐式事务（Implicit TransactionsImplicit Transactions）在SET IMPLICIT_TRANSACTIONS ON命令之后的第一条语句开始，就开始一个新的事务，直到遇到COMMIT 或 ROLLBACK语句结束这个事务，下一个语句又是一个新的事务，同样直到遇到COMMIT或 ROLLBACK语句结束这个事务。这样形成了一个事务链。【例例10.610.6】隐式事务的例子隐式事务的例子 本例有三个事务，第一个和第三个提交，第

19、二个回滚，最后有四条记录被添加到班级表2。返回目录10.2 10.2 事务事务 10.2.3 事务的模式事务的模式 3 3自动提交事务（自动提交事务（Autocommit TransactionsAutocommit Transactions）自动提交事务模式下，每条单独的语句都是一个事务，成功了就提交，这句语句执行错误就回滚这条语句，对其他语句的执行不产生影响。【例例10.710.7】自动提交事务。自动提交事务。当执行第三个插入语句时，由于字段过长而出错，本句回滚。但是不影响其它语句。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 1 1事务设置语句事务设置语句（1）设

20、置自动回滚模式 SET XACT_ABORT ON|OFF 设置当事务中有一条语句产生运行错误时，取消整个事务（ON）还是只回滚错误语句（OFF）。（2）设置隐形事务模式 SET IMPLICIT_TRANSACTIONS ON|OFF 设置启动（ON）/关闭（OFF）隐形事务模式。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 2 2事务控制语句事务控制语句（1）BEGIN TRANSACTION 显式定义一个事务的开始（2）COMMIT TRANSACTION 事务名 或COMMIT WORK 提交事务，对数据的修改将永久保存到数据库中，不能再回滚。（3）SAVE T

21、RANSACTION 保护点名在事务内设置保护点。当发生错误时，可以使事务返回到保护点。（4）ROLLBACK TRANSACTION 事务名|保护点名 或ROLLBACK WORK 回滚事务，清除自事务的起点或从某保护点起所做的所有数据修改。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 2 2事务控制语句事务控制语句【例例10.810.8】在触发器中使用在触发器中使用ROLLBACKROLLBACK语句的例子。语句的例子。【例例10.910.9】在事务中使用保存点的例子。在事务中使用保存点的例子。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 3

22、 3用于事务控制中的全局变量用于事务控制中的全局变量（1）ERROR：如果前一个 Transact-SQL 语句执行没有错误，则返回 0；如果遇到错误，则返回错误号，是一个整数。（2）TRANCOUNT 保存当前连接中未提交的事务的个数。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 4 4捕获事务中的错误的语句捕获事务中的错误的语句 BEGIN TRY SQL语句|语句块 END TRY BEGIN CATCH SQL语句|语句块 END CATCH返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 4 4捕获事务中的错误的语句捕获事务中的错误的语句在

23、事务中用来捕获语句错误需要使用TRYCATCH语句块：如果 TRY 语句块所包含的代码中有错误，则跳出TRY块，执行 CATCH 块的语句；如果TRY 语句块所包含的代码中没有错误，则当 TRY 语句块中语句完成时，跳出TRY_CATCH语句块，执行 END CATCH 语句之后的语句。【例例10.1010.10】捕获事务中错误的例子。捕获事务中错误的例子。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 5 5事务编码指导原则事务编码指导原则 当事务启动后，事务会保留很多资源，以保护事务的ACID属性。如果修改数据，数据库管理系统将用锁保护修改过的行，以防止其他事务访问这

24、些行，直到事务完成为止。在有很多用户的系统中，为了提高并发度，必须尽可能使事务简短以减少并发连接间的资源锁定争夺。返回目录10.2 10.2 事务事务 10.2.3 事务控制事务控制 6 6事务中不能使用的语句事务中不能使用的语句 ALTER DATABASE RECONFIGURE BACKUP RESTORE CREATE DATABASE DROP DATABASE UPDATE STATISTICS另外，不能使用 sp_dboption 来设置数据库选项，也不能使用任何修改显式或隐式事务内的 master 数据库的系统过程。返回目录小结小结 本章介绍了游标和事务的概念、功能以及使用方法。l游标提供了一种对从表中检索出的数据进行操作的灵活手段。游标是一种可以从包含多条记录的结果集中逐条访问数据行的一种机制，利用游标，可以对不同的行进行不同操作。l事务是由若干个T_SQL语句组成的一个工作单元，事务中对数据的修改或者全部完成，或者全部取消，从而保证数据的完整性和对数据库的并发访问。返回目录