第8章 数据库程序设计

8.1 MySQL 编程基础

8.1.1 本章内容总览

本章讨论的是：在普通 SQL 语句之外，如何利用 MySQL 提供的程序化能力完成更复杂的数据库处理任务。课件主要覆盖四类内容：

• MySQL 编程基础：变量、语句块、注释、流程控制、预处理 SQL；
• 触发器：在插入、修改、删除等事件发生时自动执行；
• 函数：把某个可复用的计算或查询逻辑封装成可调用对象；
• 存储过程：把较完整的一组数据库处理逻辑封装起来，通过 call 调用执行。

从程序设计角度看，MySQL 程序同样包含三种基本结构：

• 顺序结构：语句按先后顺序执行；
• 选择结构：根据条件分支执行；
• 循环结构：重复执行某组语句。

因此，MySQL 脚本不只是“写查询语句”，而是把 SQL + 变量 + 控制流 + 过程化封装 组合起来，形成完整的数据库程序。

8.1.2 MySQL 脚本的基本概念

MySQL 脚本通常是由一条或多条 MySQL 语句组成的程序文件，常见扩展名为 .sql。

它的组成通常包括：

• 常量；
• 变量；
• 函数；
• 表达式；
• 关键字；
• 注释；
• SQL 语句和 MySQL 扩展语句。

其中，每条 MySQL 语句都是程序的基本执行单位，每条语句负责完成特定操作。

可以把它理解成：

普通 SQL 负责“做一件事”，而 MySQL 程序负责“按一定逻辑，把很多件事组织起来”。

8.1.3 变量

MySQL 编程中常用两类变量：

• 用户会话变量；
• 局部变量。

这两类变量都能保存中间结果，但它们的定义方式、作用范围、生存周期、使用场景都不同。

8.1.3.1 用户会话变量

用户会话变量以 @ 开头，例如：

@x
@sname
@count_num

它本质上属于当前数据库连接对应的会话空间。MySQL 服务器会为每个连接单独开辟会话空间，不同会话之间互不干扰。

因此，用户会话变量具有以下特点：

• 作用范围：当前会话；
• 生存周期：从连接建立到连接关闭；
• 无需事先声明数据类型；
• 适合保存会话级临时结果。

1. 使用 set 赋值

set @user_variable = expression;

例如：

set @sname = (select sname from student where sno = '20180001');

2. 使用 select 赋值

有两种常见方式：

select @user_variable := expression;

或：

select expression into @user_variable;

例如：

select @sname := '王林';
select sname into @sname from student order by sno limit 1;

3. 理解重点

用户会话变量更像“当前连接里的临时记事本”：

• 定义简单；
• 不需要声明类型；
• 在会话中可以反复使用；
• 常用于接收存储过程输出值、预处理 SQL 参数、调试过程中的中间结果。

8.1.3.2 局部变量

局部变量必须先声明，通常使用 declare 定义。

例如：

declare myvar int default 100;
set myvar = 77;

局部变量的关键特点：

• 必须声明后才能使用；
• 必须指定数据类型；
• 只能定义在 begin ... end 语句块中；
• 主要用于函数、存储过程、触发器等程序内部；
• 作用范围小于用户会话变量。

常见使用场景

1. 在 begin ... end 语句块内部定义

这是最常见的用法。此时必须使用 declare，并声明类型。

2. 作为存储过程或函数的参数

这种情况下不需要再写 declare，但仍然需要写参数类型。

3. 在 SQL 中承接查询结果或参与条件判断

如果 select 的结果是单个值，可以使用：

select ... into 局部变量;

局部变量也可以直接出现在：

• select 的条件中；
• insert 的赋值中；
• update 的条件或赋值中；
• delete 的条件中。

8.1.3.3 用户会话变量与局部变量的区别

可以这样记：

• 会话变量偏“外部”；
• 局部变量偏“内部”。

8.1.4 定界符 delimiter 与 begin ... end 语句块

8.1.4.1 为什么要改定界符

MySQL 客户端默认使用分号 ; 作为一条命令的结束标记。

但是在创建函数、存储过程、触发器时，程序体内部本身就会包含很多分号。如果仍然把 ; 当作客户端结束符，那么客户端会误以为语句已经结束，从而导致创建失败。

所以需要先使用 delimiter 修改结束标记。

例如：

delimiter //
create procedure sp111()
reads sql data
begin
    select sno from student;
    select sname from student limit 3;
end //
delimiter ;

执行逻辑是：

1. 先把结束符从 ; 改为 //；
2. 再创建过程体；
3. 过程体内部仍然可以正常使用 ;；
4. 创建完成后，再把结束符改回 ;。

核心理解

delimiter 改变的不是 SQL 语法本身，而是客户端识别“整条语句结束位置”的方式。

