第15章存储过程与函数 · 讲义 | 数据库原理及应用教程（MySQL）

💡 先想象一个场景：

你做了一个学生选课系统。每次老师要查"信息学院的女生人数"，都要敲一长串 SQL：

SELECT COUNT(*) FROM s WHERE sex='女' AND dept='信息学院';

每次老师想查别的院，又要重新写。能不能把这段查询"打包"起来，给它一个名字，下次直接喊一下名字就能用？

👉 这就是 存储过程。它就像编程语言里的"函数"，让 SQL 可以复用。

一、存储过程是什么？概念必考

1.1 一句话定义

📖 定义

存储过程（Stored Procedure） = 一组完成特定功能的 SQL 代码，预先编译好、存储在数据库里，可以反复调用。

🌰 通俗类比

没有存储过程，就像每次做菜都从洗菜开始 —— 客户每次发请求，数据库都要重新解析、编译、执行 SQL。

有了存储过程，就像把菜做成"半成品"放冰箱 —— 客户调用时，数据库直接拿出来加热。

更通俗：把存储过程理解为"数据库里的函数"。

1.2 为什么要用存储过程？必考

⭐ 5 大优点

① 可重复调用：写一次，到处用
② 执行速度快：预编译过，省去每次解析的时间
③ 减少网络流量：客户端只发"调用语句"，不用发整段 SQL
④ 提高安全性：可以限制用户只能调用存储过程，不能直接操作表
⑤ SQL 也能"编程"：可以加 IF / WHILE 等流程控制

💡 实际工作中

真实项目中，复杂的统计、报表、批量处理，常常会用存储过程封装。学好这一章，对将来工作有用。

二、第一个存储过程：完整体验实操必考

2.1 在写存储过程前：DELIMITER必考

写存储过程之前，先要解决一个 "提前结束"的麻烦。

😵 麻烦在哪？

MySQL 默认 遇到分号 ; 就执行一次。

但存储过程里有 多条 SQL，每条都用分号结尾。这样 MySQL 会把过程"拦腰截断"，分多次执行 —— 错！

💡 解决办法：DELIMITER 命令

用 DELIMITER 命令临时把"结束符"改成别的（比如 $$ 或 //），让 MySQL 看到 ; 不再触发执行。

SQL · 改定界符

-- 把结束符改成 $$
DELIMITER $$

-- 这中间可以放多条 SQL，分号不会触发执行
-- ... 存储过程的内容 ...
END $$  -- 用 $$ 来结束

-- 用完后改回默认的 ;
DELIMITER ;

⭐ 必背口诀

"开头改 $$，结尾改回 ;" —— 这是写存储过程的标准模板。

2.2 创建第一个存储过程必考

语法格式

📋 CREATE PROCEDURE 模板

CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
    -- 这里写你想要的 SQL 语句
    -- 可以是 SELECT、INSERT、UPDATE...
    -- 也可以是 IF、WHILE 等流程控制
END

关键点：

CREATE PROCEDURE 名字：起个名字
(参数列表)：可以没有，写成 ()
BEGIN ... END：把多条语句"打包"

完整例子：查询学生总数

SQL · 创建无参存储过程

-- 第一步：改定界符
DELIMITER $$

-- 第二步：创建存储过程
CREATE PROCEDURE show_student_count()
BEGIN
    SELECT COUNT(*) AS 学生总数 FROM s;
END $$

-- 第三步：改回默认定界符
DELIMITER ;

💬 解读

① show_student_count 是过程名，() 表示没有参数

② BEGIN ... END 之间放具体的 SQL 语句

③ 创建成功后，这个过程就 "住"在数据库里了，可以随时用

2.3 调用存储过程：CALL必考

存储过程创建好之后，用 CALL 调用：

SQL · 调用

CALL show_student_count();   -- 调用，返回学生总数

💡 一句话记住

"CREATE 创建、CALL 调用" —— 这是存储过程最基本的两个动词。

