實(shí)驗(yàn)：存儲(chǔ)過程1一、實(shí)驗(yàn)?zāi)康呐c任務(wù)目的：旨在訓(xùn)練學(xué)生能創(chuàng)建帶有輸入?yún)?shù)的存儲(chǔ)過程及帶有return語(yǔ)句的存儲(chǔ)過程任務(wù)：根據(jù)需求，寫出滿足條件的存儲(chǔ)過程。任務(wù)1：在SchoolInfo數(shù)據(jù)庫(kù)的基礎(chǔ)上，創(chuàng)建ReExam表，腳本如下：--創(chuàng)建ReExam表CreatetableReExam( StuIDchar(10)constraintfkStuID1foreignkeyreferencesStudent(StuID), CourseIDintconstraintfkCourseID1foreignkeyreferencesCourse(CourseID)ConstraintpkStuCourse1primarykey(StuID,CourseID), ScorefloatconstraintchkScore1check(Scorebetween0and100))注：該表無(wú)任何記錄！?。?。任務(wù)2:寫存儲(chǔ)過程根據(jù)用戶輸入的不同學(xué)號(hào)，打印該學(xué)號(hào)對(duì)應(yīng)的學(xué)生姓名、年齡和系名。答案：createprocprcGetStuInfoByStuID@StuIDchar(10)ASbegintryifexists(select*fromStudentwhereStuID=@StuID)begindeclare@StuNamevarchar(20)declare@StuAgeintdeclare@DepNamevarchar(20)select@StuName=StuName,@StuAge=StuAge,@DepName=DepNamefromStudentJOINDepartmentonStudent.DepID=Department.DepIDwhereStuID=@StuIDprint@StuNameprint@StuAgeprint@DepNameendelseprint'InfOdoesnotexist!'endtrybegincatchprint'error!'endcatch執(zhí)行：execprcGetStuInfoByStuID'A00001'任務(wù)3:使用存儲(chǔ)過程實(shí)現(xiàn)由用戶輸入學(xué)生的學(xué)號(hào)及課程名稱，根據(jù)輸入的信息，顯示相應(yīng)的成績(jī)，如果成績(jī)小于60分，則向ReExam(StuID,CourseID,Score)表中插入一條記錄。答案：createprocprcGetScore@StuIDchar(10),@CourseNamevarchar(20)asbegintryifexists(select*fromSCjoinCourseonSC.CourseID=SC.CourseIDwhereStuID=@StuIDandCourseName=@CourseName)begindeclare@Gradefloatset@Grade=0declare@CourseIDintset@CourseID=0select@Grade=Score,@CourseID=SC.CourseIDfromSCjoinCourseonSC.CourseID=Course.CourseIDwhereStuID=@StuIDandCourseName=@CourseNameif@Grade<60insertintoReExamvalues(@StuID,@CourseID,@Grade)endelseprint'infodoesnotexist!'endtrybegincatchprint'error!'endcatch執(zhí)行：execprcGetScore'A00001','database'任務(wù)4:寫一存儲(chǔ)過程，根據(jù)不同的系名，查找該系的所有學(xué)生最高入學(xué)成績(jī)(指Student表中的StuScore字段)，假設(shè)最高分大于等于90分，則返回狀態(tài)1，否則返回狀態(tài)0，出錯(cuò)返回狀態(tài)-1,系名不存在返回狀態(tài)-2。用戶調(diào)用該存儲(chǔ)過程，返回值為1，顯示存在學(xué)生成績(jī)大于等于90，返回狀態(tài)0，顯示不存在學(xué)生最高分大于等于90，否則提示用戶出錯(cuò)了。答案：createprocprcGetMAXStuScore@DepNamevarchar(20)asbegintryifexists(select*fromDepartmentwhereDepName=@DepName)begindeclare@StuScoreintdeclare@StuMAXScoreintselect@StuMAXScore=MAX(StuScore)fromStudentjoinDepartmentonStudent.DepID=Department.DepIDwhereDepName=@DepNameif@StuMAXScore>90 return1else return0endelsereturn-2endtrybegincatchreturn-1endcatch執(zhí)行：declare@ReturnValueintexec@ReturnValue=prcGetMAXStuScore'computer'if@ReturnValue=1 print'greaterthan90'elseif@ReturnValue=0 print'lessthan90'elseif@ReturnValue=-2 print'infodoesnotexist'else print'error'三、內(nèi)容與要求課題名稱——存儲(chǔ)過程1（一）課題內(nèi)容按照任務(wù)1至任務(wù)4逐步完成，完成后學(xué)生可以創(chuàng)建帶有輸入?yún)?shù)及return語(yǔ)句的存儲(chǔ)過程。（二）課題要求 按照實(shí)驗(yàn)任務(wù)書逐步完成