《数据库管理与应用教材的电子课件和所用数据库 07-Chap07》由会员分享，可在线阅读，更多相关《数据库管理与应用教材的电子课件和所用数据库 07-Chap07（26页珍藏版）》请在金锄头文库上搜索。
1、数据库管理与应用实例教程,彭勇 主编,第7章 游标、事务和锁,本章导航,本章的主要内容及其在SQL Server2005数据库管理系统中的位置如图示。,学习要点,（1）游标的使用 （2）事务的操作方法 （3）数据的锁定,游标,一、游标的概念,1游标的作用 游标包括以下两个部分: （1）游标结果集 由定义该游标的SELECT语句返回的行的集合。 （2）游标位置 指向这个集合中某一行的指针。 2游标的使用 使用游标的一般步骤如下： 声明T-SQL变量包含游标返回的数据。 使用DECLARE CURSOR语句把T-SQL游标与一个SELECT语句相关联。 使用OPEN语句执行SELECT语句并生成游
2、标。 使用FETCH INTO语句提取单个行，并把每列中的数据转移到指定的变量中。 结束游标时使用CLOSE语句。,（1）声明游标 声明游标使用DECLARE CURSOR语句，其语法格式如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n （2）打开游标 打开游标使用OPEN语句，其语法格式如下： OPEN GLOBAL cursor_name | cursor_variable_name （3）从打开的游标中提取行 游标
3、声明而且被打开以后，游标位置位于第一行。可以使用FETCH语句从游标结果集中提取数据。其语法格式如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,.n ,（4）关闭游标 关闭游标使用CLOSE语句，其语法格式如下： CLOSE GLOBAL cursor_name | cursor_variable_name （5）释放游标 DEALLOCATE GLOBA
4、L cursor_name | cursor_variable_name ,二、游标的使用,【案例学习目标】 学会使用游标提取books数据库中图书信息表（Book）的数据。 【案例知识要点】 游标的创建、打开、提取行、关闭与释放。 【案例完成步骤】 （1）提取图书信息表（Book）中第一行的数据。 /*声明游标*/ DECLARE book_cursor CURSOR FOR SELECT * FROM Book /*打开游标*/ OPEN book_cursor /*提取第一行数据*/ FETCH NEXT FROM book_cursor /*关闭和释放游标*/ CLOSE book_c
5、ursor DEALLOCATE book_cursor 执行结果如图所示。,（2）使用报表形式显示图书信息（Book）表中的图书名称、作者和图书价格。 PRINT *图书信息表* PRINT PRINT - /*声明变量*/ DECLARE bookname varchar(100),authorname varchar(10),bookprice money /*声明游标*/ DECLARE book_cursor CURSOR FOR SELECT vBookName,vAuthor,mPrice FROM Book /*打开游标*/ OPEN book_cursor /*提取第一行数据
6、*/ FETCH NEXT FROM book_cursor INTO bookname,authorname,bookprice WHILE FETCH_STATUS=0 BEGIN /*打印数据*/ PRINT +bookname+ +authorname+ +CAST(bookprice AS CHAR(5)+ PRINT - /*提取下一行数据*/ FETCH NEXT FROM book_cursor INTO bookname,authorname,bookprice END /*关闭和释放游标*/ CLOSE book_cursor DEALLOCATE book_cursor
7、GO 执行结果如图所示。,课堂实践1,【任务1】 使用报表形式显示读者信息（Reader）表中的读者名称（vReaderName）、性别（cGender）和部门编号（cDepartmentID）。,事务,一、事务概述,事务是SQL Server中的单个逻辑单元，一个事务内的所有SQL语句作为一个整体执行，要么全部执行，要么都不执行。 一个逻辑工作单元必须有4个属性，称为ACID属性（原子性、一致性、隔离性和持久性），只有这样才能成为一个事务。,按事务的启动和执行方式，可以将事务分为四类。 （1）显示事务。 （2）隐式事务。 （3）自动处理事务。 （4）批处理级事务。,二、课堂案例2显示事务,【
8、案例学习目标】 掌握显示事务在Book表中的操作方法。 【案例知识要点】 定义事务，提交事务和回滚事务。 【案例完成步骤】 通常在程序中用BEGIN TRANSACTION命令来标识一个事务的启动开始，用COMMIT TRANSACTION命令标识事务结束。 BEGIN TRANSACTION 事务名|事务变量名 COMMIT TRANSACTION 事务名|事务变量名,事务回滚使用ROLLBACK TRANSACTION命令。 ROLLBACK TRANSACTION 事务名|事务变量名 |保存点名|保存点变量名,如果要让事务回滚到指定位置，则需要在事务中设定事务保存点。 SAVE TRAN
9、SACTION保存点名|保存点变量名,(1) 定义一个事务，在Book表中插入一条记录，然后再删除该记录。要求回滚事务时不回滚插入操作。 USE books GO BEGIN TRAN InsTran -启动事务 -插入一条记录 INSERT INTO book(cISBN,vCLCID,cPublisherID,vBookName,mPrice,cEmpID) VALUES(078-7-121-05675-1,TP036,P0004,数据库系统开发,60,E0002) SAVE TRAN InsSave -保存点 DELETE book WHERE cISBN=078-7-121-05675
10、-1 -删除记录 ROLLBACK TRAN InsSave -回滚事务 COMMIT TRAN GO （2）查询book表中的记录。 SELECT * FROM book GO,执行结果如图所示。,三、自动提交事务,USE books GO CREATE TABLE Student(xh char(3) PRIMARY KEY,name char(10) GO INSERT INTO Student VALUES(001,张三) INSERT INTO Student VALUES(002,李四) INSERT INTO Student VALUSE(003,王五) -语法错误 GO SEL
11、ECT * FROM Student -不会返回任何结果 GO 执行结果如图所示。,如果将以上语句修改如下： USE books GO CREATE TABLE Student(xh char(3) PRIMARY KEY,name char(10) GO INSERT INTO Student VALUES(001,张三) INSERT INTO Student VALUES(002,李四) INSERT INTO Student VALUES(001,王五) -运行错误 GO SELECT * FROM Student -不会返回任何结果 GO 运行结果如图所示。运行错误只影响本操作，不会
12、影响整个批处理。,四、隐式事务,隐性事务可以通过使用SET语句来打开或者关闭，或通过数据库API函数和方法进行设置。其语法格式如下： SET IMPLICIT_TRANSACTIONSON|OFF 当设置为ON时，将连接设置为隐性事务模式。当设置为OFF时，则使连接返回到自动提交事务模式。,隐性事务模式将保持有效，直到连接执行SET IMPLICIT_TRANSACTIONS OFF 语句使用连接返回到自动提交模式。在自动提交模式下，如果各个语句成功完成，则提交。,课堂实践2,【任务1】 定义一个事务，要求在删除部门操作时，将对Department表的部门记录删除操作和对Reader表中的读者
13、记录删除操作及对Employee表中的记录删除操作组成一个事务进行处理。,锁,一、并发问题,1丢失更新 2未确认的相关性（脏读） 3不一致的分析（非重复读） 4幻象读,二、事务的隔离级别,SQL-92定义了下列4种隔离级别， SQL Server支持所有这些隔离级别。 未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。 提交读（SQL Server的默认级别）。 可重复读。 可串行读（事务隔离的最高级别，事务之间完全隔离）。 4种隔离级别允许不同类型的行为，如表所示。,三、SQL Server中的锁定,SQL Server可以锁定的资源如表所示（表中按粒度增加的顺序列出）。,SQL
14、 Server使用不同的锁定模式锁定资源，这些锁定模式确定了并发事务访问资源的方式，如表所示。,资源锁模式有一个兼容性矩阵，显示了与在同一资源上可获取的其他锁相兼容的锁，如表所示。,架构锁和大容量更新锁的兼容性如下： 架构稳定性（Sch-S）锁与除了架构修改（Sch-M）锁模式之外的所有锁模式相兼容 架构修改（Sch-M）锁与所有锁模式都不兼容； 大容量更新（BU）锁只与架构稳定性（Sch-S）锁及其他大容量更新（BU）锁相兼容。,四、死锁,封锁机制的引入能解决并发用户访问数据的不一致性问题，但是，却会引起死锁。引起死锁的主要原因是两个进程已经各自锁定一个页，但是又要访问被对方锁定的页。因而会
15、形成等待圈，形成死锁。,为了最大程度地避免死锁，可以采取下面的措施： 按同一顺序访问对象； 避免事务中的用户交互； 保持事务简短并在一个批处理中； 使用低隔离级别； 使用绑定连接。,课堂实践3,【任务1】 将数据表Book的修改和查询操作组合成一个事务，在执行事务过程中检查锁的使用情况。,课外实践,【任务1】 使用游标的CURSOR_ROWS变量，统计books数据库Book表中图书总数。 【任务2】 使用游标从Book表中逐行获取图书价格高于40元的记录。 【任务3】 修改Publisher表时，将对Publisher表的cPublisherID修改操作和Book表的cPublisherID修改操作组合成一个事务进行处理，以保证数据一致性。,
