6.3 【案例讲解】存储过程

【题目 1】
创建无参存储过程proc1，查询所有学生的基本信息，显示字段：学号、姓名、性别、年龄、班级、籍贯。
-- 修改语句结束符

sql

DELIMITER //
-- 创建存储过程
CREATE PROCEDURE proc1()
BEGIN
    -- 查询所有学生基础信息
    SELECT 
        Sno AS 学号,
        Sname AS 姓名,
        Ssex AS 性别,
        sage AS 年龄,
        class AS 班级,
        sbirthplace AS 籍贯
    FROM student;
END //
-- 恢复默认结束符
DELIMITER ;

-- 调用存储过程

sql

CALL proc1();

-- 重写时先删除存储过程
-- DROP PROCEDURE IF EXISTS proc1;

【题目 2】
创建无参存储过程proc2，查询所有课程的完整信息，显示字段：课程号、课程名、授课教师姓名。

sql

DELIMITER //
CREATE PROCEDURE proc2()
BEGIN
    -- 课程表左联教师表，兼容无授课教师的课程
    SELECT 
        c.Cno AS 课程号,
        c.Cname AS 课程名,
        IFNULL(t.Tname,'无') AS 授课教师
    FROM course c
    LEFT JOIN teacher t ON c.Tno = t.Tno;
END //
DELIMITER ;

-- 调用

sql

CALL proc2();

【题目 3】
创建带输入参数的存储过程proc3，输入参数为学生学号in_sno，查询该学生的所有考试成绩，显示字段：课程名、考试分数。

sql

DELIMITER //
CREATE PROCEDURE proc3(IN in_sno VARCHAR(10))
BEGIN
    -- 成绩表关联课程表，按学号筛选
    SELECT 
        c.Cname AS 课程名,
        sc.score AS 考试分数
    FROM sc
    LEFT JOIN course c ON sc.Cno = c.Cno
    WHERE sc.Sno = in_sno;
END //
DELIMITER ;

-- 调用示例：查询学号001学生的成绩

sql

CALL proc3('001');

【题目 4】
创建带输入参数的存储过程proc4，输入参数为课程号in_cno，查询并显示该课程的考试平均分，结果保留 2 位小数。

sql

DELIMITER //
CREATE PROCEDURE proc4(IN in_cno VARCHAR(10))
BEGIN
    -- 按课程号计算平均分
    SELECT 
        c.Cname AS 课程名,
        ROUND(AVG(sc.score),2) AS 课程平均分
    FROM sc
    LEFT JOIN course c ON sc.Cno = c.Cno
    WHERE sc.Cno = in_cno
    GROUP BY sc.Cno;
END //
DELIMITER ;

-- 调用示例：查询01号课程的平均分

sql

CALL proc4('01');

【题目 5】
创建带输入参数的存储过程proc5，输入参数为班级号in_class，查询该班级的所有学生名单，显示字段：学号、姓名、性别、年龄。

sql

DELIMITER //
CREATE PROCEDURE proc5(IN in_class CHAR(2))
BEGIN
    -- 按班级号筛选学生
    SELECT 
        Sno AS 学号,
        Sname AS 姓名,
        Ssex AS 性别,
        sage AS 年龄
    FROM student
    WHERE class = in_class;
END //
DELIMITER ;

-- 调用示例：查询03班的学生

sql

CALL proc5('03');

【题目 6】
创建带输入和输出参数的存储过程proc6，输入参数为学生学号in_sno，输出参数为该学生的考试总科目数out_count。

sql

DELIMITER //
CREATE PROCEDURE proc6(
    IN in_sno VARCHAR(10),
    OUT out_count INT
)
BEGIN
    -- 统计学生参考科目数，赋值给输出参数
    SELECT COUNT(*) INTO out_count
    FROM sc
    WHERE Sno = in_sno;
END //
DELIMITER ;

-- 调用示例：查询学号001的参考科目数

sql

CALL proc6('001', @count);

-- 查看输出结果

sql

SELECT @count AS 总科目数;

【题目 7】
创建带多个输入参数的存储过程proc7，输入参数为课程号in_cno、分数阈值in_score，查询该课程中分数大于等于阈值的学生信息，显示字段：学号、姓名、考试分数。

sql

