《数据库》实验11-游标 (2).doc

1、实验：游标一、实验目的与任务目的：旨在训练学生使用游标遍历数据。任务：根据需求，创建游标遍历数据。任务1: 创建一个存储过程，根据给定的系名，要求使用游标输出该系的所有学生学号及姓名的信息。答案：create proc prcGetStuInfoByCursorDepName varchar(20)asbegin try if exists(select * from Department where DepName = DepName) begin declare curStudent cursor for select StuID,StuName from Student join Dep

2、artment on Student.DepID=Department.DepID where DepName=DepName open curStudent declare StuID char(10) declare StuName varchar(20) Fetch curStudent Into StuID,StuName while(Fetch_Status = 0) begin print StuID + + StuName Fetch curStudent Into StuID,StuName end close curStudent deallocate curStudent

3、endend trybegin catch printerror!end catch执行：exec prcGetStuInfoByCursor computer任务2：使用游标完成教师工资的生成。答案：create proc prcGetTeacherSalaryasbegin try if exists(select * from Teacher) begin declare TeaID char(10) declare TeaTitle varchar(20) declare CourseNum int declare Wage decimal declare curTeacherWage

4、 cursor for select TeaID,TeaTitle,CourseNum from Teacher open curTeacherWage fetch curTeacherWage into TeaID,TeaTitle,CourseNum while(fetch_Status = 0) begin if(TeaTitle = 讲师) set Wage = 1000 + 80 * CourseNum else if(TeaTitle = 副教授) set Wage = 1500 + 90 * CourseNum else set Wage = 2000 + 100 * Cours

5、eNum insert into Salary values(TeaID,Wage) fetch curTeacherWage into TeaID,TeaTitle,CourseNum end close curTeacherWage deallocate curTeacherWage endend trybegin catch printerror!end catch执行：exec prcGetTeacherSalary任务3： 有Course表和SC表，使用嵌套游标，游标1先找出Course表中的每个课程号，对于找到的课程号，再使用游标2找出选修该课程的所有学生的学号和成绩。答案：alt

6、er proc prcGetStuInfoByCursor2asbegin try declare CourseID int declare curCourse cursor for select CourseID from Course open curCourse fetch curCourse into CourseID while(fetch_status = 0) begin declare StuID char(10) declare Score int declare curSC cursor for select StuID,Score from SC where Course

7、ID=CourseID open curSC fetch curSC into StuID,Score while(fetch_status = 0) print CourseID + + StuID + + Score fetch curSC into StuID,Score close curSC deallocate curSC fetch curCourse into CourseID end close curCourse deallocate curCourseend trybegin catch printerror!end catch执行：exec prcGetStuInfoByCursor2三、内容与要求 课题名称游标 （一）课题内容 按照任务1至任务4逐步完成，完成后学生可以掌握游标遍历数据。 （二）课题要求按照实验任务书逐步完成实验（三）考核及报告要求 1. 考核能独立完成任一项任务。2. 报告要求提交一份完整的实验报告，实验报告中详细给出每个任务的解决方案，解决方案后附带每个任务的实验结果。四、主要仪器设备硬件：PC机软件：SQL SERVER 2005