首页 › MySQL › 第 7 章 › 习题 › 游标练习题

游标练习题

第 7 章 · 存储过程与函数 · 共 10 题 · 游标遍历 / 嵌套游标 / 可更新游标

🛠 练习前准备：导入教学实例数据库

本练习使用「教学实例」数据库中的 student、course、sc、grades 表。请先导入建库脚本：课程资料 → 教学实例库（复制脚本执行，或下载 教学实例库.sql 后导入）。

1. 编写存储过程proc_course_list，使用游标遍历course表，逐行输出所有课程的课程号和课程名称。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_course_list;
DELIMITER //
CREATE PROCEDURE proc_course_list()
BEGIN
    -- 声明变量
    DECLARE v_cno VARCHAR(10);
    DECLARE v_cname VARCHAR(10);
    DECLARE done INT DEFAULT 0;
    
    -- 声明游标
    DECLARE cur_course CURSOR FOR 
        SELECT Cno, Cname FROM course;
    
    -- 声明结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 打开游标
    OPEN cur_course;
    
    -- 循环读取
    course_loop: LOOP
        FETCH cur_course INTO v_cno, v_cname;
        IF done = 1 THEN
            LEAVE course_loop;
        END IF;
        
        -- 输出结果
        SELECT CONCAT('课程号：', v_cno, '，课程名：', v_cname) AS 课程信息;
    END LOOP course_loop;
    
    -- 关闭游标
    CLOSE cur_course;
END //
DELIMITER ;

-- 调用测试
CALL proc_course_list();

2. 编写存储过程proc_student_count_sex，使用游标遍历student表，统计并输出男生和女生的总人数。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_student_count_sex;
DELIMITER //
CREATE PROCEDURE proc_student_count_sex()
BEGIN
    DECLARE v_sex VARCHAR(10);
    DECLARE done INT DEFAULT 0;
    DECLARE male_count INT DEFAULT 0;
    DECLARE female_count INT DEFAULT 0;
    
    DECLARE cur_sex CURSOR FOR SELECT Ssex FROM student;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_sex;
    sex_loop: LOOP
        FETCH cur_sex INTO v_sex;
        IF done = 1 THEN
            LEAVE sex_loop;
        END IF;
        
        IF v_sex = '男' THEN
            SET male_count = male_count + 1;
        ELSE
            SET female_count = female_count + 1;
        END IF;
    END LOOP sex_loop;
    CLOSE cur_sex;
    
    SELECT male_count AS 男生人数, female_count AS 女生人数;
END //
DELIMITER ;

3. 编写存储过程proc_grade_count，使用游标遍历sc表，统计并输出不及格、及格、良好、优秀四个等级的成绩数量（参考grades表的分数范围）。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_grade_count;
DELIMITER //
CREATE PROCEDURE proc_grade_count()
BEGIN
    DECLARE v_score DECIMAL(18,1);
    DECLARE done INT DEFAULT 0;
    -- 初始化四个等级的计数器
    DECLARE excellent INT DEFAULT 0;  -- 优秀(90-100)
    DECLARE good INT DEFAULT 0;       -- 良好(75-89)
    DECLARE pass INT DEFAULT 0;       -- 及格(60-74)
    DECLARE fail INT DEFAULT 0;       -- 不及格(0-59)
    
    DECLARE cur_score CURSOR FOR SELECT score FROM sc;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_score;
    score_loop: LOOP
        FETCH cur_score INTO v_score;
        IF done = 1 THEN
            LEAVE score_loop;
        END IF;
        
        -- 根据分数判断等级
        IF v_score >= 90 THEN
            SET excellent = excellent + 1;
        ELSEIF v_score >= 75 THEN
            SET good = good + 1;
        ELSEIF v_score >= 60 THEN
            SET pass = pass + 1;
        ELSE
            SET fail = fail + 1;
        END IF;
    END LOOP score_loop;
    CLOSE cur_score;
    
    -- 输出统计结果
    SELECT 
        fail AS 不及格人数,
        pass AS 及格人数,
        good AS 良好人数,
        excellent AS 优秀人数;
