淮阴工学院数据库实验八

GO 图1.实验1.1 (2) 定义一个事务,将表SC中所有选了C3号课程的学生分数增加5%,并提交该事务。 USE JXGL1 GO DECLARE @TranName varchar(20) SELECT @TranName= 'Add_Grade' BEGIN TRAN @TranName UPDATE SC SET grade= grade + grade * 0.05 WHERE cno= 'C3' COMMIT TRAN @TranName GO 图2.实验1.2 (3) 定义一个事务,将删除表SC中“S9”的学生成绩和s表中学号为“S9”的学生记录定义 2 / 13

为一个事务,执行该事务并提交。 USE JXGL1 GO DECLARE @TranName varchar(20) SELECT @TranName = 'Del_Grade ' BEGIN TRAN @TranName DELETE FROM SC WHERE sno = 'S9 ' DELETE FROM S WHERE sno = 'S9' COMMIT TRAN @TranName GO 图3.实验1.3 2. 当对多个表进行更新的时候某条执行失败,为了保持数据的完整性需要使用事务回滚。 (1) 显式事务。当对表SC进行插入操作时S表中没有“S10”记录,所以插人失败。 USE JXGL1 GO BEGIN TRY BEGIN TRANSACTION INSERT INTO SC(sno, cno) VALUES( 'S10', 'C4') COMMIT TRANSACTION END TRY BEGIN CATCH SELECT ERROR_NUMBER( ) AS ERRORNUMBER ROLLBACK TRANSACTION END CATCH 3 / 13

GO 图4.实验2.1 (2) 隐式设置事务。当对表S进行插入操作时S表中有“S4\" ,所以插入失败。 USE JXGL1 GO SET IMPLICIT_TRANSACTIONS ON BEGIN TRY INSERT INTO S( sno, sname, sex) VALUES('S4', '王莹','女') INSERT INTO SC VALUES( 'S4', 'C4',90) COMMIT TRANSACTION END TRY BEGIN CATCH SELECT ERROR_NUMBER( ) AS ERRORNUMBER ROLLBACK TRANSACTION END CATCH SET IMPLICIT_TRANSACTIONS OFF GO 4 / 13

图5.实验2.2 3. SQL事务处理实验 (1) 当第1条有效,第2条有误时,会保证两条记录都不写入数据库。 USE JXGL1 GO BEGIN TRAN INSERT INTO S( sno, sname, age, sex, sdept) VALUES('S20', '王荣格',21,'F', 'CS') INSERT INTO S( sno, sname, age, sex, sdept) VALUES('S20', '王连福',22,'M', 'MA') IF @@ERROR<> 0 ROLLBACK TRAN COMMIT TRAN GO 图6.实验3.1 5 / 13

(2) 当第1条有误,第2条有效时,第1条不写人数据库,但第2条写入数据库。 USE JXGL1 GO INSERT INTO S( sno, sname, sex, age, sdept) VALUES('S20', '王荣格','女',21, 'CS') BEGIN TRAN INSERT INTO S( sno, sname, sex, age, sdept) VALUES( 'S20', '王连福', '男',22, 'MA') IF @@ERROR<> 0 ROLLBACK TRAN INSERT INTO S( sno, sname, sex, age, sdept) VALUES ('S21', '钱云华','男',21, 'MA') IF @@ERROR<> 0 ROLLBACK TRAN COMMIT TRAN GO 图7.实验3.2 (3) 不管哪条数据有问题都能保证不写入数据库。 USE JXGL1 GO BEGIN TRY BEGIN TRAN tempTran INSERT INTO S( sno, sname, sex, age, sdept) VALUES('S21', '王金霞', 22, 'F', 'IS' ) INSERT INTO C1(cno, cname, cdept, tname) VALUES('C14', '汇编语言', 'CS','马政') INSERT INTO SC( sno, cno, grade) VALUES ( 'S20', 'C14',78) COMMIT TRAN tempTran END TRY BEGIN CATCH ROLLBACK TRAN tempTran PRINT 'have error, please check. ' 6 / 13

END CATCH GO 图8.实验3.3 4. 简单银行转账程序设计 (1) 创建存储过程proc_takemoney,完成转账过程中的账户余额变换及存储支取和存人记录。 USE BANK GO CREATE PROCEDURE proc_takemoney @cardID1 varchar(20), @pwd char(6), @takemoney money, @cardID2 varchar(20) AS SET NOCOUNT ON DECLARE @err INT SET @err = 0 IF EXISTS(SELECT * FROM CARDINFO WHERE cardid= @cardID1 AND pass = @pwd) BEGIN BEGIN TRAN UPDATE CARDINFO SET BALANCE= BALANCE + @takemoney WHERE cardID = @cardID2 --接受款账户加上@takemoney SET @err = @err + @@error --设置错误信息提示 UPDATE CARDINFO SET balance= balance - @takemoney WHERE cardID = @cardID1 --汇款账户减去@takemoney SET @err = @err + @@error 7 / 13