DELIMITER //
CREATE PROCEDURE proc7(
    IN in_cno VARCHAR(10),
    IN in_score DECIMAL(18,1)
)
BEGIN
    -- 多表联查，筛选达标学生
    SELECT 
        s.Sno AS 学号,
        s.Sname AS 姓名,
        sc.score AS 考试分数
    FROM sc
    LEFT JOIN student s ON sc.Sno = s.Sno
    WHERE sc.Cno = in_cno AND sc.score >= in_score;
END //
DELIMITER ;

-- 调用示例：查询01号课程分数≥80分的学生

sql

CALL proc7('01', 80);

【题目 8】
创建存储过程proc8，输入参数为学生学号in_sno、课程号in_cno，根据grades表的等级规则，判断并输出该学生该课程的成绩等级（优秀 / 良好 / 及格 / 不及格）。

sql

DELIMITER //
CREATE PROCEDURE proc8(
    IN in_sno VARCHAR(10),
    IN in_cno VARCHAR(10)
)
BEGIN
    -- 定义变量存储成绩和等级
    DECLARE stu_score DECIMAL(18,1);
    DECLARE score_level VARCHAR(10);

    -- 查询学生对应课程的成绩
    SELECT score INTO stu_score
    FROM sc
    WHERE Sno = in_sno AND Cno = in_cno;

    -- 根据分数区间判断等级
    IF stu_score >=90 AND stu_score <=100 THEN
        SET score_level = '优秀';
    ELSEIF stu_score >=75 AND stu_score <=89 THEN
        SET score_level = '良好';
    ELSEIF stu_score >=60 AND stu_score <=74 THEN
        SET score_level = '及格';
    ELSE
        SET score_level = '不及格';
    END IF;

    -- 输出结果
    SELECT 
        in_sno AS 学号,
        in_cno AS 课程号,
        stu_score AS 考试分数,
        score_level AS 成绩等级;
END //
DELIMITER ;

-- 调用示例：查询001号学生02号课程的成绩等级

sql

CALL proc8('001','02');

【题目 9】
创建存储过程proc9，输入参数为课程号in_cno，输出参数为该课程的最高分out_max、最低分out_min、平均分out_avg（平均分保留 2 位小数）。

sql

DELIMITER //
CREATE PROCEDURE proc9(
    IN in_cno VARCHAR(10),
    OUT out_max DECIMAL(18,1),
    OUT out_min DECIMAL(18,1),
    OUT out_avg DECIMAL(18,2)
)
BEGIN
    -- 计算成绩统计指标，赋值给输出参数
    SELECT 
        MAX(score),
        MIN(score),
        ROUND(AVG(score),2)
    INTO out_max, out_min, out_avg
    FROM sc
    WHERE Cno = in_cno;
END //
DELIMITER ;

-- 调用示例：查询01号课程的成绩统计

sql

CALL proc9('01', @max_score, @min_score, @avg_score);

-- 查看结果

sql

SELECT 
    @max_score AS 最高分,
    @min_score AS 最低分,
    @avg_score AS 平均分;

【题目 10】
创建存储过程proc10，输入参数为教师姓名in_tname，查询该教师所授课程的所有学生成绩明细，显示字段：教师姓名、课程名、学生学号、学生姓名、考试分数。

sql

DELIMITER //
CREATE PROCEDURE proc10(IN in_tname VARCHAR(10))
BEGIN
    -- 四表联查：教师-课程-成绩-学生
    SELECT 
        t.Tname AS 教师姓名,
        c.Cname AS 课程名,
        s.Sno AS 学生学号,
        s.Sname AS 学生姓名,
        sc.score AS 考试分数
    FROM teacher t
    LEFT JOIN course c ON t.Tno = c.Tno
    LEFT JOIN sc ON c.Cno = sc.Cno
    LEFT JOIN student s ON sc.Sno = s.Sno
    WHERE t.Tname = in_tname;
END //
DELIMITER ;

-- 调用示例：查询李老师的授课成绩明细

sql

CALL proc10('李老师');

【题目 11】
创建存储过程proc11，输入参数为学生学号in_sno，判断该学生是否存在不及格科目，输出对应的提示信息（有不及格科目 / 无不及格科目 / 未查询到该学生成绩）。

sql

