实验六 存储过程的设计与应用

实验六 存储过程的设计与应用

实验1、[实验场景]：当学期末课程教学结束后，任课老师在考试后要将学生的成绩录入到“教学管理系统”中，在随后的日期中，任课教师还要经常查询自己任教课程班的学生成绩。

请以编码为“T080040401”的课程班为例，用存储过程实现任课教师的课程班成绩查询T-SQL语句。

图一 查询课程班的成绩

解决：

1、创建存储过程 [参考代码]:

表一 创建存储过程CourseClassGradeQuery

USE DB_TeachingMS GO CREATE PROCEDURE CourseClassGradeQuery AS SELECT TG.StuID,StuName,CommonScore,MiddleScore,LastScore,TotalScore FROM TB_Grade TG,TB_Student TS

2、执行（保存）存储过程

3、执行语句：EXEC CourseClassGradeQuery

拓展练习：在上述存储过程中引入一个参数，用来给存储过程传递【课

程班编码】，然后存储过程再根据传递来的【课程班编码】进行相关的学生成绩查询。请写出创建带【课程班编码】参数的存储过程的T-SQL语句。

实验2、[实验场景]：在某个班级新生记录添加的时候，学号不用手工输入，系统将统计出这个班级已有学生的最大学号，然后在其基础上做自动加1处理后插入到学生表中。插入学生记录的流程如图二所示。

1

请以班级编码为“050802”的“05软件（2）班”为例，用存储过程实现学号自增的学生记录插入功能。

1、查询出该班级最大的学号 2、判断是否存在？否新学号设为班级编码+'01' 是3、取出最大学号的后两位流水号，存入变量中 4、将后两位字符型流水 号转变为数值型，加转换为字符型 1后 5、判断字符型长度为1流水号前添加流水号长度？ 字符'0'长度为26、将班级编码与新得的 两位流水号进行连接，得到新的学号 7、返回新学号

图二 插入学生记录的流程图

[参考代码]:

CREATE PROCEDURE AutoGetStuID @ClassID CHAR(6),@NewStuID CHAR(8) OUTPUT

AS

DECLARE @MaxStuID CHAR(8),@CharTwoStuID CHAR(2),@IntTwoStuID INT SET @MaxStuID =

(SELECT MAX(StuID) FROM TB_Student WHERE ClassID=@ClassID) IF @MaxStuID IS NULL

SET @NewStuID = @ClassID+'01' ELSE BEGIN

SET @CharTwoStuId = RTRIM(@MaxStuID,7,2)

SET @IntTwoStuID = CONVERT(INT,@CharTwoStuID)+1 SET @CharTwoStuID = CONVERT(CHAR,@IntTwoStuID) IF LEN(@CharTwoStuID) = 1

SET @CharTwoStuID='0'+@CharTwoStuID

2

ELSE

SET @NewStuID = @ClassID+@CharTwoStuID END

保存后，执行：

DECLARE @GetedStuID CHAR(8)

EXEC AutoGetStuID '050801',@GetedStuID OUTPUT SELECT @GetedStuID AS NewStuID

结果如图三所示：

图三 获取学生最大学号结果

实验3、[实验场景]：

课程班成绩录入系统后，由于系统记录的是学生的分数，而有的课程班的成绩需要用“优秀、良好、中等、及格和不及格”五个等第进行显示。等第划分的标准是：90分以上为“优秀”，80-89分为“良好”，70-79分为“中等”，60-69分为“及格”，60分以下为“不及格”。

请用带参数的存储过程实现不同课程班成绩的等第自动划分功能。结果如图四：

图四 课程班成绩的等第自动划分

[参考代码]:

USE DB_TeachingMS GO CREATE PROCEDURE GradeLevelSet @CourseClassID CHAR(10) AS

3

SELECT TG.StuID 学号,StuName 姓名,TotalScore 分数, CASE WHEN TotalScore<60 THEN '不及格' WHEN TotalScore>=60 AND TotalScore<70 THEN '及格' WHEN TotalScore>=70 AND TotalScore<80 THEN '中等' WHEN TotalScore>=80 AND TotalScore<90 THEN '良好' WHEN TotalScore>=90 AND TotalScore<=100 THEN '优秀' END AS '等第' FROM TB_Grade TG,TB_Student TS WHERE TG.StuID=TS.StuID AND CourseClassID=@CourseClassID ORDER BY TotalScore DESC

拓展练习：

如果从表“TB_CourseClass”中查询课程班信息时，要将选满标志【FullFlag】字段中的值“U”和“F”分别显示为“未满”和“已满”，如图五所示：请用简单格式CASE语句实现。

图五 拓展练习

4