第 12 章视图与存储过程

站长2026-10-23 16:00:00

内容摘要

前面我们都是一条一条地写 SQL，本篇进入"封装"的世界——把常用查询封装成视图、把一段业务逻辑封装成存储过程，重复调用、统一口径。本篇讲视图的创建修改、存储过程的参数与流程控制、以及游标的基本用法。学完后你能把复杂查询模块化，避免到处复制粘贴 SQL。本示例基于 MySQL 8.0 语法。

📖 本篇简介：前面我们都是一条一条地写 SQL，本篇进入"封装"的世界——把常用查询封装成视图、把一段业务逻辑封装成存储过程，重复调用、统一口径。本篇讲视图的创建修改、存储过程的参数与流程控制、以及游标的基本用法。学完后你能把复杂查询模块化，避免到处复制粘贴 SQL。本示例基于 MySQL 8.0 语法。

1 视图虚拟的表

视图（View）是一张"虚拟的表"：它本身不存数据，数据仍来自基表，视图只是把一条 SELECT 查询存了起来。你像查表一样查视图，MySQL 在背后执行那条查询。

视图的典型用途：

1. 简化复杂查询：多表 JOIN + 子查询写一次存成视图，以后直接 SELECT * FROM 视图。

2. 权限控制：只开放视图给某些用户，隐藏敏感列（如把手机号脱敏后再开放）。

3. 统一口径：把"活跃用户""销售额"等统计逻辑固定在视图里，所有报表引用同一个定义。

| 对比 | 普通表 | 视图 |

|---|---|---|

| 存数据 | 是 | 否，查询时实时计算 |

| 占用空间 | 数据 + 索引 | 只存定义 |

| 修改基表 | 直接影响视图结果 | 视图自动反映最新数据 |

| 适合 | 业务数据 | 简化查询、权限隔离 |

2 视图的创建修改与删除

2.1 创建视图

-- 创建一个"用户订单汇总"视图（MySQL 8.0）
CREATE VIEW v_user_order AS
SELECT
  u.user_id,
  u.name,
  COUNT(o.order_id) AS order_cnt,
  SUM(o.amount)     AS total_amt
FROM user_info u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;

建好后，像查表一样用它：

-- 查询视图（MySQL 8.0）
SELECT * FROM v_user_order WHERE total_amt > 1000;

2.2 修改与删除视图

-- 修改视图定义（MySQL 8.0）
CREATE OR REPLACE VIEW v_user_order AS
SELECT u.user_id, u.name, COUNT(o.order_id) AS cnt
FROM user_info u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;

-- 删除视图（MySQL 8.0）
DROP VIEW IF EXISTS v_user_order;

CREATE OR REPLACE 的好处是：视图不存在就创建、存在就替换，省去先 DROP 再 CREATE 的两步。

2.3 视图的可更新性

普通视图在一定条件下可以 INSERT/UPDATE/DELETE，MySQL 会把操作转发回基表。但包含以下元素的视图不可更新：

聚合函数（SUM/COUNT）、GROUP BY、DISTINCT；
多表 JOIN 且修改涉及多表；
子查询在 SELECT 列表里。

记住：视图主要是用来"查"的，别指望拿复杂聚合视图去做写操作。

2.4 视图实战：脱敏后开放

客服系统只想让客服看到脱敏后的手机号，不想暴露完整号码。建一个视图专门给他们用：

-- 脱敏视图（MySQL 8.0）
CREATE VIEW v_user_mask AS
SELECT
  user_id,
  name,
  CONCAT(SUBSTRING(phone,1,3), '****', SUBSTRING(phone,8)) AS phone,
  city
FROM user_info;

客服账号只授予这个视图的 SELECT 权限，碰不到原始表，敏感字段自然泄露不出去。这就是视图做权限控制的典型场景。

3 存储过程入门

存储过程（Stored Procedure）是预先编译好、存在数据库里的一段 SQL 程序。它可以接收参数、有变量、有分支循环，像写函数一样把业务逻辑封装进去。

3.1 第一个存储过程

-- 最简单的存储过程（MySQL 8.0）
DELIMITER $$                       -- 临时把语句结束符改成 $$
CREATE PROCEDURE sp_hello()
BEGIN
  SELECT 'Hello, MySQL' AS msg;    -- 过程体里的 SQL