DELIMITER //
CREATE PROCEDURE proc11(IN in_sno VARCHAR(10))
BEGIN
    -- 定义变量存储不及格科目数
    DECLARE fail_count INT;

    -- 统计不及格科目数
    SELECT COUNT(*) INTO fail_count
    FROM sc
    WHERE Sno = in_sno AND score < 60;

    -- 多条件判断输出
    IF NOT EXISTS (SELECT * FROM sc WHERE Sno = in_sno) THEN
        SELECT '未查询到该学生成绩' AS 提示信息;
    ELSEIF fail_count > 0 THEN
        SELECT CONCAT('该学生有',fail_count,'门不及格科目') AS 提示信息;
    ELSE
        SELECT '该学生无不及格科目' AS 提示信息;
    END IF;
END //
DELIMITER ;

-- 调用示例1：查询004号学生

sql

CALL 11('004');

-- 调用示例2：查询001号学生

sql

CALL 11('001');

【题目 12】
创建存储过程proc12，实现新增学生信息功能，输入参数为：学号、姓名、性别、出生日期、年龄、籍贯、班级，完成学生表的插入操作，执行后输出插入成功的提示。

sql

DELIMITER //
CREATE PROCEDURE proc12(
    IN in_sno VARCHAR(10),
    IN in_sname VARCHAR(10),
    IN in_ssex VARCHAR(10),
    IN in_sbirth DATE,
    IN in_sage TINYINT,
    IN in_sbirthplace VARCHAR(20),
    IN in_class CHAR(2)
)
BEGIN
    -- 插入学生数据
    INSERT INTO student(Sno,Sname,Ssex,Sbirth,sage,sbirthplace,class)
    VALUES (in_sno,in_sname,in_ssex,in_sbirth,in_sage,in_sbirthplace,in_class);

    -- 输出执行结果
    SELECT '学生信息插入成功' AS 执行结果;
END //
DELIMITER ;

-- 调用示例：新增学号021的学生

sql

CALL proc12('021','张三','男','2008-05-01',16,'深圳','02');

-- 验证插入结果

sql

SELECT * FROM student WHERE Sno = '021';

【题目 13】
创建存储过程proc13，输入参数为班级号in_class，输出参数为该班级总人数out_total、男生人数out_male、女生人数out_female。

sql

DELIMITER //
CREATE PROCEDURE proc13(
    IN in_class CHAR(2),
    OUT out_total INT,
    OUT out_male INT,
    OUT out_female INT
)
BEGIN
    -- 统计总人数
    SELECT COUNT(*) INTO out_total
    FROM student
    WHERE class = in_class;

    -- 统计男生人数
    SELECT COUNT(*) INTO out_male
    FROM student
    WHERE class = in_class AND Ssex = '男';

    -- 统计女生人数
    SELECT COUNT(*) INTO out_female
    FROM student
    WHERE class = in_class AND Ssex = '女';
END //
DELIMITER ;

-- 调用示例：统计03班的性别分布

sql

CALL proc13('03', @total, @male, @female);

-- 查看结果

sql

SELECT 
    @total AS 班级总人数,
    @male AS 男生人数,
    @female AS 女生人数;

【题目 14】
创建带循环结构的存储过程proc14，输入参数为正整数in_n，输出参数为 1 到该整数的累加和out_sum，使用 WHILE 循环实现。

sql

DELIMITER //
CREATE PROCEDURE proc14(
    IN in_n INT,
    OUT out_sum INT
)
BEGIN
    -- 定义循环变量和累加变量
    DECLARE i INT DEFAULT 1;
    DECLARE sum_total INT DEFAULT 0;

    -- WHILE循环实现累加
    WHILE i <= in_n DO
        SET sum_total = sum_total + i;
        SET i = i + 1;
    END WHILE;

    -- 赋值给输出参数
    SET out_sum = sum_total;
END //
DELIMITER ;

-- 调用示例：计算1到100的累加和

sql

CALL proc14(100, @sum_result);

-- 查看结果

sql

SELECT @sum_result AS 累加和结果;

【题目 15】
创建存储过程proc15，实现成绩批量更新：输入参数为课程号in_cno、加分值in_add_score，给该课程所有学生的成绩加上指定分数，要求加分后最高分不超过 100 分，执行后输出更新的行数。

sql

DELIMITER //
CREATE PROCEDURE proc15(
    IN in_cno VARCHAR(10),
    IN in_add_score INT
)
BEGIN
    -- 批量更新成绩，LEAST函数确保不超过100分
    UPDATE sc
    SET score = LEAST(score + in_add_score, 100)
    WHERE Cno = in_cno;

    -- 输出更新的行数
    SELECT ROW_COUNT() AS 成功更新的记录行数;
