第8章游标、事务和锁.ppt

2023-11-161第第8章章 游标、事务和锁游标、事务和锁22023-11-16第第8 8章章 游标、事务和锁游标、事务和锁游标游标1事务事务2锁锁3本章小结本章小结 432023-11-168.1 游标 8.1.1 游标的定义及优点游标的定义及优点1游标的定义游标能够部分读取返回结果集合中的数据行，并允许应用程序通过游标来定位修改表中数据。2游标的优点 允许定位在结果集的特定行。从结果集的当前位置检索一行或一部分行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句。42023-11-16 8.1.2 游标的用法游标的用法（1）声明游标（DECLARE CURSOR）（2）打开游标（OPEN CURSOR）（3）提取游标（FETCH CURSOR）（4）根据需要，对游标中当前位置的行执行修改操作（更新或删除）（5）关闭游标（CLOSE CURSOR）（6）释放游标（DEALLOCATE CURSOR）52023-11-161声明游标（声明游标（DECLARE CURSOR）可以使用可以使用DECLARE 语句声明或创建一个游标。语法格式如语句声明或创建一个游标。语法格式如下：下：DECLARE cursor_name CURSOR LOCAL|GLOBAL FORWARD_ONLY|SCROLL STATIC|KEYSET|DYNAMIC|FAST_FORWARD READ_ONLY|SCROLL_LOCKS|OPTIMISTIC FOR select_statement FOR UPDATE OF column_name ,.n ;62023-11-162打开游标（打开游标（OPEN CURSOR）可以使用可以使用OPEN语句打开声明过的游标。语法格式语句打开声明过的游标。语法格式如下：如下：OPEN cursor_name 其中，其中，cursor_name 是已声明过的并且没有打开的是已声明过的并且没有打开的游标名称。游标名称。72023-11-163从打开的游标中提取数据（从打开的游标中提取数据（FETCH CURSOR）可以使用可以使用FETCH语句来提取数据。语法格式如下：语句来提取数据。语法格式如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n FROM cursor_name INTO variable_name ,.n 82023-11-164关闭游标（关闭游标（CLOSE CURSOR）当不再使用游标时，应及时调用当不再使用游标时，应及时调用CLOSE语句关闭游标，以语句关闭游标，以便释放游标所占用的系统资源。在关闭游标时，便释放游标所占用的系统资源。在关闭游标时，SQL Server删除游标中的所有数据，并释放游标对数据库的所删除游标中的所有数据，并释放游标对数据库的所有锁定。所以，在游标关闭后，禁止提取游标数据，或有锁定。所以，在游标关闭后，禁止提取游标数据，或通过游标进行定位修改或删除操作。但是，关闭游标并通过游标进行定位修改或删除操作。但是，关闭游标并不改变游标的定义，应用程序可以再次执行不改变游标的定义，应用程序可以再次执行OPEN语句打语句打开游标。开游标。可以使用可以使用CLOSE关闭游标。语法格式如下：关闭游标。语法格式如下：CLOSE cursor_name 其中，其中，cursor_name 是要被关闭的游标名。是要被关闭的游标名。92023-11-165释放（删除）游标（释放（删除）游标（DEALLOCATE CURSOR）由于关闭游标时并没有删除游标，因此，游标仍然由于关闭游标时并没有删除游标，因此，游标仍然占用着一定的系统资源。如果一个游标确定不再占用着一定的系统资源。如果一个游标确定不再使用，将其关闭后，还需要使用使用，将其关闭后，还需要使用DEALLOCATE语句来删除游标。语法格式如下：语句来删除游标。语法格式如下：DEALLOCATE cursor_name 其中，其中，cursor_name 是已声明的游标名称。是已声明的游标名称。102023-11-16 8.1.3 使用游标修改数据使用游标修改数据 在SQL Server 中，UPDATE语句和DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。这样，就可以通过游标更新和删除数据表中的数据。用于游标操作时，UPDATE语句的语法格式如下：UPDATE table_nameSET column_name=expressionWHERE CURRENT OF cursor_name112023-11-168.2 事务 8.2.1 什么是事务什么是事务事务（transaction）是SQL Server 中的单个逻辑工作单元，也是一个操作序列，它包含了一组数据库操作命令。一个事务内的所有语句被作为一个整体执行。在事务执行过程中，如果遇到错误，则可以回滚事务，取消该事务所做的全部改变，从而保证数据库的一致性和完整性。因此，事务是一个不可分割的工作逻辑单元，一个事务中的语句要么全部正确执行，要么全部不起作用。事务作为一个逻辑工作单元必须具有四个属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个属性简称ACID属性。122023-11-161显示事务显示事务显示事务就是可以显式地定义事务的开始和结束的事务，这类事务又称显示事务就是可以显式地定义事务的开始和结束的事务，这类事务又称为用户定义事务。为用户定义事务。（1）BEGIN TRAN SACTION transaction_name|tran_name_variable 标记一个显式本地事务的起始点。标记一个显式本地事务的起始点。（2）COMMIT TRANSACTION transaction_name|tran_name_variable 或或 COMMIT WORK标志一个成功的显示事务或隐性事务的结束。如果没有遇到错误，可使标志一个成功的显示事务或隐性事务的结束。如果没有遇到错误，可使用该语句成功地结束事务。该事务中的所有数据修改在数据库中都用该语句成功地结束事务。该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。将永久有效。事务占用的资源将被释放。132023-11-16（3）ROLLBACK TRAN SACTION transaction_name|tran_name_variable|savepoint_name|savepoint_variable 或或 ROLLBACK WORK将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点。用来清除遇到将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点。用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。的资源将被释放。（4）SAVE TRANSACTION在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的成其他剩余的 Transact-SQL 语句语句 和和 COMMIT TRANSACTION 语句，或语句，或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，请使用者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，请使用 ROLLBACK TRANSACTION transaction_name 语句。这将撤消事务的所语句。这将撤消事务的所有语句和过程。有语句和过程。在事务中允许有重复的保存点名称，但指定保存点名称的在事务中允许有重复的保存点名称，但指定保存点名称的 ROLLBACK TRANSACTION 语句只将事务回滚到使用该名称的最近的语句只将事务回滚到使用该名称的最近的 SAVE TRANSACTION。7.1.2 事务类型与事务的状态2事务的状态图7.1说明了一个事务对数据库进行操作时，其生存周期内可能进入的状态。图7.1 事务的状态描述7.1.2 事务类型与事务的状态活动状态：表示事务正在执行中。活动状态：表示事务正在执行中。提交未完成状态：表示事务虽然已完成，但事务对数据的更新可能还在缓冲提交未完成状态：表示事务虽然已完成，但事务对数据的更新可能还在缓冲区，未写到数据库中。区，未写到数据库中。失败状态：在两种状态下，事务可能进入失败状态。失败状态：在两种状态下，事务可能进入失败状态。一个处于活动状态的事务在执行过程中发生故障，将进入失败状态。一个处于活动状态的事务在执行过程中发生故障，将进入失败状态。一个处于提交未完成状态的事务执行时发生故障，将进入失败状态。一个处于提交未完成状态的事务执行时发生故障，将进入失败状态。对于处于失败状态的事务必须进行回滚，才能使数据库处于一致状态。对于处于失败状态的事务必须进行回滚，才能使数据库处于一致状态。提交已完成状态：处于提交已完成状态的事务表示事务已执行完毕，数据已提交已完成状态：处于提交已完成状态的事务表示事务已执行完毕，数据已写入数据库，并处于一致状态。写入数据库，并处于一致状态。终止状态：表示事务执行回滚操作，数据库恢复到事务执行前的一致状态。终止状态：表示事务执行回滚操作，数据库恢复到事务执行前的一致状态。事务内部的故障例如，银行转账事务，这个事务把一笔金额从一个账户甲转给另一个账户乙。例如，银行转账事务，这个事务把一笔金额从一个账户甲转给另一个账户乙。BEGIN TRANSACTION 读账户甲的余额读账户甲的余额BALANCE；BALANCE=BALANCE-AMOUNT；(AMOUNT 为转账金额为转账金额)写回写回BALANCE；IF(BALANCE 0)THEN 打印打印金额不足，不能转账金额不足，不能转账；ROLLBACK；(撤销刚才的修改，恢复事务撤销刚才的修改，恢复事务)ELSE 读账户乙的余额读账户乙的余额BALANCE1；BALANCE1=BALANCE1+AMOUNT；写回写回BALANCE1；COMMIT；172023-11-16 2隐式事务隐式事务 隐式事务是指在当前事务提交或回滚后，隐式事务是指在当前事务提交或回滚后，SQL Server 自动开始的事务。所以，隐式事务不需要自动开始的事务。所以，隐式事务不需要使用使用BEGIN TRANSACTION语句标识事务的开语句标识事务的开始，而只需要用户使用始，而只需要用户使用ROLLBACK TRANSACTION、COMMTT TRANSACTION 等语句回滚事务或结束事务。在回滚时候，等语句回滚事务或结束事务。在回滚时候，SQL Server又自动开始一个新的事务。又自动开始一个新的事务。182023-11-16 3自动事务自动事务 自动事务是一种能够自动执行并能自动回滚的事务。在自动事务是一种能够自动执行并能自动回滚的事务。在自动事务模式下，当一个语句成功执行后，它被自动提自动事务模式下，当一个语句成功执行后，它被自动提交，而当它执行过程中产生错误时则自动回滚。自动事交，而当它执行过程中产生错误时则自动回滚。自动事务模式是务模式是SQL Server的默认事务管理模式，当与的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSCTION语句开始一个显示事务，或者执语句开始一个显示事务，或者执行行SET IMPLICIT_TRANSACTIONS