《数据库原理与应用》11.事务和游标的创建和使用.ppt

《《数据库原理与应用》11.事务和游标的创建和使用.ppt》由会员分享，可在线阅读，更多相关《《数据库原理与应用》11.事务和游标的创建和使用.ppt（59页珍藏版）》请在三一办公上搜索。

1、孙 发 勤 扬州大学新闻与传媒学院Y,数据库原理与应用第十一讲 事务和游标的创建和使用,创建事务提交事务回滚事务回滚事务的一部分用锁理解死锁声明游标打开游标从游标取数据关闭游标,本讲主要内容,例：一个员工代码为000002的内部候选人已经被选定为Sales Manager（岗位代码为0001）岗位。这必须在Employee表中被更新，当前已在该岗位的人数也需要在Position表中更新。上面要做的UPDATE语句如下：UPDATE EmployeeSET cCurrentPosition=0001WHERE cEmployeeCode=000002UPDATE PositionSET iCur

2、rentStrength=iCurrentStrength+1WHERE cPositionCode=0001,系统崩溃是由于两个更新之间导致数据不一致性而引起的。需要防止这种情况，要确保两个更新或者都发生或者都不发生。,问题：怎样防止数据的不一致性？执行事务 验证数据在两个表中都已更新,怎样防止数据的不一致性？事务一个事务可以被定义为作为工作的单个的逻辑单元被一起执行的一串的操作。单个的工作单元必须具有称为ACID（原子性，一致性，独立性，和持久性）的四个性质原子性一致性独立性持久性,怎样防止数据的不一致性(续)为实现ACID性质的需求，SQL Server提供了下面的特性:事务管理上锁日志

3、事务日志-是SQL Server管理所有它的的事务而维护的日志显式事务-是事务的开始和结束都被显式地定义的事务。,怎样防止数据的不一致性(续)BEGIN TRANSACTION:该语句标志显示事务的开始语法BEGIN TRANSACTION transaction_name|tran_name_variableCOMMIT TRANSACTION或 COMMIT WORK:语句标志显式事务的结束点语法 COMMIT TRANSACTIONtransaction_name|tran_name_variable,怎样防止数据的不一致性(续)Autocommit事务autocommit模式是SQL

4、Server的缺省事务管理模式，当使用SET AUTOCOMMIT OFF开启事务支持时，所有insert，delete或update操作列表存储在内存中，因此，当进行ROLLBACK事务时，这些操作能够被撤销。跨越几百个数据改变的事务将会花费很多的内存知道下一个COMMIT或ROLLBACK清除了操作列表。结果：事务的使用可以避免数据的不一致性UPDATE语句可通过使用BEGIN TRANSACTION和COMMIT TRANSACTION语句来维持其原子性,执行事务动作：在 Query Analyzer窗口中，键入：BEGIN TRANSACTION trnUpdatePositionUP

5、DATE Employee SET cCurrentPosition=0001 WHERE cEmployeeCode=000002 UPDATE Position SET iCurrentStrength=iCurrentStrength+1 WHERE cPositionCode=0001 COMMIT TRANSACTION trnUpdatePosition,验证两个表中的数据都已被更新动作：下面的SELECT语句来验证那些行已被更新：SELECT*FROM PositionWHERE cPositionCode=0001SELECT*FROM Employee WHERE cEmpl

6、oyeeCode=000002,识别下面单个工作单元的性质：由并发事务引起的任何数据修改必须与其他并发事务所作的修改隔离开来所有的数据修改都被执行或者都没有被执行已完成事务的任何数据改变在系统中永久起作用事务成功地完成后所有的数据必须处于一致的状态,2.回复改变职位0015已经招募了10个候选人。为了反映这一变化，对于RequisitionCode 0015，Requisition表的NuOfRequir属性将会减少10。还有，对于cPositionCode 0015，Position表的iCurrentStrength属性要加10，使用下面命令：UPDATE Requisitionset N