2.4 删除存储过程：DROP

SQL

DROP PROCEDURE IF EXISTS show_student_count;

IF EXISTS 可以避免"删除不存在的过程时报错"，建议都加上。

三、参数：IN / OUT / INOUT必考核心

3.1 为什么需要参数？

之前的 show_student_count() 没有参数，只能查"全部学生"。但现实中老师会问：

"信息学院的女生有几个？"
"计算机学院的男生有几个？"

👉 需要让存储过程 "接收输入"。这就是参数的作用。

3.2 三种参数类型必考

存储过程的参数有 3 种类型，区别在于 "数据从哪流到哪"：

📥 IN（输入）

外 → 过程

调用者把值传给存储过程。

最常用，省略时默认 IN

📤 OUT（输出）

过程 → 外

存储过程把值带回给调用者。

用于返回结果

🔄 INOUT（双向）

外 ⇄ 过程

既输入又输出：传入一个值，过程修改后再带回。

了解即可

⭐ 速记

IN = 你给我值（默认）

OUT = 我给你结果

INOUT = 你给我一个值，我改完再给你

3.3 IN 参数：经典例子必考

需求

写一个存储过程，传入"系别"，返回该系的学生人数。

SQL · IN 参数

DELIMITER $$

CREATE PROCEDURE count_by_dept(IN dp VARCHAR(20))
BEGIN
    SELECT COUNT(*) AS 人数
    FROM s
    WHERE dept = dp;
END $$

DELIMITER ;

-- 调用：传入"信息学院"
CALL count_by_dept('信息学院');

💬 解读

① IN dp VARCHAR(20) 声明一个 IN 参数：名字 dp、类型 VARCHAR(20)

② 在过程体里，dp 当作普通变量用，参与查询

③ 调用时直接 CALL count_by_dept('信息学院')，把字符串传给 dp

3.4 OUT 参数：返回结果必考

需求

存储过程查询学生人数，把结果"装"在一个变量里带出来，方便后续使用。

SQL · OUT 参数

DELIMITER $$

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

DELIMITER ;

-- 调用方法：
-- 1. 准备一个会话变量 @n（前面要加 @）来"接住"结果
CALL get_student_count(@n);

-- 2. 看一下结果
SELECT @n;

💬 解读

① OUT total INT：声明 total 是输出参数，过程结束后把值带出来

② SELECT COUNT(*) INTO total：把查询结果赋给 total

③ 调用时传 @n（用户会话变量，前面加 @），让 @n 接住带出来的值

④ 然后 SELECT @n 就能看到结果

⚠️ 关键点

调用 OUT 参数的存储过程时，传进去的必须是 用户会话变量（带 @ 的，比如 @n），不能是普通的常量（不能直接传 5、'abc' 这种）。

四、变量与流程控制（让 SQL 也能"编程"）了解

4.1 局部变量：DECLARE

在存储过程内部，可以声明 局部变量（只在 BEGIN…END 内有效）。用 DECLARE：

SQL · 局部变量

DECLARE total INT DEFAULT 0;       -- 整数变量，默认值 0
DECLARE name VARCHAR(20);             -- 字符串变量

-- 给变量赋值（两种写法等价）
SET total = 100;
SELECT COUNT(*) INTO total FROM s;  -- 把查询结果赋给变量

⚠️ 三种变量速记

@变量名 = 用户会话变量（在 SQL 命令行里随便用，断开连接就消失）

变量名（不加 @）= 局部变量（只在 BEGIN…END 内有效，要先 DECLARE）

@@变量名 = 系统变量（如 @@version 看 MySQL 版本，了解即可）

4.2 IF 条件判断

📋 IF 模板

IF 条件1 THEN
    语句1;
ELSEIF 条件2 THEN
    语句2;
ELSE
    语句3;
END IF;

例子：判断学生平均成绩是否及格

SQL

DELIMITER $$

