第12章 游标和事务(例题).doc

游标的应用利用所学知识，结合示例分析如何创建、使用游标。【例12-7】创建游标，并读取游标的数据。显示学生表（tblStudents）的信息。USE CJMS -使用数据库CJMSDECLARE ST_cursor SCROLL CURSOR -声明游标名FOR SELECT * FROM tblStudents -标准游标-FOR READ ONLY 只读游标-FOR UPDATE 更新游标OPEN ST_cursor -打开游标FETCH FIRST FROM ST_cursor -返回游标中的第一行FETCH NEXT FROM ST_cursor -返回游标中的当前记录的下一行FETCH LAST FROM ST_cursor -返回游标中的最后行FETCH PRIOR FROM ST_cursor -返回游标中的最前行FETCH ABSOLUTE 2 FROM ST_cursor -返回游标中的第二行FETCH RELATIVE 2 FROM ST_cursor -返回游标中的当前位置起向后第二行CLOSE ST_cursor -关闭游标DEALLOCATE ST_cursor -释放游标执行该SQL语句，显示如图12-1所示效果。图12-1游标的基本操作【例12-8】利用游标更新数据，首先查看tblStudents表中的每一行，将Classes等于电子0801的记录的Department改为电子工程系。USE CJMS -使用数据库CJMSDECLARE studentId nchar(10),name nchar(10),sex nchar(2), classes nchar(50),Departments nchar(50)DECLARE edit_cursor CURSOR -声明游标名FOR SELECT StudentID,Sname,Sex,Classes,Department FROM tblStudents -标准游标-FOR READ ONLY 只读游标FOR UPDATE -更新游标OPEN edit_cursor -打开游标FETCH NEXT FROM edit_cursor INTO studentId,name,sex,classes,Departments -从游标中提取第一行WHILE FETCH_STATUS=0BEGINIF classes =电子0801UPDATE tblStudents SET Department=电子工程系WHERE CURRENT OF edit_cursorFETCH NEXT FROM edit_cursor INTO studentId,name,sex,classes,Departments -从游标中提取下一行ENDCLOSE edit_cursor -关闭游标DEALLOCATE edit_cursor -释放游标运行该SQL代码，显示如图12-2所示的效果。图12-2 利用游标更新数据【例12-9】利用游标判断记录是否存在，查找tblStudents表中StudentID等于S200820005的记录是否存在。USE CJMSDECLARE SER_CURSOR CURSORFOR SELECT * FROM tblStudents WHERE StudentID=08102101OPEN SER_cursorFETCH NEXT FROM SER_CURSORIF FETCH_STATUS =0 PRINT 该记录存在ELSE PRINT 该记录不存在CLOSE SER_cursorDEALLOCATE SER_cursor运行该SQL代码，显示如图12-3所示的效果。图12-3利用游标判断记录是否存在事务的应用【例12-10】在成绩管理系统中删除课程信息表（tblCourses）中“操作系统（C8030268）”这门课程，将学生课程成绩表（tblScore）中的“操作系统”的课程号（C8030268）改为“计算机操作系统”的课程号（C8030268）。BEGIN TRANSACTION my_Tran_delete -开始一个事务 USE CJMS -使用数据库CJMS GO DELETE FROM tblCourses WHERE CourseID=C8030268 SAVE TRANSACTION after_delete -设置事务恢复点 UPDATE tblScore SET CourseID=C8030269 WHERE CourseID=C8030268 -将学生课程成绩表（tblScore）中的“操作系统”的课程号改为“计算机操作系统”的课程号 IF ERROR0 OR ROWCOUNT=0 -检查是否更新成功，ERROR返回上一个SQL语句状态，非零即说明出错，错则回滚之 BEGIN ROLLBACK TRAN after_delete-回滚到保存点after_delete，如果使用ROLLBACK TRAN my_Tran_delete，则会回滚到事务开始前 COMMIT TRAN PRINT 更新信息产生错误 RETURN ENDCOMMIT TRANSACTION my_Tran_deleteGO 说明：如果不指定回滚的事务名称或保存点，则ROLLBACK TRANSACTION命令会将事务回滚到事务执行前，如果事务是嵌套的，则会回滚到最靠近的BEGIN TRANSACTION命令前。【例12-11】在成绩管理系统中将某学生的学号由原来的S200820008改为S200820208 ，修改涉及到“tblStudents”和“tblScore”两个表，本事务的操作为了保证两个表的数据一致性。BEGIN TRANSACTION my_Tran_update -开始一个事务 USE CJMS -使用数据库CJMS GO UPDATE tblStudents SET StudentID=S200820208 WHERE StudentID=S200820008 -将学生信息表（tblScore）中的学号由原来的S200820008改为S200820208 IF ERROR0 -检查是否更新成功，ERROR返回上一个SQL语句状态，非零即说明出错，错则回滚之 BEGIN PRINT 更新学生信息表出现错误 ROLLBACK TRAN -回滚事务 RETURN END UPDATE tblScore SET StudentID=S200820208 WHERE StudentID=S200820008 -将学生课程成绩表（tblScore）中的学号由原来的S200820008改为S200820208 IF ERROR0 BEGIN PRINT 更新学生课程成绩表出现错误 ROLLBACK TRAN -回滚事务 RETURN END COMMIT TRANSACTION my_Tran_updateGO说明：要对表中有外键约束的数据进行修改时候，应先修改外键关系中的INSERT和UPDATE规范里的更新规则或删除规则进行更改。【例12-12】游标和事务的综合使用，给所有的同学的“C语言程序设计（C8030120）”课程加上5分。BEGIN TRAN -开始事务 USE CJMS -使用数据库 SET NOCOUNT ON -不显示计数信息 DECLARE studentID char(10),courseID char(8) DECLARE Score_cursor CURSOR FOR -声明游标 SELECT StudentID,CourseID FROM tblScore WHERE CourseID=C8030120 OPEN Score_cursor -打开游标 FETCH NEXT FROM Score_cursor INTO studentID,courseID -取第一行的值给变量: studentID,courseID UPDATE tblScore-更改成绩，加上分 SET Grade = Grade+5 WHERE CourseID=courseID AND StudentID=studentID IF error!=0 -执行错误回滚 BEGIN ROLLBACK TRAN RETURN END WHILE FETCH_STATUS = 0 -移动游标,其它所有行更新操作(当到结尾时退出) BEGIN FETCH NEXT FROM Score_cursor -游标移到下一行 INTO studentID,courseID -取第一行的值给变量: studentID,courseID UPDATE tblScore set Grade = Grade+5 WHERE CourseID=courseID AND StudentID=studentID IF error!=0 -执行错误回滚 BEGIN ROLLBACK TRAN RETURN ENDEND COMMIT TRAN -提交所有变更 CLOSE Score_cursor -关闭游标 DEALLOCATE Score_cursor -释放游标 SET NOCOUNT OFF -恢复设置 GO【例12-13】事务在银行转帐过程的中的简单应用，将张三的账户(14200101033014122)转1000到李四的账户（14200101000014243）上。USE stuDB -使用数据库GOSET NOCOUNT ON -不显示受影响的行数信息print 查看转帐事务前的余额SELECT * FROM bank GOBEGIN TRANSACTION -开始事务（指定事务从此处开始，后续的T-SQL语句是一个整体DECLARE errorSum INT -定义变量，用于累计事务执行过程中的错误SET errorSum=0 -初始化为，即无错误UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerNumber=14200101033014122 -转出：张三的账户少了1000元，SET errorSum=errorSum+error -累计是否有错误UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerNumber=14200101000014243 -转入：李四的账户多了1000元SET errorSum=errorSum+error -累计是否有错误print 查看转帐事务过程中的余额SELECT * FROM bank WHERE IF errorSum0 -根据是否有错误，确定事务是提交还是