8.1.4.2 begin ... end 语句块

begin ... end 用于把多条语句组织成一个语句块。

基本形式：

begin
    [局部变量声明];
    程序代码行集;
end;

它的意义在于：

• 允许把多条语句当作一个整体；
• 允许在块内部定义局部变量；
• 允许在块内部使用 if、case、循环等控制流语句。

在 MySQL 中的使用限制

课件强调：

• 在一些数据库系统中，begin ... end 可以直接在客户端执行；
• 但在 MySQL 中，begin ... end、局部变量、流程控制语句等，一般只能出现在函数、存储过程、游标、触发器等程序定义内部。

因此，begin ... end 更适合看成“数据库程序体的骨架”。

8.1.5 注释

注释是程序中不被执行的文本，主要用于：

• 说明代码含义；
• 标记重要步骤；
• 临时屏蔽部分语句；
• 帮助调试与维护。

MySQL 常见注释方式有三种：

1. # 注释

从 # 开始直到本行结束，都是注释。

# 这是单行注释

2. -- 注释

从 -- 到本行末尾是注释，但 -- 后面必须有一个空格。

-- 这是单行注释

3. /* ... */ 注释

这是块注释，可以跨多行。

/*
这是多行注释
可以写很多说明
*/

使用建议

• 简短说明用 # 或 -- ；
• 多行解释、屏蔽一段代码时用 /* ... */；
• 书写时注意 -- 后面一定要留空格，否则可能不被识别为注释。

8.1.6 控制流语句

控制流语句负责实现 MySQL 程序中的分支与循环。

课件主要介绍：

• if 语句；
• case 语句；
• while 循环；
• loop 循环；
• leave 语句。

8.1.6.1 if 语句

基本形式：

if condition then
    ...
[elseif condition then
    ...]
[else
    ...]
end if;

它的作用是：按条件分支执行不同语句。

课件示例：函数 exam_if

功能：

• 如果输入参数 x = 10，返回 1；
• 如果 x = 20，返回 2；
• 否则返回 3。

delimiter //
create function exam_if(x int)
returns int
no sql
begin
    declare aa int default 0;
    if x = 10 then
        set aa = 1;
    elseif x = 20 then
        set aa = 2;
    else
        set aa = 3;
    end if;
    return aa;
end //
delimiter ;

调用：

select exam_if(79);

理解重点

• if 更适合处理条件分支明显、逻辑链条清晰的问题；
• 在函数和存储过程中，if 常用于判断参数、控制返回结果、决定是否执行某段 SQL。

8.1.6.2 case 语句

课件给出两种形式。

形式一：按表达式逐值匹配

case input_expression
    when value then ...
    [when value then ...]
    [else ...]
end case;

形式二：按布尔条件逐个判断

case
    when boolean_expression then ...
    [when boolean_expression then ...]
    [else ...]
end case;

课件强调的一个要点

单独使用的 case 语句通常不能省略 else 子句。

也就是说，在过程化程序里写 case ... end case 时，最好始终提供 else，避免遗漏情况。

8.1.6.3 case 在查询语句中的使用

case 不仅能单独写在程序体中，也能嵌入 select 中对查询结果做分类转换。

示例 1：根据开课学期转换为中文描述

把学期编号映射为：

• 1 → 大一上学期
• 2 → 大一下学期
• 3 → 大二上学期
• 4 → 大二下学期
• 5 → 大三上学期
• 6 → 大三下学期

写法一：

select student.sno, sname, cname,
       case semester
           when 1 then '大一上学期'
           when 2 then '大一下学期'
           when 3 then '大二上学期'
           when 4 then '大二下学期'
           when 5 then '大三上学期'
           when 6 then '大三下学期'
       end 开课学期
from student join sc on student.sno = sc.sno
join course on sc.cno = course.cno;

写法二：

select student.sno, sname, cname,
       case
           when semester = 1 then '大一上学期'
           when semester = 2 then '大一下学期'
           when semester = 3 then '大二上学期'
           when semester = 4 then '大二下学期'
           when semester = 5 then '大三上学期'
           when semester = 6 then '大三下学期'
       end 开课学期
from student join sc on student.sno = sc.sno
join course on sc.cno = course.cno;

这两个写法都能完成分类显示。

示例 2：按选课人数给课程打标签

select cname, avg(grade), count(sno),
       case
           when count(sno) > 50 then '较多'
           when count(sno) between 30 and 49 then '适当'
           when count(sno) between 10 and 29 then '较少'
           when count(sno) < 10 then '非常少'
       end 选课人数
from course join sc on course.cno = sc.cno
group by cname;

这一类题的本质

本质是：

把数据库中的“编码值”或“统计结果”转换成更容易理解的业务语义文本。

这类写法在查询题、报表题中非常常见。