CREATE PROCEDURE check_pass(IN sid CHAR(10))
BEGIN
    DECLARE avg_score DECIMAL(5,2);
    
    -- 算平均分，存到变量
    SELECT AVG(score) INTO avg_score
    FROM sc WHERE sno = sid;
    
    -- 根据平均分判断
    IF avg_score >= 60 THEN
        SELECT '通过' AS 结果;
    ELSE
        SELECT '不通过' AS 结果;
    END IF;
END $$

DELIMITER ;

4.3 WHILE 循环

📋 WHILE 模板

WHILE 条件 DO
    循环体;
END WHILE;

例子：求 1+2+3+...+100

SQL

DELIMITER $$

CREATE PROCEDURE sum_100()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;
    
    WHILE i <= 100 DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;
    
    SELECT total AS 结果;
END $$

DELIMITER ;

CALL sum_100();   -- 输出 5050

💡 类比其他语言

这个写法跟 Python、Java 的 while 循环一模一样，"循环条件 + 循环体"。

MySQL 还有 REPEAT、LOOP 循环，用法类似，会用 WHILE 一种就够了。

五、自定义函数必考对比

5.1 函数 vs 存储过程：核心区别必考

📦 存储过程（PROCEDURE）

用 CREATE PROCEDURE 创建
用 CALL 调用
参数有 IN / OUT / INOUT 三种
不一定有返回值（用 OUT 参数带出）
主要用于 "做事"（执行操作）

🧮 函数（FUNCTION）

用 CREATE FUNCTION 创建
像内置函数一样直接调用：SELECT 函数名()
参数 只有 IN 一种
必须有返回值（用 RETURN 返回）
主要用于 "算东西"（计算并返回值）

⭐ 一句话区别

存储过程"做事"，函数"算值"。

函数像 NOW()、COUNT() 一样，可以嵌进 SELECT 里直接用；存储过程必须 CALL 单独调用。

5.2 创建一个简单函数

📋 CREATE FUNCTION 模板

CREATE FUNCTION 函数名 (参数列表)
RETURNS 返回值类型
BEGIN
    -- 函数体
    RETURN 值;
END

例子：计算两个数的平均值

SQL · 自定义函数

DELIMITER $$

