资源简介

(共25张PPT)
第10章 T-SQL高级应用
目录
◎本章目标
■理解：游标、事务的概念及作用
■掌握：并灵活运用游标和事务以提高系统的开发效率
◎本章内容
■10.1 事务
■10.2 游标
10.1 事务
事务（Database Transaction），是并发控制的基本逻辑单元，也是一个操作序列，它包含了一组数据库操作命令，所有的命令作为一个整体一起向系统提交，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
10.1.1 事务的特性
原子性（Atomicity)
一致性（Consistency）
隔离性（Isolation)
持久性（Durability）
10.1.2 事务的分类
1. 自动处理事务
每个单独的T-SQL语句就是一个自动处理事务，它不需要BEGIN TRANSCATION语句来标识事务开始，也不需要COMMIT或ROLLBACK语句来标识事务的结束，由系统自动开始并自动提交。
2. 显式事务
是指使用T-SQL事务语句显式定义的事务，即每个事务必须以BEGIN TRANSACTION 语句标识事务的开始，即启动事务，以COMMIT或ROLLBACK语句标识事务的结束。
10.1.2 事务的分类
3. 隐式事务
隐式事务模式下，当前事务提交或回滚后，SQL Server自动开始下一个事务。
执行SET IMPLICIT_TRANSACTIONS ON语句，可以使SQL Server进入隐式事务模式，需要关闭隐式事务时，执行SET IMPLICIT_TRANSACTIONS OFF则使SQL Server返回到自动处理事务模式。
10.1.2 事务的分类
当SQL Server连接以隐式事务模式进行操作时，数据库引擎首次执行以下语句时，都会自动启动一个隐式事务。在执行COMMIT或ROLLBACK语句之前，该隐式事务将一直保持有效。
① DDL语句：CREATE、DROP、ALTER TABLE。
② DML语句：INSERT、UPDATE、DELETE、SELECT、OPEN、FETCH。
③ DCL语句：GRANT、REVOKE。
10.1.2 事务的分类
4. 批处理级事务
只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server进行回滚。
10.1.3 事务的处理
1. 自动提交事务
【例10-1】针对数据库学生选课管理系统的“学生”表，做插入操作。产生编译错误的过程如下：
USE 学生选课管理
GO
INSERT INTO 学生VALUES('10101004','张三',0, '1992-2-2','电子商务101')
INSERT INTO 学生VALUES('10101005','李四',0, '1992-2-2','电子商务101')
INSERT INTO 学生VALUSE('10101006','王五',0, '1992-2-2','电子商务101')
--语法错误
GO
SELECT * FROM 学生 --不会显示上面插入的三条记录
GO
【例10-2】打开“新建查询”，写入如下代码，产生运行错误的具体过程如下：
USE 学生选课
GO
INSERT INTO 学生VALUES('10101004','张三',0, '1992-2-2','电子商务101')
INSERT INTO 学生VALUES('10101005','李四',0, '1992-2-2','电子商务101')
INSERT INTO 学生VALUES('10101001','王五',0, '1992-2-2','电子商务101')
--健值重复错误
GO
SELECT * FROM 学生--返回带有前两个记录的结果
GO
2. 显式事务
显示事务定义和提交的基本语句格式如下：
BEGIN TRANSACTION [事务名 | @事务变量名]
…
COMMIT TRANSACTION [事务名 | @事务变量名]
其中BEGIN TRANSACTION可以缩写为BEGIN TRAN，
COMMIT TRANSACTION可以缩写为COMMIT TRAN或
COMMIT。
【例10-3】在数据库“学生选课”中，删除学号为“10101001”的所有记录信息。
因为在学生表中保存了该学生的个人信息记录，选课表中保存了该生所修课程以及成绩等信息，出于数据库整体一致特性考虑，要求对学生表和选课表中所有涉及学号为“10101001”的学生相关信息，要么都删除，要么都不删除。
（1） 为了验证事务处理的实际效果，先将表中的原始结果提取出来，以下代码查看学生表和选课表中的原有记录情况，因此点击“新建查询”，写入以下代码。
2. 显式事务
SELECT 学号,姓名,性别,出生年月,班级
FROM 学生
SELECT 学号,课程号,成绩
FROM 选课
点击“执行”，查看学生表和选课表中的记录。
（1）定义和提交事务
（2）接下来，点击新建查询，写入如下代码，定义并提交一个事务
BEGIN TRANSACTION
DELETE 学生
WHERE 学号='10101001'
DELETE 选课
WHERE 学号='10101001'
COMMIT TRANSACTION
（1）定义和提交事务
在事务代码成功执行后，再查询这两个表的数据发现学号为“10101001”的学生记录和选课记录都已经被删除，运行结果如图所示。
（2）回滚事务
事务回滚使用ROLLBACK TRANSACTION命令，其基本语句格式如下所示：
ROLLBACK TRANSACTION [事务名|@事务变量名|保存点名|@保存点变量名]
SAVE TRANSACTION [保存点名 | @保存点变量名]
如果要让事务回滚到指定位置，则需要在事务中设定事务保存点。所谓保存点，是指在事务中使用T-SQL语句在某一个位置定义一个点，点之前的事务语句不能回滚，即此点之前的语句执行被视为有效。定义保存点的基本语句格式如下所示。
【例10-4】在数据库“学生选课”中，查询“选课”表中学号为“10101001”的所有选课信息，并将此学生所有的课程成绩更改为55分。
USE 学生选课
GO
BEGIN TRANSACTION
SELECT 学号,课程号,成绩
FROM 选课
WHERE 学号='10101001'
SAVE TRANSACTION after_query
UPDATE 选课
SET 成绩=55
WHERE 学号='10101001'
IF @@ERROR!=0 OR @@ROWCOUNT=0
BEGIN
ROLLBACK TRANSACTION after_query --回滚到保存点
COMMIT TRANSACTION
PRINT '更新选课学生成绩产生错误'
RETURN
END
SELECT 学号,课程号,成绩
FROM 选课
WHERE 学号='10101001'
COMMIT TRANSACTION
如果代码调整为：
USE 学生选课管理系统
GO
BEGIN TRANSACTION
SELECT 学号,课程号,成绩
FROM 选课
WHERE 学号='10101001'
SAVE TRANSACTION after_query
UPDATE 选课
SET 成绩=105
WHERE 学号='10101001'
IF @@ERROR!=0 OR @@ROWCOUNT=0
BEGIN
ROLLBACK TRANSACTION after_query --回滚到保存点
COMMIT TRANSACTION
PRINT '更新选课表学生成绩产生错误'
RETURN
END
SELECT 学号,课程号,成绩
FROM 选课
WHERE 学号='10101001'
COMMIT TRANSACTION
3. 隐式事务
隐式事务模式，在这种模式中，SQL Server在没有事务存在的情况下会开始一个事务，但不会像在自动模式中那样自动执行COMMIT 或ROLLBACK 语句。隐式事务无须像显式事务那样必须以BEGIN TRANSACTION语句标示事务的开始，但是隐式事务必须显式结束(即COMMIT 或者ROLLBACK)。
【例10-5】在数据库“学生选课”中，启动并执行两个隐式事务，两个隐式事务完成向学生表中插入四条记录。
USE 学生选课管理系统
GO
SET IMPLICIT_TRANSACTIONS ON --设置连接为隐式事务模式
GO
INSERT INTO 学生 VALUES('10103001','王传',0,’1991-3-2’,'电子商务')
GO
INSERT INTO 学生 VALUES('10103002','欧阳雨',0,’1992-2-2’,'电子商务')
GO
COMMIT TRANSACTION --提交第一个隐式事务
GO
-- 启动第二个隐式事务
INSERT INTO 学生 VALUES('10103003', '齐飞',1, '1990-6-1', '电子商务')
GO
INSERT INTO 学生 VALUES('10103004', '蓝波',0, '1992-7-4', '电子商务')
GO
ROLLBACK TRANSACTION --回滚第二个隐式事务
GO
SET IMPLICIT_TRANSACTIONS OFF --关闭隐式事务模式
GO
--返回自动事务模式，查询语句构成一个自动事务
SELECT * FROM 学生
GO
10.2 游标
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
在SQL Server2008中使用游标的一般步骤如下：
（1）声明游标：使用DECLARE CURSOR语句声明游标。语法格式为：
DECLARE 游标名 CURSOR FOR SELECT 语句
（2）打开游标：使用OPEN CURSOR语句打开游标。
（3）提取游标：使用FETCH CURSOR语句，从结果集中检索特定的一行。
在打开一个游标后，它首先被放到结果集首行前，可以用FETCH NEXT语句访问其第一行。
FETCH NEXT FROM 游标名 INTO @变量名
（4）关闭游标：使用CLOSE CURSOR语句关闭游标。
（5）删除游标：使用DEALLOCATE CURSOR语句删除游标使用。
【例10-8】使用游标实现报表形式显示数据库“学生选课”中所有性别为0的“学生”的学号、姓名信息。
DECLARE @no char(8) ,@name char(8)
DECLARE cur CURSOR FOR
SELECT 学号,姓名 FROM 学生 WHERE 性别=0 --声明游标
OPEN cur --打开游标
FETCH NEXT FROM cur INTO @no,@name --第一次提取
PRINT SPACE(4)+'-------学生表--------'
WHILE （@@FETCH_STATUS=0） --检查游标中是否有尚未提取的数据
BEGIN
PRINT '学号：'+@no+' 姓名：'+@name
FETCH NEXT FROM cur INTO @no,@name
END
CLOSE cur --关闭游标
DEALLOCATE cur --删除游标
GO
【例10-9】通过游标将数据库“学生选课”中“课程”表的课程名和学分的对应关系以报表形式现实出来。
USE 学生选课
DECLARE @KechengName char(8),@Member char(8)
DECLARE MyCursor CURSOR
FOR SELECT 课程名,学分 FROM 课程 ORDER BY 学分--新建游标
OPEN MyCursor --打开游标
FETCH NEXT FROM MyCursor INTO @KechengName,@Member --操作游标
PRINT SPACE(4)+'-------课程对应学分表--------'
WHILE(@@Fetch_Status = 0)
BEGIN
PRINT '课程名：'+@KechengName + ' 学分:' + @Member
FETCH NEXT FROM MyCursor INTO @KechengName,@Member
END
CLOSE MyCursor --关闭游标
DEALLOCATE MyCursor --释放游标
SELECT * FROM 选课
--显示更新后每个学生每门课成绩
USE 学生选课
SELECT * FROM 选课
--显示更新前学生每门课程成绩
DECLARE @member char(8),@ID int,@score int
DECLARE score_cursor CURSOR
FOR SELECT 学号,课程号,成绩 FROM 选课
FOR UPDATE OF 成绩 --声明游标
OPEN score_cursor --打开游标
--从游标中读取数据
FETCH NEXT FROM score_cursor INTO @member,@ID,@score
WHILE(@@FETCH_STATUS=0)
BEGIN
IF(@score>=60)
UPDATE 选课 SET 成绩=成绩-10 WHERE CURRENT OF score_cursor
FETCH NEXT FROM score_cursor INTO @member,@ID,@score
END
CLOSE score_cursor --关闭游标
DEALLOCATE score_cursor --删除游标
【例10-9】通过游标将数据库“学生选课”中“选课”表中及格学生的成绩都减10分。
本章小结
事务是包括一系列操作的逻辑工作单元，事务具有ACID特性。一个事务中的语句要么全部执行，要么全部不执行。使用事务可以保证数据库中数据的一致性。
事务分为自动提交事务、显式事务、隐式事务，自动提交事务是SQL Server默认的工作模式，显示事务需要用户定义事务的开始和结束，隐式事务由SQL Server自动开始，但需要由用户定义事务的结束。
游标是一种对SELECT结果集进行逐行操作的数据处理技术，一个游标包括查询结果集和游标位置两个组成部分。
声明游标DECLARE CURSER、使用OPEN打开游标、FETCH获取游标、CLOSE关闭游标以及使用DEALLOCATE释放游标。
本章结束,谢谢！

展开更多......

收起↑