1、实验:存储过程1一、实验目的与任务目的:旨在训练学生能创建带有输入参数的存储过程及带有return语句的存储过程任务:根据需求,写出满足条件的存储过程。任务1:在SchoolInfo数据库的基础上,创建ReExam表,脚本如下:-创建ReExam表Create table ReExam(StuID char(10) constraint fkStuID1 foreign key references Student(StuID),CourseID int constraint fkCourseID1 foreign key references Course(CourseID) Constra
2、int pkStuCourse1 primary key(StuID,CourseID), Score float constraint chkScore1 check(Score between 0 and 100)注:该表无任何记录!。任务2:写存储过程根据用户输入的不同学号,打印该学号对应的学生姓名、年龄和系名。答案:create proc prcGetStuInfoByStuIDStuID char(10)ASbegin try if exists(select * from Student where StuID=StuID) begin declare StuName varcha
3、r(20) declare StuAge int declare DepName varchar(20) select StuName=StuName,StuAge=StuAge,DepName=DepName from Student JOIN Department on Student.DepID=Department.DepID where StuID=StuID print StuName print StuAge print DepName end else printInfO does not exist!end trybegin catchprinterror!end catch
4、执行:exec prcGetStuInfoByStuID A00001任务3: 使用存储过程实现由用户输入学生的学号及课程名称,根据输入的信息,显示相应的成绩,如果成绩小于60分,则向ReExam(StuID,CourseID,Score)表中插入一条记录。答案:create proc prcGetScoreStuID char(10),CourseName varchar(20)as begin try if exists(select * from SC join Course on SC.CourseID=SC.CourseID where StuID=StuID and CourseN
5、ame=CourseName) begin declare Grade float set Grade=0 declare CourseID int set CourseID=0 select Grade=Score,CourseID=SC.CourseID from SC join Course on SC.CourseID=Course.CourseID where StuID=StuID and CourseName=CourseName if Grade90return 1 elsereturn 0 end else return -2 end try begin catch retu
6、rn -1 end catch执行:declare ReturnValue intexec ReturnValue = prcGetMAXStuScore computerif ReturnValue = 1print greater than 90else if ReturnValue = 0print less than 90else if ReturnValue = -2print info does not existelseprint error三、内容与要求 课题名称存储过程1 (一)课题内容 按照任务1至任务4逐步完成,完成后学生可以创建带有输入参数及return语句的存储过程。 (二)课题要求按照实验任务书逐步完成实验(三)考核及报告要求 1. 考核能独立完成任一项任务。2. 报告要求提交一份完整的实验报告,实验报告中详细给出每个任务的解决方案,解决方案后附带每个任务的实验结果。四、主要仪器设备硬件:PC机软件:SQL SERVER 2005