CREATE FUNCTION avg2(a INT, b INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC                       -- 声明"输入相同则输出相同"，必加
BEGIN
    RETURN (a + b) / 2;            -- 必须有 RETURN！
END $$

DELIMITER ;

-- 调用：直接像普通函数那样用
SELECT avg2(80, 90);    -- 结果：85.00

-- 也可以嵌入 SELECT 中
SELECT sno, avg2(语文, 数学) AS 平均分 FROM scores;

💬 跟存储过程对比

① CREATE FUNCTION（不是 PROCEDURE）

② 必须有 RETURNS 类型（说明返回什么类型）

③ 必须有 RETURN 值;（实际返回的值）

④ 调用时用 SELECT 函数名()，不是 CALL

⚠️ DETERMINISTIC 是什么？

意思是 "输入相同 → 输出一定相同"。MySQL 默认要求函数声明这个特性，不写会报错。

大部分函数都满足这个条件（比如算平均值），直接加上即可。

删除函数

SQL

DROP FUNCTION IF EXISTS avg2;

六、游标（知道概念即可）了解

6.1 游标是什么？

📖 一句话定义

游标（Cursor） = 一个"指针"，指向查询结果集的某一行。用它可以一行一行地处理结果集。

🌰 通俗类比

普通 SELECT 一次性返回所有结果（像一桌菜端上来）。

游标像 "一个一个夹给你"：先打开游标 → 用 FETCH 一次取一行 → 处理完再取下一行 → 关闭。

💡 4 步流程（了解即可）

① DECLARE 游标名 CURSOR FOR 查询语句; —— 声明

② OPEN 游标名; —— 打开

③ FETCH 游标名 INTO 变量; —— 一次取一行

④ CLOSE 游标名; —— 关闭

⚠️ 注意

① 游标 只能在存储过程或函数中使用，不能单独使用。

② 游标性能不如直接 SQL，能不用就不用。

③ 本章浅学，知道有这个东西即可，期末一般不深入考。

七、本章小结

📋 三句话总结

🎯 核心内容

存储过程 = 数据库里的"函数"，可重复调用、执行快、安全。
三种参数：IN（输入）/ OUT（输出）/ INOUT（双向）。
函数 vs 存储过程："过程做事，函数算值"。

本章必考点回顾

⭐ 期末考点

存储过程的概念和优点（简答）
CREATE PROCEDURE + CALL（写代码）
三种参数 IN / OUT / INOUT 的区别（必考）
存储过程 vs 函数的区别（对比题）
DELIMITER 的作用（简答）

关键语法对照表

操作	存储过程	函数
创建	`CREATE PROCEDURE 名(参数) BEGIN ... END`	`CREATE FUNCTION 名(参数) RETURNS 类型 DETERMINISTIC BEGIN ... RETURN 值; END`
调用	`CALL 名(...)`	`SELECT 名(...)`
参数类型	IN / OUT / INOUT	只有 IN
返回值	没有，可用 OUT 带出	必须有 RETURN
删除	`DROP PROCEDURE 名`	`DROP FUNCTION 名`

课堂综合测验

第 1 题

在创建存储过程之前，使用 DELIMITER $$ 命令的目的是？

A. 给存储过程起名字

B. 临时改变 SQL 的结束符，避免存储过程中间被分号"截断"执行

C. 加密存储过程的代码

D. 提高存储过程的执行速度

✅ 正确：B

MySQL 默认遇到 ; 就执行一次。存储过程里有多条以 ; 结尾的语句，会被中途打断。所以用 DELIMITER 临时改成 $$，让 MySQL 看到 ; 不再触发执行。

用完后再用 DELIMITER ; 改回来。

第 2 题

关于存储过程的三种参数类型，下列说法错误的是？

A. IN 参数用于把值传入存储过程，是默认的参数类型

B. OUT 参数用于把值从存储过程带出来给调用者

C. 调用 OUT 参数的存储过程时，可以直接传入常量（如 5、'abc'）

D. INOUT 既能传入又能带出，了解即可

✅ 错的是：C

调用 OUT 参数的存储过程时，必须传入用户会话变量（带 @ 的），不能传常量。

因为 OUT 参数要"装"返回值带出来，常量不能存值，必须用变量来接。

例如：CALL get_count(@n); 然后 SELECT @n; 看结果。

第 3 题

下列关于 存储过程和函数 的说法，正确的是？

A. 存储过程必须有 RETURN 语句

B. 函数可以有 IN、OUT、INOUT 三种参数

C. 函数可以嵌入 SELECT 语句中使用，存储过程不行

D. 存储过程用 SELECT 调用，函数用 CALL 调用

✅ 正确：C

分析：

A 错：存储过程不能有 RETURN（用 OUT 参数带出）

B 错：函数只能用 IN 一种参数

C 对：函数像 NOW()、COUNT() 一样，可以嵌入 SQL 任何地方

D 错：反了。存储过程用 CALL，函数用 SELECT。

记忆口诀："过程 CALL，函数 SELECT"。

第 4 题

在存储过程中声明一个局部变量 total，初值为 0，类型为整数。下列写法正确的是？

A. SET total = 0;

B. DECLARE @total INT DEFAULT 0;

C. DECLARE total INT DEFAULT 0;

D. VAR total INT = 0;

✅ 正确：C

记住：局部变量用 DECLARE 声明，名字前不加 @。

① @变量 是用户会话变量（命令行用，不需要 DECLARE）

② 变量（不加 @）是局部变量，必须先 DECLARE

D 选项 VAR 是 JavaScript 的语法，MySQL 中不存在。