📝 存储过程与函数 · 习题课

📚 第15章配套练习 💻 含代码实操 🎯 期末重点
🎯 本节练习目标
  • 掌握 DELIMITER、CALL、IN/OUT 等核心概念
  • 能 读懂 一段存储过程代码，预测执行结果
  • 能根据需求 写出 存储过程或函数
  • 会综合应用 IN+OUT 参数 + 流程控制

📌 答题前先看：写存储过程的标准模板

🎯 标准模板

DELIMITER $$
CREATE PROCEDURE 名(参数列表)
BEGIN
    -- 这里写 SQL
END $$
DELIMITER ;

⚠️ 三个最容易丢分的地方

① 忘了 DELIMITER —— 写完没改回 ; 也算错

② OUT 参数调用时传常量 —— 必须传 @变量

③ 函数忘加 DETERMINISTIC —— MySQL 会报错

📝 第一部分 · 概念巩固

先用几道选择题把核心概念过一遍。

练习 1.1 ⭐ 基础 DELIMITER

在写存储过程前为什么要使用 DELIMITER $$？

📖 查看答案

✅ 答案：B

MySQL 默认遇到 ; 就执行一次。但存储过程里有多条以 ; 结尾的语句，会被中途截断。

所以用 DELIMITER $$ 临时把结束符改成 $$，让 MySQL 看到 ; 不再执行，看到 $$ 才执行。

用完后记得 DELIMITER ; 改回来。

练习 1.2 ⭐⭐ 必考 三种参数

关于存储过程的三种参数，下列说法 正确 的是？

📖 查看答案

✅ 答案：C

分析：

A 错：IN 是输入（外→过程），不是带回结果

B 错：OUT 是输出（过程→外），不是传入

C 对：INOUT 双向，传入一个值，过程修改后再带回

D 错：函数只能用 IN 一种参数，不支持 OUT/INOUT

口诀：IN = 你给我值，OUT = 我给你结果，INOUT = 双向

练习 1.3 ⭐⭐ 必考 过程 vs 函数

下列关于存储过程和函数的对比，错误 的是？

📖 查看答案

✅ 错误的是：C

正确说法：函数可以嵌入 SELECT，存储过程不行。

例如：

✅ SELECT my_func(80, 90); -- 函数可以这样用
❌ SELECT my_proc(80, 90); -- 错！存储过程必须用 CALL

口诀："过程做事用 CALL，函数算值用 SELECT"。

练习 1.4 ⭐⭐ 中等 变量类型

下面四个变量声明，哪个是局部变量（在存储过程内部使用）？

📖 查看答案

✅ 答案：B

三种变量速记：

① @变量 = 用户会话变量（A 选项），不需要 DECLARE

② 变量（不加 @） = 局部变量（B 选项），必须 DECLARE

③ @@变量 = 系统变量（C 选项），不能赋值

D 错：DECLARE 后面变量名不能加 @，否则报错。

练习 1.5 ⭐⭐ 中等 优点辨析

下列 不是 存储过程优点的是？

📖 查看答案

✅ 错的是：C

存储过程是存在数据库里的（名字就叫"存储过程"），会占用空间。

存储过程的 5 大优点：

① 可重复调用 ② 执行快 ③ 减少网络流量 ④ 提高安全性 ⑤ 支持流程控制

📖 第二部分 · 读代码（预测结果）必考

本部分给一段代码，不要急着运行，先动脑分析执行流程，再对照答案。

练习 2.1 📖 读代码 IF 流程控制

阅读下面的存储过程，分析 调用 CALL grade_level(85) 时输出什么？

SQL
DELIMITER $$
CREATE PROCEDURE grade_level(IN score INT)
BEGIN
    IF score >= 90 THEN
        SELECT '优秀' AS 等级;
    ELSEIF score >= 75 THEN
        SELECT '良好' AS 等级;
    ELSEIF score >= 60 THEN
        SELECT '及格' AS 等级;
    ELSE
        SELECT '不及格' AS 等级;
    END IF;
END $$
DELIMITER ;
📖 查看答案

调用 CALL grade_level(85) 时：

分析过程

① score 接收到 85

② 检查 85 >= 90？不成立

③ 检查 85 >= 75？成立！ → 输出"良好"

④ 后面的 ELSEIF / ELSE 不再判断（IF 走到第一个成立的分支就跳出）

▶ 输出结果 等级 良好
💡 知识点

IF...ELSEIF...ELSE 是从上到下逐个判断，找到第一个为真的分支就执行，跳过其他。

练习 2.2 📖 读代码 WHILE 循环

阅读下面的存储过程，调用 CALL count_to(5) 时变量 total 最终是多少？