END$$
DELIMITER ;                        -- 改回分号

几个要点：

1. DELIMITER $$：因为过程体里有分号，要临时改结束符，否则 MySQL 遇到第一个 ; 就以为语句结束了。

2. BEGIN ... END 包住过程体。

3. 调用：CALL sp_hello();

-- 调用存储过程（MySQL 8.0）
CALL sp_hello();

3.2 查看与删除存储过程

-- 查看已创建的存储过程（MySQL 8.0）
SHOW PROCEDURE STATUS WHERE Db = '你的库名';

-- 删除存储过程（MySQL 8.0）
DROP PROCEDURE IF EXISTS sp_hello;

4 存储过程的参数INOUT与OUT与INOUT

存储过程有三种参数模式：

| 模式 | 含义 | 能否返回给调用者 |

|---|---|---|

| IN（默认） | 输入参数，调用时传入 | 否 |

| OUT | 输出参数，过程里赋值后带回 | 是 |

| INOUT | 既传入又带回 | 是 |

4.1 IN：传入参数

-- 按用户 ID 查订单数（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_order_cnt(IN p_uid BIGINT)
BEGIN
  SELECT COUNT(*) AS order_cnt
  FROM orders
  WHERE user_id = p_uid;
END$$
DELIMITER ;

-- 调用
CALL sp_order_cnt(1001);

4.2 OUT：带回结果

-- 把订单数写到 OUT 参数（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_get_cnt(IN p_uid BIGINT, OUT p_cnt INT)
BEGIN
  SELECT COUNT(*) INTO p_cnt
  FROM orders
  WHERE user_id = p_uid;
END$$
DELIMITER ;

-- 调用：用用户变量 @ 接收 OUT 值
CALL sp_get_cnt(1001, @cnt);
SELECT @cnt;

@cnt 是会话用户变量，CALL 后过程把结果写进去，再 SELECT @cnt 取出来。

4.3 INOUT：既传入又带回

INOUT 把外部变量传进去，过程里改完再带回来，适合"就地更新"：

-- 给传入的数加 10（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_add10(INOUT v INT)
BEGIN
  SET v = v + 10;
END$$
DELIMITER ;

SET @x = 5;
CALL sp_add10(@x);
SELECT @x;   -- 15

三种参数怎么选：只想传入用 IN；想把结果带出来用 OUT；既要用旧值又要更新后带回来，用 INOUT。

5 存储过程中的流程控制

过程体里可以用变量、IF、CASE、WHILE、LOOP、REPEAT，形成真正的程序逻辑。

5.1 变量与 IF 判断

-- 根据金额打标（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_level(IN p_amt DECIMAL(10,2), OUT p_level VARCHAR(10))
BEGIN
  IF p_amt >= 10000 THEN
    SET p_level = 'VIP';
  ELSEIF p_amt >= 1000 THEN
    SET p_level = '会员';
  ELSE
    SET p_level = '普通';
  END IF;
END$$
DELIMITER ;

CALL sp_level(5000, @lvl);
SELECT @lvl;   -- 会员

这里 SET 给变量赋值，IF ... ELSEIF ... ELSE ... END IF 做多分支判断。

5.2 循环：WHILE

-- 循环累加 1 到 100（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_sum(OUT p_total INT)
BEGIN
  DECLARE i INT DEFAULT 1;       -- 局部变量，必须声明在 BEGIN 开头
  DECLARE s INT DEFAULT 0;
  WHILE i <= 100 DO
    SET s = s + i;
    SET i = i + 1;
  END WHILE;
  SET p_total = s;
END$$
DELIMITER ;

CALL sp_sum(@t);
SELECT @t;   -- 5050

注意 DECLARE 声明的局部变量必须放在 BEGIN ... END 的最开头，这是 MySQL 的语法要求。

5.3 CASE 与错误处理

过程里也能用 CASE 做多分支，和查询里的 CASE WHEN 类似。另外，DECLARE HANDLER 可以捕获错误，让过程遇到异常时不至于直接崩溃：

