SQL Server数据库应用与实践教程(第2版) 实验文档(带答案) 实验9-事务.doc

实验：事务1 一、实验目的与任务 目的：旨在训练学生能创建带有事务的存储过程及了解事务的并发情况。 任务：根据需求，写出满足条件的存储过程。 任务1：在SchoolInfo数据库中执行如下语句： create table Ticket ( TicketInfo varchar(20) primary key, TicketLeaveNum int ) insert into Ticket values(G0001,10) 任务2:测试事务执行情况。 create proc prcInsertStudent as begin try begin transaction INSERT INTO Student VALUES(A00010,‘Andy,21,男,Nanjing,90,1) INSERT INTO Student VALUES(‘A00011’,‘mary’,21,女,Nanjing,150,1) commit transaction end try begin catch rollback transaction end catch 以上事务执行时，A00011号记录的成绩值超出了范围，编译器报错，此时会被Catch语句捕获，回滚事务，数据库中两条记录都不能插入。 可将A00011号记录的成绩改为90，重新测试存储过程，两条语句同时被成功插入数据库。 任务3: 创建事务，假设给定学号的学生将从原系转入给定的系，则相应的系总人数将发生变化，如果两系相差人数小于10，则允许该生转系，否则回滚事务。假设系表Department(DepID,DepName,Total) 答案： create proc prcTransfer @StuID char(10), @DepName varchar(20) as begin try if exists(select * from Student where StuID=@StuID) if exists(select * from Department where DepName=@DepName) begin begin transaction declare @OriginDepID int select @OriginDepID=DepID from Student where StuID=@StuID declare @NewDepID int select @NewDepID=DepID from Department where DepName=@DepName update Student set DepID=@NewDepID where StuID=@StuID declare @OriginTotal int declare @NewTotal int update Department set Total=Total-1 where DepID=@OriginDepID update Department set Total=Total+1 where DepID=@NewDepID select @OriginTotal=Total from Department where DepID=@OriginDepID select @NewTotal=Total from Department where DepID=@NewDepID if(@NewTotal-@OriginTotal=-10 and @NewTotal-@OriginTotal=10) commit Transaction else Rollback Transaction end else printDepName不存在 else printStuID不存在 end try begin catch Rollback Transaction end catch 执行： exec prcTransferA00001,math 任务4: SC表和Department表需要用下面的事务来更新： 事务1: UPDAT