SQL
DELIMITER $$
CREATE PROCEDURE count_to(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;
    
    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
    
    SELECT total AS 累加结果;
END $$
DELIMITER ;
📖 查看答案

调用 CALL count_to(5)：

逐轮分析

初始：i=1, total=0

第 1 轮：i=1 ≤ 5，total = 0+1 = 1，i 变成 2

第 2 轮：i=2 ≤ 5，total = 1+2 = 3，i 变成 3

第 3 轮：i=3 ≤ 5，total = 3+3 = 6，i 变成 4

第 4 轮：i=4 ≤ 5，total = 6+4 = 10，i 变成 5

第 5 轮：i=5 ≤ 5，total = 10+5 = 15，i 变成 6

第 6 轮：i=6 > 5，跳出循环

▶ 输出结果 累加结果 15
💡 知识点

这就是计算 1+2+3+4+5 = 15。WHILE 循环只要条件成立就一直转。

练习 2.3 📖 读代码 OUT 参数 · 必考

阅读下面的代码，最后 SELECT @result 输出什么？

SQL
DELIMITER $$
CREATE PROCEDURE multiply(IN a INT, IN b INT, OUT c INT)
BEGIN
    SET c = a * b;
END $$
DELIMITER ;

CALL multiply(6, 7, @result);
SELECT @result;
📖 查看答案

分析过程：

参数对应

① 实参 6 → 形参 a（IN，输入）

② 实参 7 → 形参 b（IN，输入）

③ 实参 @result → 形参 c（OUT，输出）

执行

SET c = a * b → c = 6 × 7 = 42

因为 c 是 OUT 参数，所以 c 的值会"带回"给 @result

所以 @result = 42

▶ 输出结果 @result 42
⚠️ 关键点

OUT 参数调用时 必须传 @ 变量（这里是 @result），不能传常量。

因为 OUT 参数要"装"返回值，常量不能存值。

💻 第三部分 · 写代码必考

本部分要求 根据需求自己写存储过程或函数。先在纸上画出框架，再对照答案。

练习 3.1 💻 写代码 无参存储过程

写一个名为 show_all_students 的存储过程，无参数，查询学生表 s 的所有学生信息，并演示如何调用。

📖 查看答案

这是最简单的"无参存储过程"，按标准模板写即可：

SQL · 标准答案
DELIMITER $$

CREATE PROCEDURE show_all_students()
BEGIN
    SELECT * FROM s;
END $$

DELIMITER ;

-- 调用：
CALL show_all_students();
💡 注意点

① 没有参数也要写 ()，不能省略

② BEGIN…END 之间放 SQL 语句

③ 调用时也要写 ()：CALL show_all_students() 或 CALL show_all_students;

练习 3.2 💻 写代码 带 IN 参数

写一个存储过程 find_student_by_dept，传入一个系别名称，查询该系所有学生的学号和姓名。然后演示调用"信息学院"。

📖 查看答案
SQL · 标准答案
DELIMITER $$

CREATE PROCEDURE find_student_by_dept(IN dept_name VARCHAR(20))
BEGIN
    SELECT sno, sn
    FROM s
    WHERE dept = dept_name;
END $$

DELIMITER ;

-- 调用：查询信息学院的学生
CALL find_student_by_dept('信息学院');
💡 知识点

① IN dept_name VARCHAR(20)：参数名 dept_name，类型 VARCHAR(20)

② 在 SQL 里把 dept_name 当作普通值用

③ 调用时直接传字符串：CALL find_student_by_dept('信息学院')

练习 3.3 💻 写代码 带 OUT 参数

写一个存储过程 get_total_count，没有输入参数，把学生表的总人数通过 OUT 参数带出来。然后演示如何调用并查看结果。

📖 查看答案
SQL · 标准答案
DELIMITER $$

CREATE PROCEDURE get_total_count(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM s;
END $$

DELIMITER ;

-- 调用步骤：
-- 1. 调用时传一个 @ 变量来"接住"结果
CALL get_total_count(@n);

-- 2. 查看结果
SELECT @n;
⚠️ 三个易错点

① 调用时 必须传 @ 变量（如 @n），不能传常量

② 用 SELECT ... INTO total 把查询结果赋给 OUT 参数

③ 调用结束后用 SELECT @n 才能看到结果

练习 3.4 💻 写代码 自定义函数

写一个函数 circle_area，传入半径，返回圆的面积（保留 2 位小数）。然后演示如何调用。

提示：圆面积 = π × r²，π 用 3.14。

📖 查看答案
SQL · 标准答案
DELIMITER $$

CREATE FUNCTION circle_area(r DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN 3.14 * r * r;
END $$

DELIMITER ;

-- 调用：直接像普通函数那样用
SELECT circle_area(5);    -- 结果：78.50
⚠️ 函数易错点

① 用 CREATE FUNCTION 不是 CREATE PROCEDURE

② 必须有 RETURNS 类型（注意是 RETURNS 不是 RETURN）

③ 必须加 DETERMINISTIC，不加 MySQL 报错

④ 必须有 RETURN 值（注意这里是 RETURN）

⑤ 调用用 SELECT，不是 CALL

💡 RETURNS vs RETURN 别搞混

RETURNS 类型（带 S，在函数声明处）= 说明返回什么类型

RETURN 值;（不带 S，在函数体里）= 实际返回的值

🎯 第四部分 · 综合大题期末重点

本部分把整章知识揉到一起，是 必考综合大题。

综合大题 1 ⭐⭐⭐ 期末重点 IN + OUT 参数

写一个存储过程 count_by_sex_dept，统计指定系别、指定性别的学生人数，把结果通过 OUT 参数带出。

📋 需求
  1. 输入：系别 dp（VARCHAR(20)）、性别 sx（CHAR(2)，'男'或'女'）
  2. 输出：人数 num（INT）
  3. 使用学生表 s，字段有 sno、sn、sex、dept
  4. 演示如何调用查询"信息学院"的"女"生人数
📖 查看答案
SQL · 标准答案
DELIMITER $$

CREATE PROCEDURE count_by_sex_dept(
    IN  dp  VARCHAR(20),
    IN  sx  CHAR(2),
    OUT num INT
)
BEGIN
    SELECT COUNT(*) INTO num
    FROM s
    WHERE dept = dp AND sex = sx;
END $$

DELIMITER ;

-- 调用步骤：
-- 1. 调用，把信息学院和女传入，结果接到 @n
CALL count_by_sex_dept('信息学院', '女', @n);

-- 2. 查看结果
SELECT @n AS 信息学院女生人数;
💡 解题要点

① 参数列表写在 () 里，用逗号分隔

② 两个 IN 参数 + 一个 OUT 参数，每个都要标明类型

③ OUT 参数的值用 SELECT ... INTO 变量 来赋值

④ 调用时三个实参对应三个形参，最后一个必须是 @ 变量

⚠️ 常见错误

① 漏写 IN / OUT 关键字（默认是 IN，但 OUT 必须写）

② 调用 OUT 参数时传字符串或数字 → 错

③ 用 SET num = SELECT COUNT... → 错，必须用 SELECT ... INTO num

综合大题 2 ⭐⭐⭐ 进阶 流程控制综合

写一个函数 get_grade，传入分数，返回等级字符串（用 IF...ELSEIF...ELSE 实现）：

📋 等级规则
  1. >= 90 → '优秀'
  2. >= 75 → '良好'
  3. >= 60 → '及格'
  4. 其他 → '不及格'

并演示在 SELECT 中使用这个函数：从选课表 sc 中查出每个学生每门课的成绩和等级。

📖 查看答案
SQL · 标准答案
DELIMITER $$

CREATE FUNCTION get_grade(score INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(10);
    
    IF score >= 90 THEN
        SET result = '优秀';
    ELSEIF score >= 75 THEN
        SET result = '良好';
    ELSEIF score >= 60 THEN
        SET result = '及格';
    ELSE
        SET result = '不及格';
    END IF;
    
    RETURN result;
END $$

DELIMITER ;

-- 在 SELECT 中使用这个函数（这就是函数比存储过程灵活的地方！）
SELECT sno, cno, score, get_grade(score) AS 等级
FROM sc;
▶ 输出示例 sno cno score 等级 s1 c1 90 优秀 s1 c2 85 良好 s2 c5 57 不及格 s2 c6 80 良好 ...
💡 解题要点

① 函数返回 VARCHAR(10)，用 RETURNS VARCHAR(10) 声明

② 必须加 DETERMINISTIC

③ 用局部变量 result 存中间结果，最后 RETURN result

④ 函数最强大的地方就是可以嵌入 SELECT，对每行数据自动调用

⑤ 同样的需求如果用存储过程实现，就不能这样直接嵌进 SELECT 了 —— 这就是函数 vs 存储过程的核心区别

💬 思考题

这个需求其实也可以用 SQL 自带的 CASE WHEN 实现，不一定非用函数。

但如果这个等级判断逻辑要在 很多地方反复用，封装成函数就 "写一次，到处用"，避免重复代码。这就是为什么要学函数。

📝 复习重点回顾

通过本次练习，你应该已经：

⭐ 期末考点排行榜

按出现频率排序：

  1. 写带参数的存储过程（综合大题，分值最高）
  2. 三种参数 IN/OUT/INOUT 的区别（必考）
  3. 存储过程 vs 函数 的对比（简答）
  4. 读代码题：给一段过程，问执行结果
  5. DELIMITER 的作用（简答）
💡 关键速记

① "开头改 $$，结尾改回 ;" —— DELIMITER 的标准用法

② "过程 CALL，函数 SELECT" —— 调用方式

③ "IN 你给我，OUT 我给你，INOUT 双向" —— 三种参数

④ "OUT 调用必传 @" —— OUT 参数最易错点

⑤ "函数必须有 DETERMINISTIC + RETURN" —— 写函数三件套