8.1.6.4 while 循环

基本形式：

while condition do
    ...
end while;

执行方式：

1. 先判断条件；
2. 若条件为真，执行循环体；
3. 再次判断；
4. 条件为假时退出。

因此，while 属于先判断、后执行的循环。

适合场景：

• 已知循环终止条件；
• 需要不断重复处理，直到条件不成立。

8.1.6.5 loop 循环与 leave

loop 的基本形式：

loop
    ...
end loop;

它本身没有内置退出条件，通常需要借助 leave 语句退出。

leave 结构：

leave label;

这说明：

• while：退出条件写在循环头部；
• loop：退出条件往往写在循环体内部，再通过 leave 跳出。

因此，loop 更灵活，但也更容易写出死循环，需要格外注意退出逻辑。

8.1.7 预处理 SQL 语句

课件把预处理 SQL 放在附录位置，但内容上属于 MySQL 编程基础的一部分。

8.1.7.1 什么是预处理 SQL

MySQL 可以使用预处理方式执行 SQL 语句。

如果某条 SQL 在运行期间，其语句本身或者其中的参数可以动态变化，那么这类 SQL 通常称为：

• 动态 SQL；
• 预处理 SQL。

其核心思想是：

1. 先把 SQL 模板准备好；
2. 首次执行时由 MySQL 解析；
3. 解析成功后缓存；
4. 后续可以多次执行，而不必每次重新解析。

因此它具备：

一次解析，多次执行 的性能优势。

8.1.7.2 基本语法

prepare stmt_name from preparable_stmt;
execute stmt_name [using @var_name [, @var_name] ...];
deallocate prepare stmt_name;

三条语句分别表示：

• prepare：预备一个语句；
• execute：执行预处理语句；
• deallocate prepare：释放预处理语句。

8.1.7.3 课件示例

需求：输出 student 表中的前几行记录的部分数据。

delimiter //
create procedure temp(in_num int)
reads sql data
begin
    set @a = in_num;
    prepare STMT from 'select sno, sname from student limit ?';
    execute STMT using @a;
    deallocate prepare STMT;
end //
delimiter ;

调用：

call temp(2);
call temp(3);

这个例子的意义

它说明：

• 查询模板本身可以固定；
• 真正变化的是参数；
• 执行时再把参数传进去。

这在需要动态拼接条件、根据输入生成不同 SQL 的场景中很有用。

8.2 触发器

8.2.1 触发器的基本概念

触发器是定义在表上的一种特殊数据库对象。

它的特点是：

不是手工主动调用，而是在指定事件发生时自动触发执行。

常见触发事件包括：

• insert
• update
• delete

因此，触发器经常用于：

• 自动记录日志；
• 自动修正数据；
• 自动检查规则；
• 自动联动更新；
• 自动报警或拒绝非法操作。

8.2.2 before 与 after

触发器既可以定义为 before，也可以定义为 after。

1. before

先执行触发器语句体，再执行真正的数据操作。

适合场景：

• 在写入前修改即将写入的数据；
• 在操作前做合法性修正；
• 在写入前做拦截。

2. after

先执行数据操作，再执行触发器语句体。

适合场景：

• 记录操作日志；
• 做备份；
• 依赖“操作已经发生”的后续处理。

可以把它理解成：

• before：动手前先检查或修正；
• after：动手后再收尾或记录。

8.2.3 old 表与 new 表

课件把它称为触发器中的两个临时工作表：old 表和 new 表。

它们的含义是：

• old：保存被修改前的旧值；
• new：保存即将写入或刚写入的新值。

本质上，这是 MySQL 在触发器事件发生时，为当前被操作的那一行构造出的两个“逻辑行视图”。

常见理解方式

• insert：只有 new 有意义；
• delete：只有 old 有意义；
• update：old 和 new 都有意义。

例如修改成绩时：

• old.grade 表示修改前成绩；
• new.grade 表示修改后成绩。

8.2.4 MySQL 触发器的执行粒度

课件特别强调：

MySQL 触发器只支持 for each row。

这表示：

• 一条 update 或 delete 语句即使影响多行；
• 触发器也会对每一行分别触发一次；
• 每次触发时，old 和 new 只代表当前这一行。

因此，触发器处理的是行级事件，而不是“整条语句整体只触发一次”。

这是理解日志记录题、批量更新题、级联处理题的关键。

8.2.5 使用触发器的注意事项

课件列出几个非常重要的限制：

1. 触发程序中的 select 不能返回结果集

也就是说，触发器里可以写 select ... into ... 等承接结果的写法，但不能直接向客户端返回一个结果表。

2. 不能在触发器中对“本表”再做更新语句

例如，在某个表的触发器里再次 update 这个表，容易报错，甚至可能导致死循环。