INSERT INTO TRANINFO ( transdate, cardid, transtype, transmoney) VALUES ( GETDATE( ), @cardID1, '支取', @takemoney) SET @err = @err + @@error INSERT INTO TRANINFO ( transdate, cardid, transtype, transmoney) VALUES ( GETDATE( ), @cardID1, '存入', @takemoney) SET @err = @err + @@error SELECT TOP 2 * FROM TRANINFO ORDER BY TRANSDATE DESC IF(@err<> 0) BEGIN ROLLBACK TRAN--错误回滚事物 END ELSE BEGIN COMMIT TRAN --无错误提交事物 END END --IF结束 ELSE BEGIN PRINT @err --输出错误提示信息 END GO 图9.实验4.1 (2) 调用存储过程proc_takemoney, 完成账户A向账户B转账1000 元。 USE BANK 8 / 13

GO DECLARE @cardID1 varchar(19), @cardID2 varchar(19), @pwd char(6), @takemoney money SET @cardID1 = '6227300292160184140' --账户B卡号 SET @pwd = '123456' -- 账户B密码 SET @cardID2 = '6227300274694561341' --账户A卡号 SET @takemoney = 1000 --汇款金额 EXEC dbo.proc_takemoney @cardID1 , @pwd, @takemoney, @cardID2 GO 图10.实验4.2 5. 复杂事务的处理 (1) 设计并执行事务:学生“王晓霞”打算选修“离散数学”课程。根据规定,此门课程选修的人数最多为30人,该生是否可以选修此门课程,并给出提示结果。 USE JXGL1 GO BEGIN TRAN DECLARE @person_num tinyint, @c_no char(4),@s_no char(8) SELECT @c_no= cno FROM C WHERE cname= 'MA' SELECT @s_no= sno FROM S WHERE sname= '王晓霞' SELECT @person_num = COUNT(*) FROM SC WHERE cno= @c_no IF @person_num < 30 BEGIN INSERT INTO SC( sno, cno) VALUES(@s_no, @c_no) COMMIT TRAN --提交事务 PRINT'王晓霞同学选修离散数学课程注册成功!' END ELSE BEGIN ROLLBACK TRAN --回滚事务 9 / 13

PRINT'选修离散数学课程的人数已满,王晓霞同学不能再选修此课程!' END GO 图11.实验5.1 (2) 设计并执行事务:李守信老师是MA系的老师,想到CS系应聘“数据库系统与应用”的课程教学。学校招聘的原则是若应聘人员是副教授以上职称且目前任课教师人数少于两人，则应聘成功,并把该教师的教师号录人教师任课信息表T_info(),否则不予接纳。 USE JXGL1 GO BEGIN TRAN DECLARE @person_num tinyint, @c_no char(4),@c_name char(4),@t_no char(4),@t_name char(8) SELECT @c_no= cno FROM C WHERE cname = '数据库系统与应用' SELECT @person_num = COUNT( * ) FROM SC WHERE cno= @c_no SET @t_name= '李守信' IF @person_num<= 2 BEGIN --不能招聘 ROLLBACK TRAN PRINT'因数据库系统与应用课程任课人数已满,故李守信老师不能再应聘该课程岗位!' END ELSE BEGIN --接受招聘 SELECT @t_no= Tno FROM T_info WHERE tname= @t_name INSERT INTO TC_info( cno, cname, tno, tname) VALUES(@c_no,'数据库系统与应用',@t_no, @t_name ) 10 / 13

COMMIT TRAN --提交事务 PRINT '李守信老师应聘数据库系统与应用课程成功!' END 图12.实验5.2 6. 事务的保存点实验 (1) 定义一个事务,向JXGL数据库的S表中插入一行数据,然后删除该行。 USE JXGL1 GO BEGIN TRANSACTION INSERT INTO S( sno, sname, sex, age, sdept) VALUES('S14', '高艳霞','女',22,'IS') SAVE TRAN My_sav DELETE FROM S WHERE sname = '高艳霞' ROLLBACK TRAN My_sav COMMIT TRAN GO 11 / 13

图13.实验6.1 (2) 退到指定的事务保存点。 USE JXGL1 GO BEGIN TRAN SAVE TRAN sp1 INSERT INTO S( sno, sname, sex, age, sdept) VALUES( 'S21', '王忠明', 'M',21, 'MA') SAVE TRAN sp2 INSERT INTO S( sno, sname, sex, age, sdept) VALUES( 'S22', '田秀荣', 'F',22, 'IS') SAVE TRAN sp3 INSERT INTO S( sno, sname, sex, age, sdept) VALUES( 'S23', '马忠波', 'M',20, 'CS') SAVE TRAN sp4 ROLLBACK TRAN sp3 COMMIT TRAN GO 图14.实验6.2 12 / 13

三、实验体会 这个实验主要是关于事务的运行，我在做实验的时候没有出现太大的问题，但是还是有少部分的问题，比如实验1.2中*5%要改成*0.05不然会报错；实验2中要看清楚到底是要有sno还是没有，不然事务的结果是截然相反的；实验3中到底是要写进数据还是不写入，判断时要看清要求；实验5.1中的王晓霞要现在S表里录入此人的信息才能实现，否则会报告没有sno。这次实验主要是看清楚题目到底要求是什么，不要一味的照着输代码。

13 / 13