7、uOfRequir=NuOfRequir-10WHERE RequisitionCode=0015,2.回复改变(续)UPDATE Positionset iCurrentStrength=iCurrentStrength+10WHERE cPositionCode=0015这两个语句都应该是原子的，如果iCurrentStrength属性变得不只是iCurrentStrength属性，那么，由UPDATE语句所作的改变必须回复。,识别怎样恢复所作的改变执行事务验证事务是否被执行,如何回复所作的改变?ROLLBACK TRANSACTION或 ROLLBACK WORK:这些语句把显式的或隐式

8、的事务回滚到事务的开始，或者回滚到事务内的保存点语法ROLLBACK TRANSACTION transaction_name|tran_name_variable|savepoint_name|savepoint_variable结果事务可用ROLLBACK TRANSACTION语句回复,BEGIN TRANSACTIONUPDATE RequisitionSET NuOfRequir=NuOfRequir-10WHERE cRequisitionCode=0015UPDATE PositionSET iCurrentStrength=iCurrentStrength+10WHERE cP

9、ositionCode=0015IF(SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode=0015)0 BEGINPRINT Current strength cannot be more than Max strength.Transaction has not been committed.ROLLBACK TRANSACTION ENDELSE BEGINPRINT The transaction has been committed.COMMIT TRANSACTION END,验证事务是否被执行

10、动作：查看结果窗口中所显示的输出。,3.回复事务的一部分Employee和Position表需要用下面的事务来更新：事务1:UPDATE EmployeeSET cCurrentPosition=0015WHERE cEmployeeCode=000002UPDATE PositionSET iCurrentStrength=iCurrentStrength+1WHERE cPositionCode=0015Requisition 和Position表需要使用下面的事务来更新：事务 2:UPDATE Requisition SET NuOfRequir=NuOfRequir-10 WHERE

11、cRequisitionCode=0015UPDATE Position SET iCurrentStrength=iCurrentStrength+10 WHERE cPositionCode=0015,3回复事务的一部分(续)所有更新应一起来做。对于cPositionCode 0015，如果iCurrentStrength值大于iMaxStrength值，由第二个事务所产生的改变必须被回复，而由第一个事务产生的改变是允许的。,识别怎样把事务分解成部分执行事务验证事务的执行,怎样把事务分解成部分?保存事务它在事务内设置保存点。保存点把事务分成几个逻辑单元，这样事务可以返回到保存点，如果事务的

12、一部分是有条件地被取消。语法SAVE TRANSACTION savepoint_name|savepoint_variable结果事务可以用SAVE TRANSACTION语句分解成几个逻辑单元,保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint_name 语句创建一个保存点，然后再执行 ROLLBACK TRANSACTION savepoint_name 语句回滚到该保存点，从而无须回滚到事务的开始。在不可能发生错误的情况下，保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试更新的有效性更为有效。更新

13、和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。,BEGIN TRANSACTION UPDATE Employee SET cCurrentPosition=0015 WHERE cEmployeeCode=000002UPDATE Position SET iCurrentStrength=iCurrentStrength+1 WHERE cPositionCode=0015 SAVE TRANSACTION trnTransaction1 UPDATE Requisition SET NuOfRequir=NuO

14、fRequir-10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength+10 WHERE cPositionCode=0015,IF(SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode=0015)0 BEGINPRINT Transaction 1 has been committed but transaction 2 has not been ommitted.ROLLBACK TRANS

15、ACTION trnTransaction1 ENDELSE BEGINRINT Both the transactions have been committed.COMMIT TRANSACTION END,验证事务的执行动作:查看在结果窗口中显示的输出。,4.体验上琐的用法User1给出下面的语句来更新ExternalCandidate表中的材料，表中有cCandidateCode000002的候选人参加测试后的测试分数和测试日期。BEGIN TRANSACTIONUPDATE ExternalCandidate SET siTestScore=90 WHERE cCandidateCo

16、de=000002UPDATE ExternalCandidate SET dTestDate=getdate()WHERE cCandidateCode=000002COMMIT TRANSACTION,4体验上琐的用法(续)当上面的事务被执行时，User2要安排一个候选人来面试，但是不能查看高于80分候选人的材料。他/她用下面的语句来查看材料和安排面试：BEGIN TRANSACTION SELECT*from ExternalCandidate WHERE siTestScore 80 UPDATE ExternalCandidate SET dInterviewDate=getdate