课件建议：

• 若只是想修改即将写入的当前行数据；
• 可以在 before 触发器中直接使用 set new.字段 = ...。

3. 触发器适合自动化规则，但不宜过度滥用

虽然课件没有展开讲设计原则，但从例题可以看出：

• 简单规则适合触发器；
• 复杂业务逻辑如果全塞进触发器，会增加调试和维护难度。

8.2.6 触发器的一般语法

课件中的例题可以归纳出如下写法：

delimiter //
create trigger trigger_name
before | after insert | update | delete
on table_name for each row
begin
    触发器语句体;
end //
delimiter ;

关键组成包括：

• 触发器名称；
• 触发时机：before 或 after；
• 触发事件：insert、update、delete；
• 所属表；
• 行级触发：for each row；
• 程序体。

8.2.7 例 1：成绩上涨超过 10% 时记录日志

需求：当 SC 表中 grade 被修改，并且涨幅超过 10% 时，把本次修改记录到日志表 sc_up 中。

1. 准备日志表

create table if not exists sc_up
(
    changer char(20),
    ctime time,
    sno char(8),
    cno char(5),
    oldgrade int,
    newgrade int
);

2. 创建触发器

delimiter //
create trigger tri_up after update
on sc for each row
begin
    if (new.grade > 1.1 * old.grade) then
        insert into sc_up values
        (user(), now(), old.sno, old.cno, old.grade, new.grade);
    end if;
end //
delimiter ;

3. 验证

update sc set grade = grade + 20 where grade < 60;

4. 本例要点

• 这是 after update 触发器；
• 依赖 old.grade 和 new.grade 比较涨幅；
• 使用 user() 和 now() 记录操作者与时间；
• 典型用途是 审计日志。

8.2.8 例 2：插入学生时年龄下限自动修正为 16

需求：向 student 表插入学生记录时，如果年龄小于 16，则自动改为 16。

delimiter //
create trigger tri_limit
before insert
on student for each row
begin
    if new.age < 16 then
        set new.age = 16;
    end if;
end //
delimiter ;

验证：

insert into student values
('8888888', '张笑', '女', 14, '通信工程系');

本例要点

• 必须使用 before insert；
• 因为要在真正写入前修正 new.age；
• 这是触发器实现“自动纠错”的经典模式。

这个例子非常重要，因为它也说明了：

当需要修改“即将插入或更新的当前行值”时，应优先考虑 before 触发器 + set new.列名 = 值。

8.2.9 例 3：课程号变更时同步更新选课表

需求：当 course 表中某门课的课程号发生变化时，同步更新 sc 表中对应的课程号。

delimiter //
create trigger cno_update
after update
on course for each row
begin
    update sc
    set cno = new.cno
    where cno = old.cno;
end //
delimiter ;

验证：

update course set cno = '9999' where cno = 'C009';

本例要点

• 这是利用触发器实现“联动更新”；
• 核心条件是 where cno = old.cno；
• 更新目标使用 new.cno。

课件特别说明

此例能够执行的前提是：

sc 和 course 表之间不存在参照—被参照关系。

这句话的含义是：

• 如果已经建立了外键参照约束，尤其带有联动策略；
• 那么这类问题通常应该优先通过参照完整性机制处理；
• 课件这个例子主要用于演示“触发器也能实现联动更新”。

8.2.10 例 4：禁止成绩涨幅超过 20%

需求：如果 SC 表中的成绩涨幅超过 20%，则报错并撤销相关操作。

delimiter //
create trigger tri_nochange after update
on sc for each row
begin
    if (new.grade > old.grade * 1.2) then
        signal sqlstate '45000'
        set message_text = 'message:不允许将成绩增加20%以上';
    end if;
end //
delimiter ;

验证：

update sc set grade = grade + 30 where grade < 60;

本例要点

• 使用 signal 主动抛出错误；
• sqlstate '45000' 通常表示用户自定义异常；
• 一旦触发错误，系统会撤销本次非法更新。

这体现了触发器的另一个重要用途：

不仅能自动修正数据，还能主动阻止非法操作。

8.2.11 例 5：删除成绩记录时自动备份

需求：删除 sc 表中的记录时，把被删数据备份到 sc_deleback 表中。

1. 准备备份表

create table if not exists sc_deleback
(
    changer char(20),
    ctime time,
    sno char(8),
    cno char(5),
    oldgrade int
);

2. 创建触发器

delimiter //
create trigger tri_dele after delete
on sc for each row
begin
    insert into sc_deleback
    values(user(), now(), old.sno, old.cno, old.grade);
end //
delimiter ;

验证：

delete from sc where grade < 70;

本例要点

• delete 操作只有旧值，没有新值，因此只使用 old；
• 这是典型的“删除备份”或“删除审计”场景；
• 适合保留操作痕迹，便于追责和恢复。

