CREATE PROCEDURE p_rank(IN snum char(12), OUT avg_grade FLOAT, OUT grade_level VARCHAR(20))
时间: 2024-05-16 13:15:56 浏览: 9
这是一个 MySQL 存储过程的语法,该存储过程接受一个输入参数 snum,表示学号,同时返回两个输出参数 avg_grade 和 grade_level。
具体实现如下:
```mysql
CREATE PROCEDURE p_rank(IN snum char(12), OUT avg_grade FLOAT, OUT grade_level VARCHAR(20))
BEGIN
DECLARE total_grade FLOAT;
DECLARE count_grade INT;
DECLARE rank FLOAT;
SELECT SUM(grade), COUNT(*) INTO total_grade, count_grade FROM scores WHERE s_id = snum;
SET avg_grade = total_grade / count_grade;
IF avg_grade >= 90 THEN
SET grade_level = '优秀';
ELSEIF avg_grade >= 80 THEN
SET grade_level = '良好';
ELSEIF avg_grade >= 70 THEN
SET grade_level = '中等';
ELSEIF avg_grade >= 60 THEN
SET grade_level = '及格';
ELSE
SET grade_level = '不及格';
END IF;
SELECT COUNT(*) INTO rank FROM (SELECT AVG(grade) AS avg_score FROM scores GROUP BY s_id) AS t WHERE avg_score > avg_grade;
SET rank = rank + 1;
SELECT CONCAT('你的平均成绩为:', avg_grade, ',属于', grade_level, '水平,班级排名第', rank, '名') AS result;
END;
```
该存储过程首先查询指定学号的成绩总分和总数,并计算出平均成绩。然后根据平均成绩的分数段给出对应的等级,最后查询出班级排名并返回结果字符串。
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![cmd](https://img-home.csdnimg.cn/images/20210720083646.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://img-home.csdnimg.cn/images/20210720083646.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)