END //
DELIMITER ;

-- 调用测试
CALL proc_grade_count();

4. 编写带参数的存储过程proc_student_by_class，输入班级号（如 '01'），使用游标遍历该班级的所有学生，输出他们的姓名和出生日期。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_student_by_class;
DELIMITER //
CREATE PROCEDURE proc_student_by_class(IN p_class CHAR(2))
BEGIN
    DECLARE v_name VARCHAR(10);
    DECLARE v_birth DATE;
    DECLARE done INT DEFAULT 0;
    
    -- 带参数的游标：根据输入的班级号筛选学生
    DECLARE cur_student CURSOR FOR 
        SELECT Sname, Sbirth FROM student WHERE class = p_class;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_student;
    student_loop: LOOP
        FETCH cur_student INTO v_name, v_birth;
        IF done = 1 THEN
            LEAVE student_loop;
        END IF;
        
        SELECT CONCAT('姓名：', v_name, '，出生日期：', v_birth) AS 学生信息;
    END LOOP student_loop;
    CLOSE cur_student;
END //
DELIMITER ;

-- 调用测试：查询01班学生
CALL proc_student_by_class('01');

5. 编写存储过程proc_update_score，使用可更新游标遍历sc表，将所有数学课程（课程号 '02'）的成绩加 3 分。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_update_score;
DELIMITER //
CREATE PROCEDURE proc_update_score()
BEGIN
    DECLARE v_sno VARCHAR(10);
    DECLARE v_cno VARCHAR(10);
    DECLARE v_score DECIMAL(18,1);
    DECLARE done INT DEFAULT 0;
    
    -- 声明可更新游标
    DECLARE cur_score CURSOR FOR 
        SELECT Sno, Cno, score FROM sc WHERE Cno = '02' FOR UPDATE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_score;
    score_loop: LOOP
        FETCH cur_score INTO v_sno, v_cno, v_score;
        IF done = 1 THEN
            LEAVE score_loop;
        END IF;
        
        -- 更新当前行数据
        UPDATE sc SET score = v_score + 3 
        WHERE CURRENT OF cur_score;
    END LOOP score_loop;
    CLOSE cur_score;
    
    SELECT '数学成绩已全部加3分' AS 操作结果;
END //
DELIMITER ;

6. 编写存储过程proc_delete_no_score，使用游标遍历course表，删除没有任何学生选修的课程（即sc表中没有对应记录的课程）。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_delete_no_score;
DELIMITER //
CREATE PROCEDURE proc_delete_no_score()
BEGIN
    DECLARE v_cno VARCHAR(10);
    DECLARE done INT DEFAULT 0;
    DECLARE v_count INT;
    
    -- 声明可更新游标
    DECLARE cur_course CURSOR FOR 
        SELECT Cno FROM course FOR UPDATE;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_course;
    course_loop: LOOP
        FETCH cur_course INTO v_cno;
        IF done = 1 THEN
            LEAVE course_loop;
        END IF;
        
        -- 查询该课程是否有学生选修
        SELECT COUNT(*) INTO v_count FROM sc WHERE Cno = v_cno;
        
        -- 如果没有选修记录，删除当前行
        IF v_count = 0 THEN
            DELETE FROM course WHERE CURRENT OF cur_course;
        END IF;
    END LOOP course_loop;
    CLOSE cur_course;
    
    SELECT '已删除所有无人选修的课程' AS 操作结果;
END //
DELIMITER ;

-- 调用测试（会删除课程号04的计算机课程）
CALL proc_delete_no_score();

