事务、视图和索引 第六章

• 自动提交事务
– SQL Server 的默认模式
使用事务解决银行转账问题4-1
• 从张三的账户转出1000元，存入李四的账 Baidu Nhomakorabea始事务（指定事务从此处开始，后续 户中 的T-SQL语句都是一个整体） BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/ DECLARE @errorSum INT SET @errorSum=0 --初始化为0，即无错误 /*--转账：张三的账户减少1000元，李四的账户增加1000元*/ UPDATE bank SET currentMoney = currentMoney - 1000 WHERE customerName = '张三' SET @errorSum = @errorSum + @@ERROR --累计是否有错误 UPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = '李四' SET @errorSum = @errorSum + @@ERROR 累计是否有错误
• 提示：
/*--查询Result 表中所有Y2学生的考试成绩，保存到新表HistoreResult*/ – 将毕业学生的基本信息和考试成绩分别保存 SELECT Result.* INTO HistoreResult FROM Result INNER JOIN 到历史表中 Student ON Result.StudentNo=Student.StudentNo INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2' SET @errorSum=@errorSum@@ERROR – 使用显式事务 /*--删除Result表中所有Y2学生的考试成绩*/ DELETE–Result FROM Result JOIN Student ON 查询 Result 表中所有 Y2 学生的考试成绩，保 Result.StudentNo=Student.StudentNo 存到表 HistoreResult 中 INNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2' – 删除Result表中所有Y2学生的考试成绩 SET @errorSum=@errorSum+@@ERROR /*--将Student表中所有Y2的学生记录，保存到新表HistoryStudent*/ … … – 查询Student表中所有Y2的学生记录，保存到 /*--删除Studet Y2学生记录*/ 表表中所有 HistoryStudent 中 …… 完成时间：20分钟 /*--根据是否有错误，确定事务是提交还是撤销 --*/ – 删除Studet表中所有Y2学生记录
– 为了判断事务中所有T-SQL语句是否有错，可以 对错误进行累计 SET @errorSum=@errorSum+@@ERROR
如：
• 事务可以嵌套
事务分类
• 显式事务
– 用BEGIN TRANSACTION明确指定事务的开始 – 最常用的事务类型
• 隐性事务
– 通过设置SET IMPLICIT_TRANSACTIONS ON 语句 ，将隐性事务模式设置为打开 – 其后的T-SQL语句自动启动一个新事务 – 提交或回滚一个事务后，下一个 T-SQL 语句又 将启动一个新事务
回顾2-2
• 下列语句实现了什么功能？
查看没有参加考试的学生
SELECT * FROM Student WHERE StudentNo NOT IN ( SELECT StudentNo FROM Result )
• 上述语句可以用下面的联接替换吗？
SELECT * FROM Student INNER JOIN Result ON Student.StudentNo<>Result.StudentNo
– 使用事务向表中插入多条记录
• 需求说明：
– 批量插入参加今天“Java Logic”课程考试的 十名学生成绩 – 如果输入的成绩大于100分，将违反约束
讲解需求说明
指导——批量插入学生 BEGIN TRANSACTION • 实现思路： DECLARE @errorSum INT 考试成绩 2-2 SET @errorSum=0
使用事务解决银行转账问题4-3
• 转账1000元，转账失败的情况
转账事务前
转账事务过程中
转账事务结束后
演示案例1：利用事务实现转账1000元
使用事务解决银行转账问题4-4
• 转账800元，转账成功的情况
转账事务前
转账事务过程中
转账事务结束后
演示案例2：利用事务实现转账800元
指导——批量插入学生 • 训练要点： 考试成绩2-1
使用事务解决银行转账问题4-2
从张三的账户转出1000元，存入李四的账户中
IF @errorSum<>0 --如果SQL语句执行出错 根据执行是否有错误，决定 BEGIN 提交事务，或撤销事务 PRINT '交易失败，回滚事务' ROLLBACK TRANSACTION END 如果有错，则回滚操作，事务结束 ELSE BEGIN PRINT '交易成功，提交事务，写入硬盘，永久的保存' COMMIT TRANSACTION 如果成功，则提交操作，事务结束 END GO PRINT '查看转账事务后的余额' SELECT * FROM bank GO
银行转账 账户A 账户B
为什么需要事务5-2
• 假定张三的账户直接转账1000元到李四的 CREATE TABLE bank 账户 创建账户表，存放用户的账户信息
( customerName CHAR(10), --顾客姓名 currentMoney MONEY --当前余额
) 添加约束：根据银行规定，账户余 GO 额不能少于1元，否则视为销户 ALTER TABLE bank ADD CONSTRAINT CK_currentMoney CHECK(currentMoney >= 1) GO 张三开户，开户金额为1000元 ；李四开户，开户金额1元 INSERT INTO bank(customerName, currentMoney) VALUES('张三', 1000) INSERT INTO bank(customerName, currentMoney) VALUES('李四', 1)
为什么需要事务5-5
• 发生错误的原因
UPDATE语句违反约束： 余额>=1元
--张三的账户减少1000元，李四的账户增加1000元 UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三' 执行失败，张三账户余额还是1000元 UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四' GO 继续往下执行：执行成功，李四账户余额变为1001元 如何解决呢？使用事务
–/*-使用显式事务完成批量插入 10个学生考试成 插入数据--*/ … … 绩的操作 INSERT INTO –Result(StudentNo,SubjectNo,ExamDate,StudentResult) 使用全局变量@@ERROR判断插入操作是否成 VALUES(10012,1,'2009-5-20',102) --分数违反约束 功 SET @errorSum=@errorSum+@@ERROR … –… 使用 IF语句判断@@ERROR值。如果插入成功 IF(@errorSum<>0) --如果有错误 BEGIN ，提交事务；否则回滚事务
本章任务
• • • • • 模拟实现银行转帐功能 批量插入学生考试成绩 办理毕业学生离校手续 查看学生成绩 查看学生档案
本章目标
• 使用事务保证操纵数据的完整性 • 掌握如何创建并使用视图
为什么需要事务5-1
• 银行转账问题： 假定资金从账户A转到账户B，至少需要 两步：
– 账户A的资金减少 – 然后账户B的资金相应增加
PRINT '插入失败，回滚事务' ROLLBACK TRANSACTION END ELSE BEGIN PRINT '插入成功，提交事务' COMMIT TRANSACTION 完成时间：20分钟 END
练习 ——办理毕业学生 BEGIN TRANSACTION DECLARE @errorSum INT 离校手续 • 需求说明： SET @errorSum=0
第六章
事务、视图和索引
回顾2-1
• 下面的SQL语句有错吗？实现了什么功能？
SELECT * FROM Student * FROM Result) FROM Result) WHERE StudentNo IN (SELECT StudentNo
可以 • 上述语句可以用下面的联接替换吗？ SELECT * FROM Student INNER JOIN Result ON Student.StudentNo=Result.StudentNo
事务的特性
• 事务必须具备以下四个属性，简称ACID 属 性： 事务是一个完整的操作
– 原子性（Atomicity）
事务的各步操作是不可分的（原 子的）；要么都执行，要么都不 执行 当事务完成时，数据必须处于一 致状态
– 一致性（Consistency）
– 隔离性（Isolation）
并发事务之间彼此隔离、独立， 它不应以任何方式依赖于或影响 其他事务 事务完成后，它对数据库的修改 被永久保持
SELECT * FROM Student WHERE StudentNo IN (SELECT StudentNo FROM Result)
• SELECT 请使用联接查询改写上述语句 * FROM Student INNER JOIN Result
ON Student.StudentNo = Result.StudentNo
为什么需要事务5-3
• 目前两个账户的余额总和为：1000+1=1001 元
为什么需要事务5-4
• 模拟实现转账
– 从张三的账户转账1000元到李四的账户
/*--转账测试：张三转账1000元给李四--*/ 请问： --我们可能会这样编写语句 执行转账语句后，张三、李四的账户 --张三的账户少1000元，李四的账户多1000元 余额分别是多少？ UPDATE bank SET currentMoney = currentMoney - 1000 WHERE customerName = '张三' UPDATE bank SET currentMoney = currentMoney + 1000 WHERE customerName = '李四' GO --再次查看转账后的结果 SELECT * FROM bank 张三的账户没有减少 GO 但李四的账户却多了1000元 1000＋1001＝2001元 总额多出了1000元！
不可以
有的子查询不能用联接替换。这就是典型的例子
回顾2-3
• 下列语句实现了什么功能？
查询参加考试的学生
SELECT * FROM Student WHERE EXISTS ( SELECT * FROM Result WHERE StudentNo = Student.StudentNo )
• 请使用IN子查询改写上述语句？
如何创建事务2-1
• 使用SQL语句管理事务
– 开始事务
BEGIN TRANSACTION
– 提交事务 COMMIT TRANSACTION
ROLLBACK TRANSACTION – 回滚（撤销）事务
如何创建事务2-2
• 一旦事务提交或回滚，则事务结束 • 判断某条语句执行是否出错：
– 使用全局变量@@ERROR – @@ERROR只判断当前一条T-SQL语句执行是否有 错
什么是事务
• 事务(TRANSACTION)是作为单个逻辑工作单 元执行的一系列操作
• 多个操作作为一个整体向系统提交，要么 都执行、要么都不执行 • 事务是一个不可分割的工作逻辑单元
转账过程就是一个事务
它需要两条UPDATE语句来完成，这两条语句是一个整体
如果其中任一条出现错误，则整个转账业务也应取消，两个账户中的 余额应恢复到原来的数据，从而确保转账前和转账后的余额不变，即 都是1001元