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

实验六使用T-SQL编写存储过程访问数据库.doc 一 实验目的 1. 理解存储过程的概念、使用方式; 2. 熟悉使用T-SQL编写存储过程来进行数据库应用程序的设计。 二 实验工具SQL Server 2005 利用SQL Server 2005 SSMS及其SQL查询编辑器，使用T-SQL编写存储过程。 三 实验 内容 财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容 和 要求 对教师党员的评价套管和固井爆破片与爆破装置仓库管理基本要求三甲医院都需要复审吗 建立学生-课程数据库，其中包含学生 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 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 实验内容与完成情况: create database zhangsy --Drop database zhangsy go use zhangsy --创建三个表，分别是学生表，课程表和学生选课表; 1 CREATE TABLE Student ( Sno char ( 10 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = '男' OR Ssex = '女'), Sage tinyint CHECK (Sage >= 15 AND Sage <=45), Sdept char (20 ) DEFAULT '计算机系' ) go CREATE TABLE Course ( Cno char(4) NOT NULL, Cname char(20) NOT NULL, Ccredit tinyint CHECK (Ccredit > 0), Period int CHECK (Period > 0), PRIMARY KEY(Cno) ) go CREATE TABLE SC ( Sno char(10) NOT NULL, Cno char(4) NOT NULL, Grade tinyint, CHECK (Grade >= 0 and Grade <= 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) ) go --分别向这三个表中插入数据; Insert into student values('2006010101','张扬','男',19,default) Insert into student values('2006010102','李勇','男',21,default) Insert into student values('2006010103','王小','男',20,'数学系') Insert into student values('2006010104','赵娣','女',18,'外语系') Insert into student values('2006010105','刘柳','女',19,'外语系') go update student set sname='王小' where sno='2006010103' select * from student go Insert into Course values('J001','TC',2,32) Insert into Course values('J002','VB',2,32) 2 Insert into Course values('J003','VC',2,32) Insert into Course values('J004','计算机文化基础',2,32) go select * from course go Insert into SC values('2006010101','J001',85) Insert into SC values('2006010101','J002',80) Insert into SC values('2006010101','J003',78) Insert into SC values('2006010101','J004',90) Insert into SC values('2006010102','J001',75) Insert into SC values('2006010102','J002',60) Insert into SC values('2006010102','J003',77) Insert into SC values('2006010102','J004',92) Insert into SC values('2006010103','J001',85) Insert into SC values('2006010103','J003',90) Insert into SC values('2006010104','J004',89) Insert into SC values('2006010105','J004',76) Go select * from sc Go --查询课程的平均分 create procedure sp_avggrade @cname varchar(20) = 'TC', @avg int output as select @avg=avg(grade) from sc,course where sc.cno=course.cno and course.cname =@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 Declare @avgGrade int EXECUTE sp_avggrade 'VC',@avg=@avgGrade Output PRINT @avgGrade Declare @avgGrade int EXECUTE sp_avggrade '计算机文化基础',@avg=@avgGrade Output PRINT @avgGrade --查询指定系的学生的学号，姓名，所在系，课程名和成绩 Create Procedure sp_DispSC_Sdept @sdept varchar(20)='计算机系' AS 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 where Sdept=@sdept Go EXECUTE sp_DispSC_Sdept EXECUTE sp_DispSC_Sdept '信息系' EXECUTE sp_DispSC_Sdept @sdept='数学系' 10-3 ----查询指定系，指定成绩等级的学生的学号，姓名，所在系，课程名和成绩 Create Procedure 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= Case When @GradeLevel='优' then 'between 90 And 100' When @GradeLevel='良' then 'between 80 And 89' When @GradeLevel='中' then 'between 70 And 79' When @GradeLevel='及格' then 'between 60 And 69' When @GradeLevel='不及格' then 'between 0 And 59' When @GradeLevel IS NULL then 'IS NULL' Else 'LevelError' end IF @GradeStr='LevelError' print '错误:输入的成绩等级不符合要求～' 4 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