8.2.12 例 6：插入成绩时监测学业预警

需求：当向 sc 表插入一条成绩记录后，如果发现该学生不及格课程总学分超过 10，则把该生姓名、不及格科目数、不及格总学分插入 warn_student 表。

1. 准备报警表

create table if not exists warn_student
(
    sname char(10),
    countcno int,
    sumcno int
);

2. 创建触发器

delimiter //
create trigger tri_warngrade after insert
on sc for each row
begin
    select sum(ccredit) into @aa
    from student, sc, course
    where student.sno = sc.sno
      and course.cno = sc.cno
      and grade < 60
      and student.sno = new.sno;

    if (@aa > 10) then
        insert into warn_student
        (select sname, count(sc.cno), sum(ccredit)
         from student, sc, course
         where student.sno = sc.sno
           and course.cno = sc.cno
           and grade < 60
           and sc.sno = new.sno
         group by sname);
    end if;
end //
delimiter ;

本例要点

• 这是 after insert 触发器；
• 先统计新插入成绩影响下的“不及格总学分”；
• 如果超过阈值，再写入预警表；
• 属于典型的“业务监控 + 自动报警”场景。

8.2.13 触发器部分的知识总结

这一节最核心的理解有四点：

1. 触发器是“被动执行”的数据库程序

不是 call 调用，而是遇到事件自动执行。

2. before 与 after 的区别必须分清

• 要改当前行值：优先考虑 before；
• 要记日志、做备份、做后续联动：常用 after。

3. old 与 new 是所有例题的关键

• 比较前后差异；
• 记录旧值；
• 生成新值；
• 做联动更新。

4. 触发器适合做“自动化约束补充”

它常用于：

• 审计；
• 备份；
• 限制非法修改；
• 自动修正；
• 预警。

但当系统已经有更直接的约束手段时，例如主码、外键、检查约束、级联规则等，也不应机械地全部改用触发器实现。

8.3 函数

函数分为两部分：

• 常见内置函数；
• 自定义函数。

从本质上说，函数就是把某种运算或处理逻辑封装成一个名字，以后可直接调用。

8.3.1 常见函数

8.3.1.1 数学函数

课件列出的常见数学函数包括：

• abs(n)：求绝对值；
• sign(n)：返回符号值；
• mod(n, m)：求余；
• floor(n)：向下取整；
• ceiling(n)：向上取整；
• round(n)：四舍五入；
• exp(n)：求 e 的 n 次方；
• log(n, m)：求以 m 为底 n 的对数；
• log(n)：求自然对数；
• pow(n, m) 或 power(n, m)：求幂；
• sqrt(n)：求平方根；
• rand()：返回 0 到 1 之间的随机数；
• degrees(n)：弧度转角度；
• radians(n)：角度转弧度；
• truncate(n, m)：截取为 m 位小数；
• least(...)：取最小值；
• greatest(...)：取最大值。

这些函数常用于：

• 成绩换算；
• 统计结果处理；
• 数值修约；
• 范围比较；
• 随机抽样。

8.3.1.2 字符串函数

课件列出的常见字符串函数包括：

• ascii()：返回字符的 ASCII 码值；
• ltrim(str)：去掉左侧空格；
• concat(s1, s2, ..., sn)：拼接字符串；
• quote(str)：转义字符串中的单引号；
• repeat(str, n)：重复字符串；
• replace(str, srchstr, rplcstr)：替换子串；
• reverse(str)：倒置字符串；
• right(str, n)：取最右边 n 个字符；
• rpad(str, n, pad)：右侧填充到指定长度；
• rtrim(str)：去掉右侧空格；
• strcmp(s1, s2)：比较两个字符串；
• substring(str, n, m) 或 mid(str, n, m)：截取子串；
• trim(str)：去掉首尾空格；
• ucase(str) 或 upper(str)：转为大写。

这类函数在处理姓名、院系、课程号、文本格式化时尤其常见。

8.3.1.3 时间日期函数

课件列出的时间日期函数包括：

• curdate() 或 current_date()：当前日期；
• curtime() 或 current_time()：当前时间；
• date_add(date, interval int keyword)：日期加时间间隔；
• date_format(date, fmt)：按指定格式输出日期；
• date_sub(date, interval int keyword)：日期减时间间隔；
• dayofweek(date)：一周中的第几天；
• dayofmonth(date)：一月中的第几天；
• dayofyear(date)：一年中的第几天；
• dayname(date)：星期名称；
• from_unixtime(ts, fmt)：把 Unix 时间戳格式化为日期时间；
• hour(time)：小时值；
• minute(time)：分钟值；
• month(date)：月份值；
• now()：当前日期和时间；
• quarter(date)：季度；
• week(date)：一年中的第几周；
• year(date)：年份。