17、()+2 WHERE siTestScore 80 COMMIT TRANSACTION,User2为什么不能执行此事务?,为什么User2不能执行此事务?上锁确保事务的完整性和数据库的一致性是自动实施的不上锁，查看事务处理是不可能的。,为什么User2不能执行此事务(续)事务的并发性SQL Server提供了乐观的和悲观的并发性控件乐观并发性控件建立在多用户间资源冲突大概是不可能的假设的基础上允许事务执行不用锁定任何资源只有在提交事务时才进行资源检查,为什么User2不能执行此事务(续)悲观并发性控件在事务处理期间的锁定资源并发性问题丢失更新丢失更新问题发生在当两个或多个事务基于原先所选值试

18、图修改同一行的时候自由依赖性自由依赖性问题（uncommitted dependency）也称为无效读入（dirty read）问题,为什么User2不能执行此事务(续)不一致性分析不一致性分析问题又称为 不可重复问题幻象读取幻象读取又称为作幻象问题,为什么User2不能执行此事务(续)SQL Server锁模型共享锁允许并发事务来读取资源更新锁避免了常见形式的死琐发生互斥型锁唯一地限制并发事务访问一个资源,为什么User2不能执行此事务(续)意向锁指示SQL Server要在层次结构较低的某个资源上获得一个共享或排它锁模式锁当任何数据定义（DDL）操作在表上执行时，SQL Server考察模

19、式修改(Sch-M)锁,为什么User2不能执行此事务(续)结果：User2不能执行他的事务，因为当它正在被User1使用时SQL Server已经锁定了ExternalCandidate表,死锁死锁是这样一种情形：两个用户（或事务）在个别的对象的上锁，并且每个用户正在等待另一个对象的锁,死锁(续)设置死锁优选级为探测死锁的情况，SQL Server扫描在等待锁请求的会话SQL Server提供SET DEADLOCK_PRIORITY命令来定制死锁语法SET DEADLOCK_PRIORITY LOW|NORMAL|deadlock_var控制在发生死锁情况时会话的反应方式。如果两个进程都锁

20、定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。LOW指定当前会话为首选死锁牺牲品。Microsoft SQL Server 自动回滚死锁牺牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。NORMAL指定会话返回到默认的死锁处理方法。定制LOCK_TIMEOUT SET LOCK_TIMEOUT命令可被用来设置等待被阻塞资源语句的最长时间,游标的定义及其优点,前面介绍的数据检索方法可以得到数据库中有关表的数据，但这些数据是作为一个结果集得到的，用户可以把这个结果集保存到一个文件里，或生成一个新表以便于以后使用。这种查询是非常重要的。但这种查询形式

21、有一个很大的缺点，它不能对结果集中每一行的数据进行处理。使用游标可以实现对查询结果集中的数据逐行处理。游标的概念 游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。,游标游标是一个在给定结果集中帮助访问和操纵数据的数据库对象游标能以下列方式处理结果集中的行：允许从结果集中检索指定的行允许结果集中当前行被修改帮助从结果集中当前行导航到不同的行允许被其它用户修改的数

22、据在结果集中是可见的,使用游标的步骤,有如下几个步骤：创建游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。打开游标 从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。对游标中的数据逐行操作。关闭和释放游标。,5.把指定属性作为变量显示你需要召集所有部门头目开会。为此你需要部门和相应部门头目的列表如下，其格式如下：Department Name=Production Department Head=Samuel Moore Department Name=Sales Department Head=Donald Fleming.,创建报告所需

23、的步骤执行创建报告所需的语句按所需的结果验证其输出,游标的定义及使用过程,声明游标声明游标是指用DECLARE语句声明或创建一个游标。声明游标的语法如下：DECLARE cursor_name SCROLL CURSORFOR select_statementFOR READ ONLY|UPDATEOF column_name_list其中：cursor_name:是游标的名字，为一个合法的SQL Server标识符，游标的名字必须遵循SQL Server命名规范。SCROLL：表示取游标时可以使用关键字NEXT、PRIOR、FIRST、LAST、ABSOLUTE、RELATIVE。每个关键字

