1、An Introduction to Database Systems,数据库系统及应用 基于SQL Server 2000,屠菁 ,An Introduction to Database Systems,第十章 事务和游标,10.1 事务 10.2 游标,An Introduction to Database Systems,10.1 事务,一.事务模式日常生活中我们需要一种能保证数据完整性的机制，比如学生父母给孩子打的生活费要么发送成功，要么发送失败（不应该出现一边发送成功，另一边接受不到的情况），这样才是安全的、可靠的。数据库管理中也需要类似的机制，那就是事务。事务是作为单个逻辑工作单元
2、执行的一系列操作。这一系列操作或者都被执行或者都不被执行。事务做为一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性，只有这样才能成为一个事务。,An Introduction to Database Systems,l 原子性(Atomicity)：事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。l 一致性(Consistency)：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。l 隔离性(Isolation)：由并发事务所作的修改必须与任何其它并发事务所作的修改隔
3、离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。l 持久性(Durability)：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。,An Introduction to Database Systems,二.事务分类事务的执行需要经过事务的开始、事务提交及事务回滚等三个过程。 事务的执行方式有两种，分别是显式事务和隐式事务。显式事务:用户定义或用户指定的事务,其中每个事务均
4、以 BEGIN TRANSACTION 语句显式开始，以 COMMIT 或 ROLLBACK 语句显式结束。隐式事务:在前一个事务完成时将自动启动新事务,但事务依然需要使用COMMIT 或 ROLLBACK 语句显式结束。,An Introduction to Database Systems,三.显式事务 需要显式地定义事务的启动和结束,通过BEGIN TRANSACTION、COMMIT TRANSACTION 、 COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK 等T-SQL语句完成。 1定义事务的开始（启动事务） 语法： BEGIN TR
5、ANSACTION事务名 功能：标记一个显式本地事务的起始点。 2提交事务 语法： COMMIT TRANSACTION事务名 功能：标志一个成功的隐性事务或用户定义事务的结果。 也可以用COMMIT WORK来结束事务，该语句没有参数。,An Introduction to Database Systems,3回滚事务 语法：ROLLBACK TRANSACTION 事务名事务保存点 功能：将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。也可以使用ROLLBACK WORK语句。 【例】定义一事务charu（未提交）并将“学生基本信息表”中族别不是“汉族”的学生“族别”改成“少数民
6、族”。 USE XSCJ GO BEGIN TRANSACTION charu GO UPDATE 学生基本信息表 SET 族别 = 少数民族 WHERE 族别!= 汉族 GO,An Introduction to Database Systems,图9-5 使用BEGIN TRANSACTION开始事务,An Introduction to Database Systems,【例】启动一个事务向student表中插入一个记录，然后回滚该事务。 USE SCHOOLGOBEGIN TRANSACTION -启动事务INSERT INTO student VALUES(100,陈皓,男,1976
7、/03/05,95033) -插入一个学生记录ROLLBACK -回滚事务GOSELECT * FROM student -查询student表的记录GO,An Introduction to Database Systems,4、设置事务保存点语法：SAVE TRANSACTION 事务保存点作用：事务可以返回的位置，有条件地取消事务的一部分 【例】USE SCHOOLGOBEGIN TRANSACTION -启动事务INSERT INTO student VALUES(100,陈皓,男,1976/03/05,95033)-插入一个学生记录SAVE TRANSACTION Mytran -保
8、存点INSERT INTO student VALUES(200,王勇,男,1976/10/05,95031)-插入一个学生记录ROLLBACK TRANSACTION Mytran -回滚事务COMMIT TRANSACTIONGOSELECT * FROM student -查询student表的记录GO,An Introduction to Database Systems,5.事务的嵌套 在嵌套事务中，内部事务所做的修改等提交做完最外层事务之后才能生效。 USE XSCJ GO CREATE TABLE 综合表(学号 INT PRIMARY KEY, 姓名 CHAR(8),年龄 int
9、) BEGIN TRANSACTION charu INSERT INTO 综合表 VALUES (1, 卡吾萨尔,18)BEGIN TRANSACTION charu1INSERT INTO 综合表 VALUES (2, 夏尔巴提,20)BEGIN TRANSACTION charu2 INSERT INTO 综合表 VALUES (3, 张莉,19)COMMIT TRANSACTION charu2COMMIT TRANSACTION charu1 COMMIT TRANSACTION charu,An Introduction to Database Systems,以上代码中一直执行到
10、最后一行后该事务才生效，用户在操作时，可以先试用COMMIT TRANSACTION charu 之前的代码，后用SELECT或直接打开表的方式查看其结果，即会发现用户表列表中除了刚创建的表名之外，该表内无任何数据。这说明定义的事务未提交成功，在执行最关键的外层事务的提交之后才能完成本次事务的提交，同时也可以通过SELECT或直接打开表的方式观察到其结果已生效。,An Introduction to Database Systems,6、不能用于事务的操作有些不能撤消的操作（如创建、删除和修改数据库的操作），即使取消了事务执行或对事务进行回滚，对数据库造成的影响也是不能恢复的，因此这些操作不能
11、用于事务处理。这些操作有：CREATE DATABASE、ALTER DATABASE、DROP DATABASE、RESTORE DATABASE、LOAD DATABASE、BACKUP LOG、RESTORE LOG、UPDATE STATISTICS、GRANT、DUMP TRANSACTION、DISK INIT、RECONFIGURE,An Introduction to Database Systems,四、隐式事务在隐式事务模式下，SQL SERVER执行某些T-SQL语句不需要使用BEGIN TRANSACTION 语句，包括：ALTER TABLE、INSERT、OPEN、
12、CREATE、DELETE、REVOKE、DROP、SELECT、FETCH、TRUNCATE TABLE、UPDATE等。可使用SET语句来打开或关闭隐式事务模式语法为：SET IMPLICIT_TRANSACTIONS ON|OFF注意：当设置为ON时，用户必须以显式方式结束事务，否则当用户断开连接时，事务及其所包含的所有数据更改将回滚。当设置为OFF时，系统转为自动提交模式，提交自行成功的操作。,An Introduction to Database Systems,例演示在IMPLICIT_TRANSACTION设置为ON时显式或隐式启动事务。其中TRANCOUNT为全局变量。 USE
13、 school GO SET NOCOUNT ON -不显示受影响的行数 CREATE TABLE table1(a int) GO INSERT INTO table1 values(1) GO PRINT 使用显式事务 BEGIN TRAN,An Introduction to Database Systems,INSERT INTO table1 values(2) PRINT 事务内的事务数目：+CAST(TRANCOUNT AS CHAR(5) COMMIT TRAN PRINT 事务外的事务数目:+CAST(TRANCOUNT AS CHAR(5) GO PRINT 设置IMPLI
14、CIT_TRANSACTIONS 为ON GO SET IMPLICIT_TRANSACTIONS ON GO PRINT 使用隐式事务 GO -这里不需要BEGIN TRAN 来定义事务的启动 INSERT INTO table1 VALUES(4) PRINT 事务内的事务数目：+CAST(TRANCOUNT AS CHAR(5) COMMIT TRAN PRINT 事务外的事务数目：+CAST(TRANCOUNT AS CHAR(5) GO,An Introduction to Database Systems,执行结果为:使用显式事务 事务内的事务数目:1 事务外的事务数目:0 设置I
15、MPLICIT_TRANSACTIONS 为ON 使用隐式事务 事务内的事务数目:1 事务外的事务数目:0,An Introduction to Database Systems,10.2 游标 一.游标及其作用数据库的游标是类似于C语言指针的语言结构。通常情况下，数据库执行的大多数SQL命令都是同时处理集合内部的所有数据。但是，有时用户也需要对这些数据集合中的某一行进行操作。在没有游标的情况下，这种工作不得不放到数据库前端用高级语言来实现，这将导致不必要的数据传输，从而延长执行时间。通过使用游标就可以在服务器端有效地解决这个问题。游标支持以下功能；在结果集中定位特定行。从结果集的当前位置检索
16、行。支持对结果集中当前位置的行进行数据修改。,An Introduction to Database Systems,二.使用游标 游标主要用在存储过程、触发器和Transact_SQL脚本中。用户可以把它理解为一种特殊变量，也必须先声明后使用。游标的使用可以总结为6个步骤：声明游标、打开游标、提取数据、修改数据、关闭游标、释放游标。1. 声明游标 声明游标的语法如下： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n ,其中：
17、,An Introduction to Database Systems,l cursor_name ：为游标名称。游标命名必须遵从Transact-SQL标识符规则。 l INSENSITIVE：定义一个游标时将在tempdb数据库中创建一个临时表，用于存储由该游标提取的数据。任何通过这个游标进行的操作，都在这个临时表里进行。因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。如果省略 INSENSITIVE，那么用户对基表进行的任何操作都将在游标中反映出来。 l SCROLL：指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIV
18、E、ABSOLUTE）均可用。如果在声明时未指定 SCROLL，则声明的游标只具有默认的 NEXT 功能。 l select_statement：是定义游标结果集的标准SELECT 语句。在游标声明的SELECT语句内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。,An Introduction to Database Systems,l READ ONLY：设置游标为只读，使用该关键字时，不能通过该游标更新数据。在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。 l UPDATE OF column
19、_name ,.n：定义游标内可更新的列。如果指定 OF column_name ,.n 参数，则只允许修改所列出的列。如果在 UPDATE 中未指定默认为可以更新所有列。 2. 打开游标 在使用游标之前，必须首先打开游标，方可执行指定操作。语法格式如下： OPEN GLOBAL cursor_name | cursor_variable_name ,An Introduction to Database Systems,其中：,l GLOBAL：指定 cursor_name 为全局游标。 l cursor_name：已声明的游标名称。,打开一个游标以后，可以使用无参函数ERROR来判断打开操
20、作是否成功。如果这个函数的返回植为0，则表示游标打开成功，否则表示游标打开失败。,An Introduction to Database Systems,3. 从游标中提取记录 使用FETCH语句可以从游标中提取特定的一行记录。其语法格式如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar |RELATIVE n|nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name ,.n 其中各参数意义如下：,An Introduction to Databa
21、se Systems,l NEXT：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。 l PRIOR：返回紧临当前行前面的结果行，并且当前行递减为结果行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。 l FIRST：返回游标中的第一行并将其作为当前行。 l LAST：返回游标中的最后一行并将其作为当前行。 l ABSOLUTE n | nvar：如果 n 或 nvar 为正数，返回从游标头开始的第 n 行并将返回的行变成新的当前行。如
22、果 n 或 nvar 为负数，返回游标尾之前的第 n 行并将返回的行变成新的当前行。如果 n 或 nvar 为 0，则没有行返回。n 必须为整型常量且 nvar 必须为 smallint、tinyint 或 int。,An Introduction to Database Systems,l RELATIVE n | nvar：如果 n 或 nvar 为正数，返回当前行之后的第 n 行并将返回的行变成新的当前行。如果 n 或 nvar 为负数，返回当前行之前的第 n 行并将返回的行变成新的当前行。如果 n 或 nvar 为 0，返回当前行。如果对游标的第一次提取操作时将 FETCH RELAT
23、IVE 的 n 或 nvar 指定为负数或 0，则没有行返回。n 必须为整型常量且 nvar 必须为 smallint、tinyint 或 int。 l GLOBAL：指定 cursor_name 指的是全局游标。 l cursor_variable_name：游标变量名，引用要进行提取操作打开的游标。 l INTO variable_name,.n：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。,An Introduc
24、tion to Database Systems,FETCH_STATUS()函数报告上一个FETCH语句的状态,一般有三种取值:0 FETCH语句成功-1 FETCH语句失败或此行不在结果集中-2 被提取的行不存在,An Introduction to Database Systems,全局变量ROWCOUNT来返回上一语句影响的行数. 例如:USE TESTGOPRINT 更新前的数据SELECT * FROM table2UPDATE table2 SET b=10 where a=1IF ROWCOUNT=0PRINT 没有行被更新!ELSEPRINT 行已被更新!GOPRINT 更新
25、后的数据SELECT * FROM table2GO,执行结果为: 更新前的数据a b c - 1 1 1 行已被更新! 更新后的数据a b c - 1 10 1,An Introduction to Database Systems,4利用游标修改数据 如果游标定义为可更新的，则当定位在游标中的某一行时，可以使用UPDATE或DELETE语句中的WHERE CURRENT OF cursor_name子句执行定位更新或删除操作。 5关闭游标 在打开游标后，SQL Server服务器会专门为游标开辟一定的内存空间存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以，
26、在不使用游标时，一定要关闭游标，以通知服务器释放游标占用的资源。 语法如下： CLOSE cursor_name 6释放游标 游标结构本身也会占用一定的计算机资源，所以在使用完游标后，应该将游标释放，以回收资源。 语法如下： DEALLOCATE cursor_name,An Introduction to Database Systems,【例】声明一个游标jbxxb_cursor，用于访问数据库XSCJ中的“学生基本信息表”。 可在查询分析器中运行如下命令： USE XSCJ GO DECLARE jbxxb_cursor CURSOR FOR SELECT * FROM 学生基本信息表
27、上述命令中，声明游标时在SELECT语句中未用WHERE子句，故此游标返回的结果集是由“学生基本信息表”中的所有记录构成的，结果如图所示。,An Introduction to Database Systems,【例】 使用游标查看数据库XSCJ中“学生基本信息表”中满足条件的记录个数。代码如下： USE XSCJ GO DECLARE jbxxb_Cursor CURSOR FOR SELECT 学号, 姓名 FROM xscj.dbo.学生基本信息表 WHERE 族别 LIKE 哈萨克 OPEN jbxxb_Cursor FETCH NEXT FROM jbxxb_Cursor WHILE
28、 FETCH_STATUS = 0 BEGINFETCH NEXT FROM jbxxb_Cursor END CLOSE jbxxb_Cursor DEALLOCATE jbxxb_Cursor,An Introduction to Database Systems,在以上程序中首先定义了名为jbxxb_cursor的游标，通过该游标可以对学生基本信息表中族别为“哈萨克”族的学生学号和姓名进行搜索，用FETCH NEXT来控制其结果的显示，其中FETCH_STATUS返回被FETCH语句执行的最后游标的状态，如果返回值为0，则说明该游标成功，如果返回值为-1，则说明该游标失败，如果返回值为-
29、2，则说明被提取的行不存在。,An Introduction to Database Systems,【例】为“学生基本信息表”中姓名以“张”开头的行声明一个简单的游标jbxxb1_cursor，并使用 FETCH NEXT 逐个提取这些行。FETCH 语句以单行结果集形式返回由 DECLARE CURSOR 指定列的值。,USE XSCJ GO DECLARE jbxxb1_cursor CURSOR FOR SELECT 姓名 FROM 学生基本信息表 WHERE 姓名 LIKE 张% ORDER BY 姓名 OPEN jbxxb1_cursor FETCH NEXT FROM jbxxb
30、1_cursor WHILE FETCH_STATUS = 0 BEGIN FETCH NEXT FROM jbxxb1_cursor END CLOSE jbxxb1_cursor DEALLOCATE jbxxb1_cursor,An Introduction to Database Systems,An Introduction to Database Systems,【例】本例与上例相似，使用 FETCH语句将值存入变量，但 FETCH 语句的值存储于局部变量而不是直接返回给基表。PRINT 语句将变量组合成单一字符串并将其返回到基表。 USE XSCJ GO DECLARE 学号 v
31、archar(40), 姓名 varchar(20),年龄 int DECLARE xb_cursor CURSOR FOR SELECT 学号,姓名,年龄 FROM 学生基本信息表 WHERE 姓名 LIKE 张% ORDER BY 姓名 OPEN xb_cursor FETCH NEXT FROM xb_cursor INTO 学号, 姓名,年龄 WHILE FETCH_STATUS = 0 BEGINPRINT 学生姓名为： + 姓名 + ；学号为 + 学号+ ；年龄为:+cast(年龄 as char(3)FETCH NEXT FROM xb_cursor INTO 学号, 姓名,年龄
32、 END CLOSE xb_cursor DEALLOCATE xb_cursor,An Introduction to Database Systems,运行结果如图9-4所示。,An Introduction to Database Systems,【例】更改职工表中的工资信息,要求原1250以下的增加工资100元,1250以上(包括1250)增加80元。 USE XSCJ GO DECLARE salary int DECLARE gz_cursor CURSOR FOR SELECT 工资 FROM 职工 FOR UPDATE OPEN gz_cursor FETCH NEXT FRO
33、M gz_cursor INTO salary WHILE FETCH_STATUS = 0 BEGINIF salary1250UPDATE 职工 SET 工资=工资+100 WHERE CURRENT OF gz_cursorELSEUPDATE 职工 SET 工资=工资+80 WHERE CURRENT OF gz_cursorFETCH NEXT FROM gz_cursor INTO salary END CLOSE gz_cursor DEALLOCATE gz_cursor,An Introduction to Database Systems,项 目 实 训,1、使用游标的方式搜索“职工表”中在北京的职工信息(包括所在仓库的城市和面积信息)，并按职工号升序排序。 2、使用事务定义与提交命令在数据库中创建一个“综合表”（学号、姓名、性别、民族），并为它插入三行数据，观察提交之前和之后的浏览与回滚情况。 3、使用游标的方式查找职工E3所对应的订购单信息，并将订购日期更改为当前系统时间。,An Introduction to Database Systems,休息一会儿。,追求,