这类函数常用于：

• 登录时间记录；
• 年龄或时段统计；
• 报表输出；
• 时间筛选条件构造。

8.3.1.4 数据类型转换函数

课件介绍了两类：convert() 和 cast()。

1. convert()

有两种常见形式：

convert(n using charset)
convert(n, type)

含义分别是：

• 按字符集转换；
• 按数据类型转换。

2. cast()

cast(n as type)

表示把 n 转换为指定类型。

课件示例

select 2 + cast('48' as signed), 2 + '48';

结果都为 50。

这个例子说明：

• MySQL 在表达式计算中可能发生隐式类型转换；
• cast() 可以显式控制转换过程；
• 在编程中显式转换通常更清晰、更稳妥。

8.3.1.5 控制流程函数

这类函数和前面的 if、case 语句不同：

• 前面的是语句级控制结构；
• 这里的是表达式级函数。

1. ifnull(s1, s2)

如果 s1 为 null，返回 s2；否则返回 s1。

例如：

select ifnull(1, 2), ifnull(null, 'MySQL');

2. nullif(s1, s2)

如果 s1 与 s2 相等，返回 null；否则返回 s1。

例如：

select nullif(1, 1), nullif('A', 'B');

3. if(condition, s1, s2)

如果条件为真，返回 s1；否则返回 s2。

它适合直接嵌入查询表达式中进行简短条件判断。

8.3.1.6 系统信息函数

课件列出的系统信息函数包括：

• database()：当前数据库名；
• benchmark(count, n)：把表达式 n 重复执行 count 次；
• connection_id()：当前连接 ID；
• found_rows()：返回最后一次查询检索的总行数；
• user() 或 system_user()：当前登录用户名；
• version()：MySQL 服务器版本。

这些函数适合用于：

• 调试；
• 审计；
• 环境检查；
• 性能测试；
• 日志记录。

8.3.2 自定义函数

8.3.2.1 自定义函数的作用

自定义函数是把某段可重复使用的逻辑封装成函数名，以后可在 select 或表达式中直接调用。

它适合封装：

• 某个字段查找；
• 某种统计值计算；
• 某个等级判定；
• 某个固定转换逻辑。

课件强调：

• 创建自定义函数时要基于当前数据库；
• 调用方式与 MySQL 内置函数类似；
• 函数返回的是单个标量值，不返回结果集。

8.3.2.2 自定义函数的一般语法

create function func_name(func_parameter type [, ...])
returns return_type
[characteristic ...]
begin
    function_body_statements;
    return return_value;
end;

其中 characteristic 常见写法有：

• contains sql
• no sql
• reads sql data
• modifies sql data

这些参数的作用是描述函数对 SQL 数据的访问特征与操作范围。

8.3.2.3 自定义函数的特点

1. 必须有返回值

函数的核心就是“输入一些参数，返回一个结果”。

2. 通过 return 返回结果

通常在函数体末尾显式写 return。

3. 适合嵌入查询语句中调用

这一点和存储过程不同。函数可以出现在：

• select 列表；
• 表达式；
• 条件判断；
• 排序或统计逻辑中。

4. 不返回结果集

函数返回的是一个值，而不是一张表。

8.3.2.4 例 1：返回课程号对应的课程名

需求：创建函数 func_course，根据课程号返回课程名；再利用该函数查询某学生的选课名称和成绩。

delimiter //
create function func_course(in_cno char(4))
returns char(20)
reads sql data
begin
    return (select cname from course where cno = in_cno);
end //
delimiter ;

调用：

select func_course(cno), grade
from sc
where sno = '0811101';

本例要点

• 函数把“课程号查课程名”封装起来；
• 以后凡是需要根据 cno 显示课程名，都可直接复用。

8.3.2.5 例 2：统计指定学生的选课门数

需求：创建函数 f_count，返回某学生选课门数；再查询计算机系学生姓名及其选课门数。

delimiter //
create function f_count(in_sno char(7))
returns int
reads sql data
begin
    return (select count(*) from sc where sno = in_sno);
end //
delimiter ;

调用：

select sname as 姓名, f_count(sno) as 选课门数
from student
where dept = '计算机系';

本例要点

• 这是“聚合结果封装成函数”的典型写法；
• 让查询语句更简洁，也更便于复用。

8.3.2.6 例 3：统计指定院系学生平均年龄

需求：创建函数 f_avgage，返回指定院系学生平均年龄；再查询每个系的平均年龄。

delimiter //
create function f_avgage(in_dept char(20))
returns float
reads sql data
begin
    return (select avg(age) from student where dept = in_dept);
end //
delimiter ;

调用：

select distinct dept, f_avgage(dept) as 平均年龄
from student;

本例要点