7. 编写存储过程proc_student_total_score，使用游标遍历所有学生，计算每个学生的总分，并将结果输出（学生姓名 + 总分）。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_student_total_score;
DELIMITER //
CREATE PROCEDURE proc_student_total_score()
BEGIN
    DECLARE v_sno VARCHAR(10);
    DECLARE v_name VARCHAR(10);
    DECLARE v_total DECIMAL(18,1);
    DECLARE done INT DEFAULT 0;
    
    -- 游标查询所有学生
    DECLARE cur_student CURSOR FOR 
        SELECT Sno, Sname FROM student;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_student;
    student_loop: LOOP
        FETCH cur_student INTO v_sno, v_name;
        IF done = 1 THEN
            LEAVE student_loop;
        END IF;
        
        -- 计算该学生的总分
        SELECT IFNULL(SUM(score), 0) INTO v_total 
        FROM sc WHERE Sno = v_sno;
        
        SELECT CONCAT('姓名：', v_name, '，总分：', v_total) AS 学生总分;
    END LOOP student_loop;
    CLOSE cur_student;
END //
DELIMITER ;

-- 调用测试
CALL proc_student_total_score();

8. 编写存储过程proc_class_statistics，使用嵌套游标： 外层游标遍历所有班级 内层游标遍历每个班级的学生 统计并输出每个班级的总人数、男生人数、女生人数和平均年龄

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_class_statistics;
DELIMITER //
CREATE PROCEDURE proc_class_statistics()
BEGIN
    -- 外层游标变量：班级
    DECLARE v_class CHAR(2);
    DECLARE class_done INT DEFAULT 0;
    
    -- 内层游标变量：学生
    DECLARE v_sex VARCHAR(10);
    DECLARE v_age TINYINT UNSIGNED;
    DECLARE student_done INT DEFAULT 0;
    
    -- 统计变量
    DECLARE total_count INT;
    DECLARE male_count INT;
    DECLARE female_count INT;
    DECLARE total_age INT;
    DECLARE avg_age DECIMAL(5,2);
    
    -- 外层游标：遍历所有班级
    DECLARE cur_class CURSOR FOR 
        SELECT DISTINCT class FROM student WHERE class IS NOT NULL;
    
    -- 外层结束处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET class_done = 1;
    
    OPEN cur_class;
    class_loop: LOOP
        FETCH cur_class INTO v_class;
        IF class_done = 1 THEN
            LEAVE class_loop;
        END IF;
        
        -- 重置统计变量
        SET total_count = 0;
        SET male_count = 0;
        SET female_count = 0;
        SET total_age = 0;
        
        -- 内层游标：遍历当前班级的所有学生
        BEGIN
            DECLARE cur_student CURSOR FOR 
                SELECT Ssex, sage FROM student WHERE class = v_class;
            
            -- 内层结束处理程序（注意作用域）
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET student_done = 1;
            
            OPEN cur_student;
            student_loop: LOOP
                FETCH cur_student INTO v_sex, v_age;
                IF student_done = 1 THEN
                    LEAVE student_loop;
                END IF;
                
                -- 统计数据
                SET total_count = total_count + 1;
                SET total_age = total_age + v_age;
                IF v_sex = '男' THEN
                    SET male_count = male_count + 1;
                ELSE
                    SET female_count = female_count + 1;
                END IF;
            END LOOP student_loop;
            CLOSE cur_student;
            
            -- 重置内层结束标志
            SET student_done = 0;
        END;
        
        -- 计算平均年龄
        SET avg_age = total_age / total_count;
        
        -- 输出当前班级统计结果
        SELECT 
            v_class AS 班级号,
            total_count AS 总人数,
            male_count AS 男生人数,
            female_count AS 女生人数,
            avg_age AS 平均年龄;
    END LOOP class_loop;
    CLOSE cur_class;
END //
DELIMITER ;

-- 调用测试
CALL proc_class_statistics();