-- 捕获错误并打标记（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_safe_insert(IN p_uid BIGINT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION   -- 遇到任何 SQL 错误就走这里
  BEGIN
    SELECT '插入失败，已回滚' AS err_msg;
  END;
  INSERT INTO user_info(user_id, name) VALUES(p_uid, CONCAT('u', p_uid));
  SELECT '插入成功' AS ok;
END$$
DELIMITER ;

EXIT HANDLER 表示捕获后退出当前 BEGIN...END 块。真实项目里这比让程序裸奔强得多——出错时至少有个明确提示。

6 游标CURSOR

游标（Cursor）用于"逐行处理"查询结果集。普通 SQL 是一次性处理整个结果集，游标则像指针一样一行一行往下走，适合需要对每行做不同处理的场景。

-- 用游标逐行给用户分级（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_cursor_demo()
BEGIN
  DECLARE done INT DEFAULT 0;                 -- 循环结束标志
  DECLARE v_uid BIGINT;                        -- 接收每行字段
  DECLARE v_amt DECIMAL(10,2);
  -- 声明游标：绑定要遍历的 SELECT
  DECLARE cur CURSOR FOR SELECT user_id, total_amt FROM user_stat;
  -- 当游标读完没有更多行时，把 done 置 1
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;                                    -- 打开游标
  read_loop: LOOP
    FETCH cur INTO v_uid, v_amt;               -- 取下一行
    IF done = 1 THEN
      LEAVE read_loop;                         -- 退出循环
    END IF;
    -- 这里对 v_uid / v_amt 做逐行处理
    INSERT INTO user_log(user_id, info) VALUES(v_uid, CONCAT('amt=', v_amt));
  END LOOP;
  CLOSE cur;                                   -- 关闭游标
END$$
DELIMITER ;

游标是"逐行"处理，数据量大时性能差，能集合化处理就不要用游标。它适合迁移数据、批量修复这类脚本型任务。

6.1 能集合操作就别用游标

举个对比例子：要给所有 VIP 用户的日志表补一条记录，用游标要逐行 INSERT；用一条集合 SQL 更简单更快：

-- 集合操作代替游标（MySQL 8.0）
INSERT INTO user_log(user_id, info)
SELECT user_id, CONCAT('amt=', total_amt)
FROM user_stat
WHERE total_amt >= 10000;

| 方式 | 优点 | 缺点 |

|---|---|---|

| 集合 INSERT...SELECT | 快、代码短 | 逻辑固定，无法逐行分支 |

| 游标 | 可逐行判断、灵活 | 慢、代码长 |

绝大多数"批量处理"都能用集合 SQL 完成，游标留给真正需要逐行差异化处理的场景。

7 视图与存储过程速查表

| 操作 | 语句 |

|---|---|

| 建视图 | CREATE VIEW v_xx AS SELECT ...; |

| 改视图 | CREATE OR REPLACE VIEW v_xx AS ...; |

| 删视图 | DROP VIEW IF EXISTS v_xx; |

| 建存储过程 | CREATE PROCEDURE sp_xx(参数) BEGIN ... END; |

| 调用过程 | CALL sp_xx(参数); |

| OUT 接收 | CALL sp_xx(参数, @out); SELECT @out; |

| 删过程 | DROP PROCEDURE IF EXISTS sp_xx; |

| 临时改结束符 | DELIMITER $$ ... DELIMITER ; |

| 局部变量 | DECLARE v INT DEFAULT 0;（放 BEGIN 开头） |

| 游标三步 | OPEN cur; FETCH cur INTO ...; CLOSE cur; |

8 常见坑与注意事项

1. 忘了改 DELIMITER：写存储过程时过程体里有分号，不先 DELIMITER $$ 会直接语法报错。

2. DECLARE 放错位置：局部变量必须在 BEGIN 块最开头，放后面会报错。

3. 视图里用 SELECT *：基表改了列，视图定义不会自动更新，建议视图里显式列出列名。

4. 存储过程滥用：业务逻辑塞进数据库，难调试、难版本管理、跨数据库迁移困难。简单逻辑优先放应用层。

5. 游标性能：逐行处理慢，能用 INSERT INTO ... SELECT 集合操作就别用游标。

6. 视图嵌套过深：视图套视图套视图，排查问题时层层嵌套很难懂。视图一般套一两层就够。

7. 权限安全：存储过程和视图都可能被用来隐藏敏感数据，但也要注意 DEFINER 权限问题，迁移到新库时容易因定义者账号不存在而失效。

9 练习题

练习 1：创建视图

把"每个用户的订单总数和总金额"做成一个视图 v_user_summary。

-- 用户汇总视图（MySQL 8.0）
CREATE VIEW v_user_summary AS
SELECT user_id,
       COUNT(*)  AS order_cnt,
       SUM(amount) AS total_amt
FROM orders
GROUP BY user_id;

</details>

练习 2：写一个带 IN 参数的过程

写一个过程 sp_dept_avg(dept_id)，查出该部门的平均工资。

-- 部门平均工资过程（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_dept_avg(IN p_did INT)
BEGIN
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employee
  WHERE dept_id = p_did
  GROUP BY dept_id;
END$$
DELIMITER ;

</details>

练习 3：OUT 参数

写一个过程，把某个用户的总订单金额通过 OUT 参数带出来。

-- 带出总金额（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_user_amt(IN p_uid BIGINT, OUT p_amt DECIMAL(12,2))
BEGIN
  SELECT IFNULL(SUM(amount),0) INTO p_amt
  FROM orders WHERE user_id = p_uid;
END$$
DELIMITER ;

CALL sp_user_amt(1001, @amt);
SELECT @amt;

</details>

练习 4：WHILE 循环

写一个过程，用 WHILE 计算 1~N 的平方和，结果通过 OUT 返回。

-- 平方和（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_sq_sum(IN p_n INT, OUT p_total BIGINT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE s BIGINT DEFAULT 0;
  WHILE i <= p_n DO
    SET s = s + i*i;
    SET i = i + 1;
  END WHILE;
  SET p_total = s;
END$$
DELIMITER ;

</details>

练习 5：INOUT 参数

写一个过程 sp_double(INOUT v INT)，把传入的值翻倍后存回同一个参数。

-- INOUT 翻倍（MySQL 8.0）
DELIMITER $$
CREATE PROCEDURE sp_double(INOUT v INT)
BEGIN
  SET v = v * 2;
END$$
DELIMITER ;

SET @x = 21;
CALL sp_double(@x);
SELECT @x;   -- 42

</details>

本章小结

视图和存储过程都是"封装"：视图封装查询、存储过程封装逻辑。视图适合简化报表和权限隔离，存储过程适合把重复性、脚本型的数据库操作固化下来。但要警惕过度封装——业务逻辑能放应用层就别塞进数据库，否则调试、迁移、维护都会变难。下一篇讲触发器与事件调度，它们是"自动执行"的封装。

下一篇预告：第 13 章触发器与事件调度

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 12 章视图与存储过程

目录

1 视图虚拟的表

2 视图的创建修改与删除

2.1 创建视图

2.2 修改与删除视图

2.3 视图的可更新性

2.4 视图实战：脱敏后开放

3 存储过程入门

3.1 第一个存储过程

3.2 查看与删除存储过程

4 存储过程的参数INOUT与OUT与INOUT

4.1 IN：传入参数

4.2 OUT：带回结果

4.3 INOUT：既传入又带回

5 存储过程中的流程控制

5.1 变量与 IF 判断

5.2 循环：WHILE

5.3 CASE 与错误处理

6 游标CURSOR

6.1 能集合操作就别用游标

7 视图与存储过程速查表

8 常见坑与注意事项

9 练习题

练习 1：创建视图

练习 2：写一个带 IN 参数的过程

练习 3：OUT 参数

练习 4：WHILE 循环

练习 5：INOUT 参数

本章小结

文章推广海报

文章回复

第 12 章 视图与存储过程

目录

1 视图虚拟的表

2 视图的创建修改与删除

2.1 创建视图

2.2 修改与删除视图

2.3 视图的可更新性

2.4 视图实战：脱敏后开放

3 存储过程入门

3.1 第一个存储过程

3.2 查看与删除存储过程

4 存储过程的参数INOUT与OUT与INOUT

4.1 IN：传入参数

4.2 OUT：带回结果

4.3 INOUT：既传入又带回

5 存储过程中的流程控制

5.1 变量与 IF 判断

5.2 循环：WHILE

5.3 CASE 与错误处理

6 游标CURSOR

6.1 能集合操作就别用游标

7 视图与存储过程速查表

8 常见坑与注意事项

9 练习题

练习 1：创建视图

练习 2：写一个带 IN 参数的过程

练习 3：OUT 参数

练习 4：WHILE 循环

练习 5：INOUT 参数

本章小结

第 12 章视图与存储过程