• distinct dept 用来避免重复院系；
• 每个院系名称作为参数传入函数；
• 体现了函数与普通查询组合使用的方式。

8.3.2.7 例 4：根据平均成绩返回等级评价

需求：创建函数 f_avggrade，根据指定学生的平均成绩返回评价等级：

• >= 90：优
• 80 ~ 89：良
• 70 ~ 79：中
• 60 ~ 69：及格
• < 60：不及格

delimiter //
create function f_avggrade(in_sno char(7))
returns char(3)
reads sql data
begin
    declare message char(3);
    declare aa int;

    select round(avg(grade)) into aa
    from sc
    where sno = in_sno;

    case
        when aa >= 90 then set message = '优';
        when aa between 80 and 89 then set message = '良';
        when aa between 70 and 79 then set message = '中';
        when aa between 60 and 69 then set message = '及格';
        when aa < 60 then set message = '不及格';
        else begin end;
    end case;

    return message;
end //
delimiter ;

调用：

select sname, f_avggrade(sno) as 平均成绩等级
from student;

本例要点

• 函数内部可以定义局部变量；
• 可以用 select ... into ... 承接查询结果；
• 可以在函数体中结合 case 做等级分类；
• 最终返回的是一个业务语义明确的值。

8.3.2.8 函数部分的知识总结

函数适合处理的是：

• 输入明确；
• 输出唯一；
• 可重复使用；
• 能嵌入 SQL 表达式中调用。

它更像数据库里的“可复用小工具”。

如果你的需求是：

• 根据一个值算出另一个值；
• 根据一个主键查出一个单值；
• 根据一组数据得出一个统计结果；
• 把某种评分规则、分档规则封装起来；

那么函数往往很适合。

8.4 存储过程

8.4.1 什么是存储过程

存储过程是一组为了完成特定功能而预先编写并保存在数据库中的 SQL 程序。

它比函数更“过程化”，适合完成：

• 多步查询；
• 多次条件判断；
• 参数输入与输出；
• 返回结果集；
• 复杂数据库操作流程。

简单说：

• 函数偏“算一个值”；
• 存储过程偏“做一件事”。

8.4.2 存储过程与函数的比较

课件从共同点和不同点两个角度进行了总结。

一、共同点

• 都可以重复使用；
• 都能减少数据库开发人员，尤其是应用程序开发人员的工作量；
• 都能把数据库逻辑封装起来，提高复用性。

二、不同点

1. 返回值方面不同

• 函数必须且只有一个返回值；
• 存储过程可以没有返回值，也可以有一个或多个返回值；
• 存储过程的返回结果常通过 out 或 inout 参数传出。

2. 返回结果集方面不同

• 函数体内可以 select ... into ... 给变量赋值；
• 但函数不能直接向外返回结果集；
• 存储过程则没有这个限制，甚至可以返回多个结果集。

3. 调用方式不同

• 函数可直接嵌入 SQL 表达式；
• 存储过程通常要单独使用 call 调用。

4. 限制程度不同

• 函数体的限制通常更多；
• 存储过程限制相对较少；
• 绝大多数 SQL 语句或 MySQL 命令都可以写进存储过程中。

一个非常实用的判断方法

• 只要你想“返回一个值并嵌入查询”，优先考虑函数；
• 只要你想“执行一整套处理流程”，优先考虑存储过程。

8.4.3 存储过程的一般语法

创建语法：

create procedure sp_name
([proc_parameter [, ...]])
[characteristic ...]
routine_body

调用语法：

call [dbname.]sp_name([parameter [, ...]]);

其中 characteristic 与函数类似，也可使用：

• contains sql
• no sql
• reads sql data
• modifies sql data

参数一般分为：

• in：输入参数；
• out：输出参数；
• inout：既输入又输出。

8.4.4 例 1：无参存储过程 proc_stu

需求：查询所有姓“王”的学生的学号、姓名、出生日期。

delimiter //
create procedure proc_stu()
reads sql data
begin
    select sno, sname, sbirthdate
    from student
    where sname like '王%'
    order by sno;
end //
delimiter ;

调用：

call proc_stu();

本例要点

• 这是最简单的无参过程；
• 直接返回一个查询结果集；
• 适合封装固定查询。

8.4.5 例 2：带多个输入参数的存储过程 select_score

需求：输入学号和课程号，查询该学生在该课程中的姓名、课程名、成绩。

delimiter //
create procedure select_score(in_sno char(7), in_cno char(4))
reads sql data
begin
    select sname, cname, grade
    from student
    join sc on sc.sno = student.sno
    join course on course.cno = sc.cno
    where student.sno = in_sno
      and course.cno = in_cno;
end //
delimiter ;

调用：

call select_score('0811101', 'C001');

本例要点