9. 编写存储过程proc_course_grade_statistics，使用游标遍历每门课程，统计该课程各个成绩等级（优秀 / 良好 / 及格 / 不及格）的人数，并输出结果。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_course_grade_statistics;
DELIMITER //
CREATE PROCEDURE proc_course_grade_statistics()
BEGIN
    DECLARE v_cno VARCHAR(10);
    DECLARE v_cname VARCHAR(10);
    DECLARE course_done INT DEFAULT 0;
    
    DECLARE v_score DECIMAL(18,1);
    DECLARE score_done INT DEFAULT 0;
    
    DECLARE excellent INT;
    DECLARE good INT;
    DECLARE pass INT;
    DECLARE fail INT;
    
    -- 外层游标：遍历所有课程
    DECLARE cur_course CURSOR FOR 
        SELECT Cno, Cname FROM course;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET course_done = 1;
    
    OPEN cur_course;
    course_loop: LOOP
        FETCH cur_course INTO v_cno, v_cname;
        IF course_done = 1 THEN
            LEAVE course_loop;
        END IF;
        
        -- 重置计数器
        SET excellent = 0;
        SET good = 0;
        SET pass = 0;
        SET fail = 0;
        
        -- 内层游标：遍历当前课程的所有成绩
        BEGIN
            DECLARE cur_score CURSOR FOR 
                SELECT score FROM sc WHERE Cno = v_cno;
            
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET score_done = 1;
            
            OPEN cur_score;
            score_loop: LOOP
                FETCH cur_score INTO v_score;
                IF score_done = 1 THEN
                    LEAVE score_loop;
                END IF;
                
                IF v_score >= 90 THEN
                    SET excellent = excellent + 1;
                ELSEIF v_score >= 75 THEN
                    SET good = good + 1;
                ELSEIF v_score >= 60 THEN
                    SET pass = pass + 1;
                ELSE
                    SET fail = fail + 1;
                END IF;
            END LOOP score_loop;
            CLOSE cur_score;
            
            SET score_done = 0;
        END;
        
        -- 输出当前课程统计结果
        SELECT 
            v_cname AS 课程名称,
            fail AS 不及格人数,
            pass AS 及格人数,
            good AS 良好人数,
            excellent AS 优秀人数;
    END LOOP course_loop;
    CLOSE cur_course;
END //
DELIMITER ;

-- 调用测试
CALL proc_course_grade_statistics();

10. 编写存储过程proc_top_student，使用游标遍历所有学生，计算每个学生的平均分，找出平均分最高的学生并输出其姓名和平均分。

查看参考答案（可复制）
sql
DROP PROCEDURE IF EXISTS proc_top_student;
DELIMITER //
CREATE PROCEDURE proc_top_student()
BEGIN
    DECLARE v_sno VARCHAR(10);
    DECLARE v_name VARCHAR(10);
    DECLARE v_avg DECIMAL(5,2);
    DECLARE done INT DEFAULT 0;
    
    -- 记录最高分和对应学生
    DECLARE max_avg DECIMAL(5,2) DEFAULT 0;
    DECLARE top_name VARCHAR(10);
    
    DECLARE cur_student CURSOR FOR 
        SELECT Sno, Sname FROM student;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    OPEN cur_student;
    student_loop: LOOP
        FETCH cur_student INTO v_sno, v_name;
        IF done = 1 THEN
            LEAVE student_loop;
        END IF;
        
        -- 计算该学生的平均分
        SELECT IFNULL(AVG(score), 0) INTO v_avg 
        FROM sc WHERE Sno = v_sno;
        
        -- 更新最高分记录
        IF v_avg > max_avg THEN
            SET max_avg = v_avg;
            SET top_name = v_name;
        END IF;
    END LOOP student_loop;
    CLOSE cur_student;
    
    -- 输出结果
    SELECT 
        top_name AS 最高分学生姓名,
        max_avg AS 平均分;
END //
DELIMITER ;

-- 调用测试（结果应该是赵雷，平均分89.67）
CALL proc_top_student();
←
上一个习题总览