24、的含义将在介绍FETCH子句时讲解。select_statement：是定义游标结果集的标准 SELECT语句，它可以是一个完整语法和语义的Transact-SQL的SELECT语句。,但是这个SELECT语句必须有FROM子句，不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。FOR READ ONLY：指出该游标结果集只能读，不能修改。FOR UPDATE：指出该游标结果集可以被修改。OF column_name_list：列出可以被修改的列的名单。应该注意：游标有且只有两种方式：FOR READ ONLY或FOR UPDATE。当游标方式指定为F

25、OR READ ONLY时，游标涉及的表不能被修改。当游标方式指定为FOR UPDATE时，可以删除或更新游标涉及的表中的行。通常，这也是缺省方式，即不指定游标方式时为FOR UPDATE方式。声明游标的DECLARE CURSOR 语句必须是在该游标的任何OPEN语句之前。,2.打开游标打开游标是指打开已被声明但尚未被打开的游标，打开游标使用OPEN语句。打开游标的语法如下：OPEN cursor_name其中：cursor_name是一个已声明的尚未打开的游标名。注意：当游标打开成功时，游标位置指向结果集的第一行之前。只能打开已经声明但尚未打开的游标。,3.从打开的游标中提取行游标被打开后

26、，游标位置位于结果集的第一行前，此时可以从结果集中提取（FETCH）行。SQL Server将沿着游标结果集一行或多行向下移动游标位置，不断提取结果集中的数据，并修改和保存游标当前的位置，直到结果集中的行全部被提取。从打开的游标中提取行的语法如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list其中：cursor_name：为一已声明并已打开的游标名字。NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是NEXT，即向下

27、移动。NEXT：取下一行数据。PRIOR：取前一行数据。FIRST：取第一行数据。,LAST：取最后一行数据。ABSOLUTE：按绝对位置取数据。RELATIVE：按相对位置取数据。游标位置确定了结果集中哪一行可以被提取，如果游标方式为FOR UPDATE的话，也就确定该位置一行数据可以被更新或删除。INTO fetch_target_list：指定存放被提取的列数据的目的变量清单。这个清单中变量的个数、数据类型、顺序必须与定义该游标的select_statement的SELECT_list中列出的列清单相匹配。为了更灵活地操纵数据，可以把从已声明并已打开的游标结果集中提取的列数据，分别存放在

28、目的变量中。INTO fetch_target_list是T-SQL对ANSI-92 SQL标准的扩充。,有两个全局变量提供关于游标活动的信息：FETCH_STATUS 保存着最后FETCH语句执行后的状态信息，其值和含义如下：0：表示成功完成FETCH 语句。-1：表示FETCH语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中不再有数据。-2：提取的行不存在。rowcount保存着自游标打开后的第一个FETCH语句，直到最近一次的FETCH语句为止，已从游标结果集中提取的行数。也就是说它保存着任何时间点客户机程序看到的已提取的总行数。一旦结果集中所有行都被提取，那么rowco

29、unt的值就是该结果集的总行数。每个打开的游标都与一特定的rowcount有关，关闭游标时，该rowcount变量也被删除。在FETCH语句执行后查看这个变量，可得知从游标结果集中已提取的行数。,4.关闭游标关闭(Close)游标是停止处理定义游标的那个查询。关闭游标并不改变它的定义，可以再次用open语句打开它，SQL Server会用该游标的定义重新创建这个游标的一个结果集。关闭游标的语法如下：CLOSE cursor_name其中：cursor_name：是已被打开并将要被关闭的游标名字。在如下情况下，SQL Server会自动地关闭已打开的游标：当你退出这个SQL Server会话时从

30、声明游标的存储过程中返回时,创建报告所需的步骤(续)5.解除分配游标你可以抹去由DECLARE游标语句定义的游标的定义语法 DEALLOCATE cursor_name,报告所需的步骤(续)-7号你需要用下面的语句来显示报告。.-Create two variables that would store the-values returned by the fetch statement.DECLARE DepartmentName char(25)DECLARE DepartmentHead char(25)-Defines the cursor that can be used to-ac