• 过程可以接收多个输入参数；
• 参数可直接用于多表连接查询；
• 适合“带条件的封装查询”。

8.4.6 例 3：带输入输出参数的存储过程 stu_choose

需求：输入学生姓名，统计其不及格课程门数，并通过输出参数返回。

delimiter //
create procedure stu_choose(in name char(10), out count_num int)
reads sql data
begin
    select count(cno) into count_num
    from sc join student on student.sno = sc.sno
    where grade < 60 and sname = name;
end //
delimiter ;

调用：

call stu_choose('刘晨', @c_num);
select @c_num as 不及格课程门数;

本例要点

• 使用了 out 参数把结果带出过程；
• 调用时用用户会话变量 @c_num 接收输出值；
• 这类写法在考试题和实际项目中都非常常见。

8.4.7 例 4：同时返回结果集和输出结论的存储过程 do_query

需求：输入学号，先输出该学生成绩单，并按成绩降序排列；再统计高于 85 分的课程门数；如果超过 2 门，则输出 very good!，否则输出 come on!。

delimiter //
create procedure do_query(in s_no char(7), out str char(10))
begin
    declare aa int default 0;

    select *
    from sc
    where sno = s_no
    order by grade desc;

    select count(*) into aa
    from sc
    where sno = s_no and grade > 85;

    if aa > 2 then
        set str = 'very good!';
    else
        set str = 'come on!';
    end if;
end //
delimiter ;

调用：

call do_query('0811102', @str);
select @str as 统计结果;

本例要点

这个例子非常典型，因为它把存储过程的几个优势都体现出来了：

• 可以先返回结果集；
• 再做统计；
• 再进行条件判断；
• 最后通过输出参数返回结论。

这说明存储过程比函数更适合组织“多步骤数据库任务”。

8.4.8 存储过程部分的知识总结

存储过程最适合处理的场景是：

• 一次操作里要执行多条 SQL；
• 需要过程化控制；
• 需要输入参数与输出参数；
• 需要返回结果集；
• 需要封装一整套数据库业务流程。

在学习时可以把它理解成：

存储过程是数据库里的“小程序”，函数是数据库里的“小工具”。

8.5 本章核心知识归纳

8.5.1 四类对象的定位

1. 变量

用于保存中间结果，是程序执行的基础。

2. 控制流语句

用于实现分支与循环，是程序逻辑的骨架。

3. 触发器

用于在表事件发生时自动执行，是“事件驱动型”程序对象。

4. 函数

用于返回单个标量值，是“值计算型”程序对象。

5. 存储过程

用于完成一整套数据库处理流程，是“任务执行型”程序对象。

8.5.2 几个最容易混淆的点

1. 用户会话变量与局部变量

• 会话变量：@变量名，会话期间有效；
• 局部变量：declare 定义，仅在当前程序块有效。

2. delimiter 与分号

• 分号是默认结束符；
• delimiter 是为了让客户端正确识别整条程序定义的结束位置。

3. if 语句与 if() 函数

• if ... then ... end if：语句级控制结构；
• if(condition, s1, s2)：表达式级函数。

4. case 语句与 select 中的 case

• 单独写在程序体中的是控制语句；
• 写在查询中的是结果分类表达式。

5. before 触发器与 after 触发器

• before：先执行触发器，再执行数据操作；
• after：先执行数据操作，再执行触发器。

6. old 与 new

• old：旧值；
• new：新值；
• update 时二者都能用。

7. 函数与存储过程

• 函数：返回一个值，可嵌入 SQL；
• 存储过程：完成一个过程，用 call 调用，可返回结果集和多个输出值。

8.5.3 从考试与实操角度看，本章最值得掌握的内容

必须熟练掌握

• 会话变量与局部变量的写法；
• delimiter 和 begin ... end；
• if、case 的两种写法；
• select ... into ...；
• before / after 触发器；
• old / new 的使用；
• 自定义函数的定义与调用；
• 存储过程的定义、调用、输入输出参数；
• 预处理 SQL 的基本语法。

必须会分析的题型

• 用 case 改写查询显示结果；
• 用触发器实现日志记录、限制更新、自动修正；
• 用函数封装统计逻辑；
• 用存储过程实现多步查询与输出。

8.6 本章学习结论

这一章的核心，不是死记语法，而是建立下面这套认识：

1. SQL 不只是查询语言，也可以写程序。
2. MySQL 支持变量、流程控制和语句块，因此能完成过程化处理。
3. 触发器解决“事件发生时自动做什么”。
4. 函数解决“给定输入后返回什么值”。
5. 存储过程解决“要按什么步骤完成一整件事”。

如果把这几类对象的角色分清，再结合例题反复练习，你就能把本章从“会背语法”提升到“会根据需求选工具、写程序、分析执行过程”。