实验六使用T-SQL编写存储过程访问数据库

1一实验目的1.理解存储过程的概念、使用方式；2.熟悉使用T-SQL编写存储过程来进行数据库应用程序的设计。二实验工具SQLServer2005利用SQLServer2005SSMS及其SQL查询编辑器，使用T-SQL编写存储过程。三实验内容和要求建立学生-课程数据库，其中包含学生表Student(Sno,Sname,Ssex,Sage,Sdept)、课程表：Course(Cno,Cname,Cpno,Ccredit)和学生选课表：SC(Sno,Cno,Grade)；编写相应的存储过程，完成下面的功能：（1）编写一个存储过程，可以查询指定系的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。（2）编写一个存储过程，返回指定课程的平均分。调用该存储过程，测试执行结果。（3）编写一个存储过程，可以查询指定系指定成绩等级的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。（成绩等级为优、良、中、及格、不及格，其中成绩在90分到100分之间为‘优’，在80分到89分之间为‘良’，在70分到79分之间为‘中’，在60分到69分之间为‘及格’，在0分到59分之间为‘不及格’。）要求：提交创建存储过程的SQL脚本，并标识必要的注释。保证程序能够正确编译和运行，并有相应的测试代码。四实验报告4.1实验环境：4.2实验内容与完成情况：createdatabasezhangsy--Dropdatabasezhangsygousezhangsy--创建三个表，分别是学生表，课程表和学生选课表；2CREATETABLEStudent(Snochar(10)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage=15ANDSage=45),Sdeptchar(20)DEFAULT'计算机系')goCREATETABLECourse(Cnochar(4)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit0),PeriodintCHECK(Period0),PRIMARYKEY(Cno))goCREATETABLESC(Snochar(10)NOTNULL,Cnochar(4)NOTNULL,Gradetinyint,CHECK(Grade=0andGrade=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))go--分别向这三个表中插入数据；Insertintostudentvalues('2006010101','张扬','男',19,default)Insertintostudentvalues('2006010102','李勇','男',21,default)Insertintostudentvalues('2006010103','王小','男',20,'数学系')Insertintostudentvalues('2006010104','赵娣','女',18,'外语系')Insertintostudentvalues('2006010105','刘柳','女',19,'外语系')goupdatestudentsetsname='王小'wheresno='2006010103'select*fromstudentgoInsertintoCoursevalues('J001','TC',2,32)InsertintoCoursevalues('J002','VB',2,32)3InsertintoCoursevalues('J003','VC',2,32)InsertintoCoursevalues('J004','计算机文化基础',2,32)goselect*fromcoursegoInsertintoSCvalues('2006010101','J001',85)InsertintoSCvalues('2006010101','J002',80)InsertintoSCvalues('2006010101','J003',78)InsertintoSCvalues('2006010101','J004',90)InsertintoSCvalues('2006010102','J001',75)InsertintoSCvalues('2006010102','J002',60)InsertintoSCvalues('2006010102','J003',77)InsertintoSCvalues('2006010102','J004',92)InsertintoSCvalues('2006010103','J001',85)InsertintoSCvalues('2006010103','J003',90)InsertintoSCvalues('2006010104','J004',89)InsertintoSCvalues('2006010105','J004',76)Goselect*fromscGo--查询课程的平均分createproceduresp_avggrade@cnamevarchar(20)='TC',@avgintoutputasselect@avg=avg(grade)fromsc,coursewheresc.cno=course.cnoandcourse.cname=@cnameif@avg=0print'没有学生选此门课'elsereturn@avggoDeclare@avgGradeintEXECUTEsp_avggrade@avg=@avgGradeOutputPRINT@avgGradeDeclare@avgGradeintEXECUTEsp_avggrade'VB',@avg=@avgGradeOutputPRINT@avgGrade4Declare@avgGradeintEXECUTEsp_avggrade'VC',@avg=@avgGradeOutputPRINT@avgGradeDeclare@avgGradeintEXECUTEsp_avggrade'计算机文化基础',@avg=@avgGradeOutputPRINT@avgGrade--查询指定系的学生的学号，姓名，所在系，课程名和成绩CreateProceduresp_DispSC_Sdept@sdeptvarchar(20)='计算机系'ASSelectS.sno,S.sname,S.Sdept,C.Cname,SC.gradeFromStudentSLeftJoinSConS.sno=SC.snoLeftJoinCourseConSC.Cno=C.cnowhereSdept=@sdeptGoEXECUTEsp_DispSC_SdeptEXECUTEsp_DispSC_Sdept'信息系'EXECUTEsp_DispSC_Sdept@sdept='数学系'10-3----查询指定系，指定成绩等级的学生的学号，姓名，所在系，课程名和成绩CreateProceduresp_DispSC_Dept_Level@sdeptvarchar(20)='计算机系',@GradeLevelvarchar(6)ASDeclare@SQLTextvarchar(200),@GradeStrvarchar(30)Set@SQLText='SelectS.sno,S.sname,S.Sdept,C.Cname,SC.gradeFromStudentSLeftJoinSConS.sno=SC.snoLeftJoinCourseConSC.Cno=C.cno'Set@GradeStr=CaseWhen@GradeLevel='优'then'between90And100'When@GradeLevel='良'then'between80And89'When@GradeLevel='中'then'between70And79'When@GradeLevel='及格'then'between60And69'When@GradeLevel='不及格'then'between0And59'When@GradeLevelISNULLthen'ISNULL'Else'LevelError'endIF@GradeStr='LevelError'print'错误：输入的成绩等级不符合要求！'5ElseExecute(@SQLText+'whereSdept='''+@sdept+'''AndGrade'+@GradeStr)GOEXECUTEsp_DispSC_Dept_Level@GradeLevel='优'ExecuteSP_DispSC_Dept_Level'信息系','优'EXECUTEsp_DispSC_Dept_Leveldefault,@GradeLevel='优'4.3出现的问题：在调用查询课程的平均分的存储的时候出现了问题，提示@avg没有赋值4.4解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：命名一个对象@avgGrade把@avg传给它就可以得到结果。