31、cess the records of the table,row by row.DECLARE curDepartment cursor for SELECT vDepartmentName,vDepartmentHead FROM Department-Open the cursorOPEN curDepartment-Fetch the rows into variablesFETCH curDepartment into DepartmentName,DepartmentHead-Start a loop to display all the rows of-the cursor.Wh

32、ile(fetch_status=0)BEGINPrint Department Name=+DepartmentNamePrint Department Head=+DepartmentHead-Fetch the next row from the cursor.FETCH curDepartment into DepartmentName,DepartmentHeadEND-Close the cursorCLOSE curDepartment-Deallocate the cursor.DEALLOCATE curDepartment,定义一个游标，将学生表student中所有学生的姓

33、名、性别显示出来。DECLARE student_name VARCHAR(8),student_sex VARCHAR(16)DECLARE student_coursor SCROLL CURSOR FOR SELECT name，sex FROM student FOR READ ONLYOPEN student_coursorFETCH student_coursor INTO student_name,student_sexWHILE FETCH_STATUS=0 BEGIN PRINT 学生姓名:+student_name+性别:+student_sex FETCH FROM st

34、udent_coursor INTO student_name,student_sex ENDCLOSE student_coursorDEALLOCATE student_coursor,用户可以在UPDATE或DELETE语句中使用游标来更新、删除表或视图中的行，但不能用来插入新行。更新数据通过在UPDATE语句中使用游标可以更新表或视图中的行。被更新的行依赖于游标位置的当前值。更新数据语法形式如下：UPDATE table_name|view_name SET table_name.|view_name.column_name=new_value.n WHERE CURRENT OF c

35、ursor_name,使用游标修改数据,其中：紧跟UPDATE之后的table_name|view_name：要更新的表名或视图名，可以加或不加限定。但它必须是声明该游标的SELECT语句中的表名或视图名。column_name：是要更新的列的列名，可以加或不加限定。但它们必须是声明游标的SELECT语句中UPDATE OF column_name_list的子集。new_value：为被更新列的新值，它可以是一个表达式、空值或子查询。WHERE CURRENT OF：使SQL Server只更新由指定游标的游标位置当前值确定的行。cursor_name：是已声明为FOR UPDATE方式并已

36、打开的游标名。,注意：使用UPDATE.CURRENT OF语句一次只能更新当前游标位置确定的那一行，OPEN语句将游标位置定位在结果集第一行前，可以使用FETCH语句把游标位置定位在要被更新的数据行处。用UPDATE.WHERE CURRENT OF语句更新表中的行时，不会移动游标位置，被更新的行可以再次被修改，直到下一个FETCH语句的执行。UPDATE.WHERE CURRENT OF语句可以更新多表视图或被连接的多表，但只能更新其中一个表的行，即所有被更新的列都来自同一个表。,DECLARE student_coursor SCROLL CURSOR FOR SELECT name,s

37、ex FROM student FOR UPDATE OF nameOPEN student_coursorFETCH ABSOLUTE 3 FROM student_coursorUPDATE studentSET name=ABC WHERE CURRENT OF student_coursorCLOSE student_coursorDEALLOCATE student_coursor,删除数据通过在DELETE语句中使用游标来删除表或视图中的行。被删除的行依赖于游标位置的当前值。删除数据语法形式如下：DELETE FROMdatabase.owner.table_name|view_n

38、ameWHERE CURRENT OF cursor_name其中：table_name|view_name：为要从其中删除行的表名或视图名，可以加或不加限定。但它必须是定义该游标的SELECT语句中的表名或视图名。cursor_name：为已声明并已打开的游标名。WHERE CURRENT OF：它使SQL Server只删除由指定游标的游标位置当前值确定的行。,注意：使用游标的DELETE语句，一次只能删除当前游标位置确定的那一行。OPEN语句将游标位置定位在结果集第一行之前，可以用FETCH语句把游标位置定位在要被删除的行处。在DELETE语句中使用的游标必须声明为FOR UPDATE方式。而且声明游标的SELECT语句中不能含有连接操作或涉及多表视图，否则即使声明中指明了FOR UPDATE方式，也不能删除其中的行。对使用游标删除行的表，要求有一个唯一索引。使用游标的DELETE语句，删除一行后将游标位置向前移动一行。,