《SchoolInfo数据库》实验答案-实验11-游标

实验：游标一、实验目的与任务目的：旨在训练学生使用游标遍历数据。任务：根据需求，创建游标遍历数据。任务1:创建一个存储过程，根据给定的系名，要求使用游标输出该系的所有学生学号及姓名的信息。答案：createprocprcGetStuInfoByCursor@DepNamevarchar(20)asbegintryifexists(select*fromDepartmentwhereDepName=@DepName)begindeclarecurStudentcursorforselectStuID,StuNamefromStudentjoinDepartmentonStudent.DepID=Department.DepIDwhereDepName=@DepNameopencurStudentdeclare@StuIDchar(10)declare@StuNamevarchar(20)FetchcurStudentInto@StuID,@StuNamewhile(@@Fetch_Status=0)beginprint@StuID+''+@StuNameFetchcurStudentInto@StuID,@StuNameendclosecurStudentdeallocatecurStudentendendtrybegincatchprint'error!'endcatch执行：execprcGetStuInfoByCursor'computer'任务2：使用游标完成教师工资的生成。答案：createprocprcGetTeacherSalaryasbegintryifexists(select*fromTeacher)begindeclare@TeaIDchar(10)declare@TeaTitlevarchar(20)declare@CourseNumintdeclare@WagedecimaldeclarecurTeacherWagecursorforselectTeaID,TeaTitle,CourseNumfromTeacheropencurTeacherWagefetchcurTeacherWageinto@TeaID,@TeaTitle,@CourseNumwhile(@@fetch_Status=0)beginif(@TeaTitle='讲师')set@Wage=1000+80*@CourseNumelseif(@TeaTitle='副教授')set@Wage=1500+90*@CourseNumelseset@Wage=2000+100*@CourseNuminsertintoSalaryvalues(@TeaID,@Wage)fetchcurTeacherWageinto@TeaID,@TeaTitle,@CourseNumendclosecurTeacherWagedeallocatecurTeacherWageendendtrybegincatchprint'error!'endcatch执行：execprcGetTeacherSalary任务3：有Course表和SC表，使用嵌套游标，游标1先找出Course表中的每个课程号，对于找到的课程号，再使用游标2找出选修该课程的所有学生的学号和成绩。答案：alterprocprcGetStuInfoByCursor2asbegintrydeclare@CourseIDintdeclarecurCoursecursorforselectCourseIDfromCourseopencurCoursefetchcurCourseinto@CourseIDwhile(@@fetch_status=0)begindeclare@StuIDchar(10)declare@ScoreintdeclarecurSCcursorforselectStuID,ScorefromSCwhereCourseID=@CourseIDopencurSCfetchcurSCinto@StuID,@Scorewhile(@@fetch_status=0)print@CourseID+''+@StuID+''+@ScorefetchcurSCinto@StuID,@ScoreclosecurSCdeallocatecurSCfetchcurCourseinto@CourseIDendclosecurCoursedeallocatecurCourseendtrybegincatchprint'error!'endcatch执行：execprcGetStuInfoByCursor2三、内容与要求课题名称——游标（一）课题内容按照任务1至任务4逐步完成，完成后学生可以掌握游标遍历数据。（二）课题要求 按照实验任务书逐步完成实验（三）考核及报告要求