1)创建一个查询存储过程,过程名称为P_CHX。参数是学号,输出信息是指定学号所对应的姓名、课程名称、成绩。运行时若没有指定学号,则提示请输入学号,若学号不存在则提示学号不存在; 2)创建一个统计存储过程,过程名称为P_TJ。参数是学号,输出信息是指定学号的姓名、选课门数、平均分、总分、最高分、最低分。运行时若没有指定学号,则提示输入学号;若学号不存在则提示学号不存在; 3)查询平均分数在x到y范围内的学生信息(学号、姓名、院系名称和平均分数)。提示:该存储过程有两个参数。 4)自行再分别设计一个完成查询和完成操作功能的存储过程(在实验报告中要准确描述功能需求)。
时间: 2023-12-10 17:42:48 浏览: 88
1)P_CHX存储过程代码:
```
CREATE PROCEDURE P_CHX
@stu_id INT
AS
BEGIN
IF @stu_id IS NULL
BEGIN
PRINT '请输入学号'
RETURN
END
DECLARE @name VARCHAR(20)
DECLARE @course_name VARCHAR(20)
DECLARE @score FLOAT
SELECT @name = name, @course_name = course_name, @score = score
FROM scores
WHERE stu_id = @stu_id
IF @@ROWCOUNT = 0
BEGIN
PRINT '学号不存在'
RETURN
END
PRINT '姓名:' + @name + ',课程名称:' + @course_name + ',成绩:' + CAST(@score AS VARCHAR(10))
END
```
2)P_TJ存储过程代码:
```
CREATE PROCEDURE P_TJ
@stu_id INT
AS
BEGIN
IF @stu_id IS NULL
BEGIN
PRINT '请输入学号'
RETURN
END
DECLARE @name VARCHAR(20)
DECLARE @num_courses INT
DECLARE @avg_score FLOAT
DECLARE @total_score FLOAT
DECLARE @max_score FLOAT
DECLARE @min_score FLOAT
SELECT @name = name,
@num_courses = COUNT(course_name),
@avg_score = AVG(score),
@total_score = SUM(score),
@max_score = MAX(score),
@min_score = MIN(score)
FROM scores
WHERE stu_id = @stu_id
GROUP BY name
IF @@ROWCOUNT = 0
BEGIN
PRINT '学号不存在'
RETURN
END
PRINT '姓名:' + @name + ',选课门数:' + CAST(@num_courses AS VARCHAR(10)) + ',平均分:' + CAST(@avg_score AS VARCHAR(10)) + ',总分:' + CAST(@total_score AS VARCHAR(10)) + ',最高分:' + CAST(@max_score AS VARCHAR(10)) + ',最低分:' + CAST(@min_score AS VARCHAR(10))
END
```
3)查询平均分数在x到y范围内的学生信息存储过程代码:
```
CREATE PROCEDURE P_AVG_SCORE_RANGE
@start_score FLOAT,
@end_score FLOAT
AS
BEGIN
SELECT stu_id, name, department_name, AVG(score) AS avg_score
FROM students
JOIN scores ON students.id = scores.stu_id
JOIN courses ON scores.course_id = courses.id
GROUP BY stu_id, name, department_name
HAVING AVG(score) >= @start_score AND AVG(score) <= @end_score
END
```
4)完成查询和完成操作功能的存储过程:
查询功能:
```
CREATE PROCEDURE P_QUERY
@table_name VARCHAR(20)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM ' + @table_name
EXECUTE (@sql)
END
```
操作功能:
```
CREATE PROCEDURE P_OPERATION
@table_name VARCHAR(20),
@column_name VARCHAR(20),
@old_value VARCHAR(20),
@new_value VARCHAR(20)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'UPDATE ' + @table_name + ' SET ' + @column_name + ' = ''' + @new_value + ''' WHERE ' + @column_name + ' = ''' + @old_value + ''''
EXECUTE (@sql)
END
```
以上存储过程仅供参考,具体实现需要根据实际情况进行修改。
阅读全文