2022年实验六使用T-SQL编写存储过程访问数据库 2.pdf

1 一 实验目的1.理解存储过程的概念、使用方式；2.熟悉使用 T-SQL编写存储过程来进行数据库应用程序的设计。二 实验工具 SQL Server 2005 利用 SQL Server 2005 SSMS 及其 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 实验内容与完成情况：createdatabase zhangsy-Drop database zhangsy go use zhangsy-创建三个表，分别是学生表，课程表和学生选课表；名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 5 页 -2 CREATETABLE Student (Sno char(10)PRIMARYKEY,Sname char(10)NOT NULL,Ssex char(2)CHECK(Ssex=男OR Ssex=女),Sage tinyintCHECK(Sage=15 AND Sage 0),Period intCHECK(Period 0),PRIMARYKEY(Cno)go CREATETABLE SC (Sno char(10)NOT NULL,Cno char(4)NOT NULL,Grade tinyint,CHECK(Grade=0 and Grade=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCES Student(Sno),FOREIGNKEY(Cno)REFERENCES Course(Cno)go-分别向这三个表中插入数据；Insertinto student values(2006010101,张扬 ,男,19,default)Insertinto student values(2006010102,李勇 ,男,21,default)Insertinto student values(2006010103,王小 ,男,20,数学系 )Insertinto student values(2006010104,赵娣 ,女,18,外语系 )Insertinto student values(2006010105,刘柳 ,女,19,外语系 )go update student set sname=王小 where sno=2006010103 select*from student go Insertinto Course values(J001,TC,2,32)Insertinto Course values(J002,VB,2,32)名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 5 页 -3 Insertinto Course values(J003,VC,2,32)Insertinto Course values(J004,计算机文化基础,2,32)go select*from course go Insertinto SC values(2006010101,J001,85)Insertinto SC values(2006010101,J002,80)Insertinto SC values(2006010101,J003,78)Insertinto SC values(2006010101,J004,90)Insertinto SC values(2006010102,J001,75)Insertinto SC values(2006010102,J002,60)Insertinto SC values(2006010102,J003,77)Insertinto SC values(2006010102,J004,92)Insertinto SC values(2006010103,J001,85)Insertinto SC values(2006010103,J003,90)Insertinto SC values(2006010104,J004,89)Insertinto SC values(2006010105,J004,76)Go select*from sc Go-查询课程的平均分createprocedure sp_avggrade cname varchar(20)=TC,avg intoutput as select avg=avg(grade)from sc,course where o=o and ame=cname if avg=0 print 没有学生选此门课 else return avg go Declare avgGrade int EXECUTE sp_avggrade avg=avgGrade Output PRINT avgGrade Declare avgGrade int EXECUTE sp_avggrade VB,avg=avgGrade Output PRINT avgGrade 名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 5 页 -4 Declare avgGrade int EXECUTE sp_avggrade VC,avg=avgGrade Output PRINT avgGrade Declare avgGrade int EXECUTE sp_avggrade 计算机文化基础,avg=avgGrade Output PRINT avgGrade-查询指定系的学生的学号，姓名，所在系，课程名和成绩CreateProcedure sp_DispSC_Sdept sdept varchar(20)=计算机系 AS Select S.sno,S.sname,S.Sdept,C.Cname,SC.grade From Student S LeftJoin SC on S.sno=SC.sno LeftJoin Course C on SC.Cno=C.cno where Sdept=sdept Go EXECUTE sp_DispSC_Sdept EXECUTE sp_DispSC_Sdept 信息系 EXECUTE sp_DispSC_Sdept sdept=数学系 10-3-查询指定系，指定成绩等级的学生的学号，姓名，所在系，课程名和成绩CreateProcedure sp_DispSC_Dept_Level sdept varchar(20)=计算机系 ,GradeLevel varchar(6)AS Declare SQLText varchar(200),GradeStr varchar(30)Set SQLText=Select S.sno,S.sname,S.Sdept,C.Cname,SC.grade From Student S Left Join SC on S.sno=SC.sno Left Join Course C on SC.Cno=C.cno Set GradeStr=CaseWhen GradeLevel=优thenbetween 90 And 100 When GradeLevel=良thenbetween 80 And 89 When GradeLevel=中thenbetween 70 And 79 When GradeLevel=及格 thenbetween 60 And 69 When GradeLevel=不及格 thenbetween 0 And 59 When GradeLevel ISNULLthenIS NULL ElseLevelErrorend IF GradeStr=LevelErrorprint 错误：输入的成绩等级不符合要求！名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 5 页 -5 Else Execute(SQLText+where Sdept=+sdept+And Grade +GradeStr)GO EXECUTE sp_DispSC_Dept_Level GradeLevel=优 Execute SP_DispSC_Dept_Level 信息系 ,优 EXECUTE sp_DispSC_Dept_Level default,GradeLevel=优4.3 出现的问题：在调用查询课程的平均分的存储的时候出现了问题，提示avg 没有赋值4.4 解决方案（列出遇到的问题和解决办法，列出没有解决的问题）：命名一个对象 avgGrade把avg传给它就可以得到结果。名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 5 页 -