END //
DELIMITER ;

-- 调用示例：给01号课程所有学生加5分

sql

CALL proc15('01',5);

-- 验证结果

sql

SELECT * FROM sc WHERE Cno = '01';

【题目 16】
创建带游标的存储过程proc16，遍历所有有成绩的学生，统计每个学生的总成绩、平均成绩，将结果插入到student_score_stat统计表中。
-- 第一步：创建学生成绩统计表

sql

DROP TABLE IF EXISTS student_score_stat;
CREATE TABLE student_score_stat(
    Sno VARCHAR(10) NOT NULL COMMENT '学号',
    Sname VARCHAR(10) COMMENT '姓名',
    total_score DECIMAL(18,1) COMMENT '总成绩',
    avg_score DECIMAL(18,2) COMMENT '平均成绩',
    PRIMARY KEY (Sno)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 第二步：创建带游标的存储过程

sql

DELIMITER //
CREATE PROCEDURE proc16()
BEGIN
    -- 定义变量存储游标数据
    DECLARE stu_sno VARCHAR(10);
    DECLARE stu_sname VARCHAR(10);
    DECLARE stu_total DECIMAL(18,1);
    DECLARE stu_avg DECIMAL(18,2);
    -- 定义游标结束标志
    DECLARE done INT DEFAULT 0;

    -- 定义游标：查询学生成绩统计数据
    DECLARE score_cursor CURSOR FOR
        SELECT 
            s.Sno,
            s.Sname,
            SUM(sc.score),
            ROUND(AVG(sc.score),2)
        FROM student s
        INNER JOIN sc ON s.Sno = sc.Sno
        GROUP BY s.Sno;

    -- 定义游标结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- 清空原有数据
    TRUNCATE TABLE student_score_stat;

    -- 打开游标
    OPEN score_cursor;

    -- 循环读取游标数据
    read_loop:LOOP
        FETCH score_cursor INTO stu_sno, stu_sname, stu_total, stu_avg;
        -- 结束循环判断
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        -- 插入统计数据
        INSERT INTO student_score_stat(Sno,Sname,total_score,avg_score)
        VALUES (stu_sno,stu_sname,stu_total,stu_avg);
    END LOOP read_loop;

    -- 关闭游标
    CLOSE score_cursor;

    -- 输出结果
    SELECT '学生成绩统计完成' AS 执行结果;
END //
DELIMITER ;

-- 调用存储过程

sql

CALL proc16();

-- 查看统计结果

sql

SELECT * FROM student_score_stat;

【题目 17】
创建带事务和异常处理的存储过程proc17，实现学生转班功能：输入学生学号in_sno、新班级号in_new_class，更新学生班级，同时向operate_log表记录操作日志；执行出错则事务回滚，输出错误提示。
-- 第一步：创建操作日志表

sql

DROP TABLE IF EXISTS operate_log;
CREATE TABLE operate_log(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    operate_type VARCHAR(20) COMMENT '操作类型',
    operate_content VARCHAR(200) COMMENT '操作内容',
    operate_time DATETIME DEFAULT NOW() COMMENT '操作时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 第二步：创建带事务和异常处理的存储过程

sql

DELIMITER //
CREATE PROCEDURE proc17(
    IN in_sno VARCHAR(10),
    IN in_new_class CHAR(2)
)
BEGIN
    -- 定义变量存储学生原班级和姓名
    DECLARE old_class CHAR(2);
    DECLARE stu_name VARCHAR(10);
    -- 定义异常处理：出错回滚并提示
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '操作执行失败，已回滚' AS 执行结果;
    END;

    -- 查询学生原信息
    SELECT class, Sname INTO old_class, stu_name
    FROM student
    WHERE Sno = in_sno;

    -- 开启事务
    START TRANSACTION;

    -- 更新学生班级
    UPDATE student
    SET class = in_new_class
    WHERE Sno = in_sno;

    -- 插入操作日志
    INSERT INTO operate_log(operate_type,operate_content)
    VALUES ('学生转班',CONCAT('学生',stu_name,'（学号',in_sno,'）从',old_class,'班转入',in_new_class,'班'));

    -- 提交事务
    COMMIT;

    -- 输出成功提示
    SELECT '学生转班操作成功' AS 执行结果;
END //
DELIMITER ;

-- 调用示例：将001号学生从01班转入02班

sql

CALL proc17('001','02');

-- 验证学生班级更新

sql

SELECT * FROM student WHERE Sno = '001';

-- 查看操作日志

sql

SELECT * FROM operate_log;

【题目 18】
创建存储过程proc18，输入参数为分数下限in_low、分数上限in_high，实现两个功能：1. 查询该分数区间内的所有学生成绩明细；2. 统计该区间内的成绩记录总条数。

sql

DELIMITER //
CREATE PROCEDURE proc_get_score_range(
    IN in_low DECIMAL(18,1),
    IN in_high DECIMAL(18,1)
)
BEGIN
    -- 1. 查询成绩明细
    SELECT 
        s.Sno AS 学号,
        s.Sname AS 姓名,
        c.Cname AS 课程名,
        sc.score AS 考试分数
    FROM sc
    LEFT JOIN student s ON sc.Sno = s.Sno
    LEFT JOIN course c ON sc.Cno = c.Cno
    WHERE sc.score BETWEEN in_low AND in_high;

    -- 2. 统计记录总数
    SELECT 
        CONCAT(in_low,'-',in_high,'分区间') AS 分数区间,
        COUNT(*) AS 成绩记录总条数
    FROM sc
    WHERE score BETWEEN in_low AND in_high;
END //
DELIMITER ;

-- 调用示例：查询60-80分区间的成绩

sql

CALL proc18(60,80);

【题目 19】
创建存储过程proc19，使用 CASE 多分支语句，实现部门表dept的增删改通用操作：输入操作类型in_operate_type（1 = 新增、2 = 修改、3 = 删除）、部门 IDin_dept_id、部门名称in_dept_name，执行对应逻辑并输出提示。

sql

DELIMITER //
CREATE PROCEDURE proc_dept_operate(
    IN in_operate_type TINYINT,
    IN in_dept_id INT,
    IN in_dept_name VARCHAR(50)
)
BEGIN
    -- 多分支判断操作类型
    CASE in_operate_type
        WHEN 1 THEN
            -- 新增部门
            INSERT INTO dept(id,name) VALUES (in_dept_id,in_dept_name);
            SELECT '部门新增成功' AS 执行结果;
        WHEN 2 THEN
            -- 修改部门
            UPDATE dept SET name = in_dept_name WHERE id = in_dept_id;
            SELECT '部门修改成功' AS 执行结果;
        WHEN 3 THEN
            -- 删除部门
            DELETE FROM dept WHERE id = in_dept_id;
            SELECT '部门删除成功' AS 执行结果;
        ELSE
            -- 非法操作类型
            SELECT '操作类型错误，仅支持1=新增、2=修改、3=删除' AS 错误提示;
    END CASE;
END //
DELIMITER ;

-- 调用示例1：新增部门，ID=7，名称=后勤部

sql

CALL proc19(1,7,'后勤部');

-- 调用示例2：修改ID=7的部门名称为行政后勤部

sql

CALL proc19(2,7,'行政后勤部');

-- 调用示例3：删除ID=7的部门

sql

CALL proc_dept_operate(3,7,'');

【题目 20】综合实训题
创建存储过程proc20，输入参数为入职年份in_year、入职月份in_month，统计该年月入职的教师的授课情况，显示字段：教师姓名、入职时间、所授课程名、选课学生人数、课程平均分；无授课的教师也需显示，无数据字段显示为 “无”。

sql

DELIMITER //
CREATE PROCEDURE proc20(
    IN in_year INT,
    IN in_month INT
)
BEGIN
    SELECT 
        t.Tname AS 教师姓名,
        t.entrydate AS 入职时间,
        IFNULL(c.Cname,'无') AS 所授课程名,
        IFNULL(COUNT(sc.Sno),'无') AS 选课学生人数,
        IFNULL(ROUND(AVG(sc.score),2),'无') AS 课程平均分
    FROM teacher t
    LEFT JOIN course c ON t.Tno = c.Tno
    LEFT JOIN sc ON c.Cno = sc.Cno
    WHERE YEAR(t.entrydate) = in_year AND MONTH(t.entrydate) = in_month
    GROUP BY t.Tno, c.Cno;
END //
DELIMITER ;

-- 调用示例：查询2023年7月入职的教师授课情况

sql

CALL proc20(2023,7);

←

上一节 6.2 存储过程

下一节 6.4 存储函数

→