数据库系统原理（04735） · 数据库编程：过程、函数与游标至参考与延伸

返回分篇目录

先理解数据如何建模，再设计表和约束，最后学习数据库编程、并发与恢复。SQL 增删改查集中在 MySQL 入门课程，本篇保留原理与设计方法，避免重复铺一遍命令。

数据库编程：过程、函数与游标

存储过程与存储函数

比较	存储过程	存储函数
调用	CALL	SQL 表达式中调用
参数	可声明 IN、OUT、INOUT	有输入参数，返回值由 RETURNS 与 RETURN 定义
返回	可用输出参数或结果集	必须返回声明类型的值

封装可减少应用往返，但不能无条件认为存储程序更高效。事务、权限、部署与测试仍须设计。下面采用 MySQL 语法，在专用练习库中创建；DELIMITER 是 mysql 客户端命令。

DELIMITER //
CREATE PROCEDURE sp_sum_numbers(IN p_max INT, OUT p_total BIGINT)
BEGIN
  DECLARE v_i INT DEFAULT 1;
  SET p_total = 0;
  WHILE v_i <= p_max DO
    SET p_total = p_total + v_i;
    SET v_i = v_i + 1;
  END WHILE;
END //
DELIMITER ;
CALL sp_sum_numbers(3, @total);
SELECT @total; -- 6

局部变量用 DECLARE，放在块内可执行语句之前；用户变量以 @ 开头，生命周期属于会话，两者不要混用。

条件与循环的使用边界

• IF ... THEN ... ELSEIF ... ELSE ... END IF：按条件执行语句。
• CASE ... END CASE：存储程序中的分支语句；查询值表达式用 CASE ... END。
• WHILE：先判断条件，可能一次也不执行。
• REPEAT ... UNTIL：先执行再判断，至少执行一次。
• LOOP：用带标签的 LEAVE 结束循环；ITERATE 开始下次迭代。

尽可能先用集合 SQL 表达任务；循环逐行执行不是统计查询的默认方案。

下面用同样的零次边界对比 WHILE 与 REPEAT，避免只记语法名称：

DELIMITER //
CREATE PROCEDURE sp_compare_loops(IN p_max INT)
BEGIN
  DECLARE v_while INT DEFAULT 0;
  DECLARE v_repeat INT DEFAULT 0;
  WHILE v_while < p_max DO
    SET v_while = v_while + 1;
  END WHILE;
  REPEAT
    SET v_repeat = v_repeat + 1;
  UNTIL v_repeat >= p_max
  END REPEAT;
  SELECT v_while AS while_count, v_repeat AS repeat_count;
END //
DELIMITER ;
CALL sp_compare_loops(0); -- while_count=0，repeat_count=1

REPEAT 至少执行一次，业务需要允许零次处理时应先校验条件。需要无固定次数的循环时，用带标签的 LOOP 配合 LEAVE，并确保退出条件能推进。

游标按行处理结果集

游标适合确实需要逐行处理的存储程序。声明顺序一般为变量、条件、游标、处理器；依次 OPEN、FETCH、处理、CLOSE，并用 NOT FOUND 处理结束。

-- 在当前连接准备独立练习表；只求行数时直接 COUNT(*)。
CREATE TEMPORARY TABLE customers (cust_id INT PRIMARY KEY);
INSERT INTO customers VALUES (1), (2);
DELIMITER //
CREATE PROCEDURE sp_count_customers(OUT p_count BIGINT)
BEGIN
  DECLARE v_done BOOLEAN DEFAULT FALSE;
  DECLARE v_id INT;
  DECLARE cur CURSOR FOR SELECT cust_id FROM customers;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  SET p_count = 0;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id;
    IF v_done THEN LEAVE read_loop; END IF;
    SET p_count = p_count + 1;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;
CALL sp_count_customers(@customer_count);
SELECT @customer_count; -- 2

MySQL 存储程序游标不是可任意滚动、修改底层结果的客户端游标。先确认是否能用聚合、连接或批量更新替代。

完整性、触发器与访问控制

先用约束表达数据规则

• 实体完整性：主键唯一且非空；一张表一个主键，可由多列组成。
• 参照完整性：外键引用存在的键，删除或更新时按声明规则处理。
• 用户定义完整性：类型、非空、唯一、检查条件等业务规则。
• 组合键只保留标识记录所必需的列，不能随意添加字段来“保证唯一”。

触发器由 INSERT、UPDATE、DELETE 等事件驱动，可使用 OLD 和 NEW 访问相应行。优先使用声明式约束；触发器会引入隐式行为，需要测试和变更管理。现代 MySQL 允许同一表同一事件与时机多个触发器，原笔记“每表最多六个”不是通用结论。

授权与数据约束不是一回事

约束控制数据是否合法，权限控制谁能执行什么。按应用所需授予库、表、列或例程权限，用 SHOW GRANTS 验证；不用示例管理员账号或固定密码作为应用配置。

创建、授权、撤销与删除账号是不同步骤，不能先删账号再继续向其授权。具体身份和权限命令应对照目标数据库版本。

事务、锁与并发控制

ACID 与可串行化

事务把一组操作作为工作单元。原子性关心全部成功或撤销，一致性关心业务约束，隔离性关心并发可见性，持久性关心提交后的保存。它们需要引擎、配置和应用共同满足，不能仅凭语法中有 COMMIT 判断。

可串行化要求并发效果等价于某个串行顺序。隔离级别和实现方式会影响脏读、不可重复读、幻读等现象；锁和多版本并发控制并不是同一机制。

共享锁、排他锁与死锁

基础锁模型中，共享锁之间可兼容，排他锁与冲突访问互斥；实际锁类型、范围和读行为由数据库实现决定。不能说“有 S 锁就任何读都必须等待”。

两段封锁把申请锁与释放锁分为增长、收缩阶段；严格两段封锁还要求相应写锁持有到事务结束。锁粒度更细可提高并发，但增加管理开销。

死锁是相互等待构成环；饥饿是一个请求长期得不到机会，活锁是持续活动却无法推进。可通过固定访问顺序、缩短事务、检测后回滚与有限重试等方式处理，不能把所有问题统称“排队”。

具体实验见 MySQL 锁与死锁。

备份、恢复与综合验收

备份要明确数据、日志、权限及对象定义的覆盖范围；恢复要明确可恢复到的时间点。复制不能替代独立备份，导出一张表的 CSV 也不等于数据库完整备份。

在并发写入下取得一致备份，应使用适合引擎与工具的快照或锁定方案，评估暂停时间；恢复演练在隔离环境核对对象、行数与业务约束，不给所有数据库套一套手动 LOCK TABLES 流程。

用一个“客户—订单”业务完成以下检查：

1. 写出实体、候选键、基数与数据字典。
2. 从函数依赖说明为何拆表，证明不会丢失关联。
3. 为非法外键、重复键、空值和边界值设计验证。
4. 为并发扣减定义事务边界，复现并处理冲突。
5. 从备份恢复到隔离库，核对可恢复时间与业务数据。

参考与延伸

• MySQL 8.4 存储过程与函数
• MySQL 8.4 游标声明
• MySQL 8.4 触发器