距离我真正写存储过程已经过去一周了。其实我是一个新手，看了《MySQL必知必会》之后就动手写了，算不得熟练，所以写此文也只是为了记录一下。

1. 存储过程

1.1 什么是存储过程？

一条或多条MySQL语句的组合，将其保存在数据库服务端以便复用，这就是存储过程。

1.2 为什么使用存储过程？

我遇到的场景是：开发过程中，后期给数据库追加字段，字段值来自同库其他表的字段值，几百条数据一条一条查出来再插进去太慢了，所以我选择用存储过程。

首先阿里规范是不让使用存储过程的，因为它：

  1. 调试困难。
  2. 可移植性差，特别是和代码比起来。
  3. 数据量大起来要分库分表、要扩展的时候就很无力了。
  4. 没有版本迭代……

这可能导致很多小伙伴学习MySQL语法的时候就直接跳过了视图、存储过程、游标、触发器等功能（说的就是我自己😂）。站在阿里从小公司到大型互联网企业的演变角度来看，它的规定绝对是有道理的。
但是我为什么还要使用呢？

  1. 开发库数据不重要，随便玩。
  2. 一个单库，数据量不大，没分库分表。
  3. 单纯想学习新东西和练手。

1.3 创建简单存储过程

下面是一个创建存储过程的简单样例：

  1. CREATE PROCEDURE get_id()
  2. BEGIN
  3.     SELECT id
  4.   FROM table_a;
  5. END;
  1. 在上例中，使用create procedure创建了一个名为get_id的无参存储过程。
  2. 存储过程中具体要执行的MySQL语句写在begin和end之间，可以写多条。
  3. 每条语句依旧用英文分号“;”隔开，end后也要加分号。

1.4 分隔符和删除存储过程

你把上面给出的代码拿到navicat或者sqlyog中应该八成会报语法错误。没错，我故意的😜。并不是因为你的table_a不存在，而是因为分隔符“;”把语句拆开了，无法被正确解析。
这个时候要改变分隔符，语法就是delimiter //，除“\”符号外，任何字符都可以用作新的分隔符。
不管刚刚是否创建成功，我们都把刚刚创建的存储过程删除一遍重新建。删除语法如下：

  1. -- 如果不存在会报错（不推荐用）
  2. drop procedure get_id;
  3. -- 如果不存在不会报错（推荐使用）
  4. drop procedure if exists get_id;

1.5 执行存储过程

现在重新创建，创建前后记得改变分隔符。表名和列名不存在虽然不会错，但是后面执行会报错，所以记得改你的表名和列名。

  1. drop procedure if exists get_id;
  2. delimiter //
  3. create procedure get_id()
  4. begin
  5.     select id
  6.   from table_a;
  7. end//
  8. delimiter ;

接下来使用call就可以直接使用这个存储过程了，call后跟存储过程名和括号，括号里面给存储过程需要的参数，如果没有参数可以省略。样例如下：

  1. call get_id();

1.6 参数

考虑如下表：

  1. CREATE TABLE `user` (
  2.   `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
  3.   `username` varchar(32) NOT NULL COMMENT '用户名称',
  4.   `age` int NOT NULL DEFAULT '10' COMMENT '年龄',
  5.   PRIMARY KEY (`id`) USING BTREE
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表';

引用《MySQL必知必会》的原文：

一般，存储过程并不显示结果，而是把结果返回给你指定的变量。 变量（variable）：内存中一个特定的位置，用来临时存储数据。 变量名：所有MySQL变量都必须以@开始。

给出如下存储过程：

  1. drop procedure if exists get_uname;
  2. delimiter //
  3. create procedure get_uname(
  4.     in uid bigint,
  5.     out uname varchar(32)    
  6. )
  7. begin
  8.     select id
  9.     into uid
  10.     from `user`
  11.     where username = uname;
  12. end//
  13. delimiter ;

该存储过程接收两个参数，传给存储过程的变量uid，从存储过程传出去的变量uname。

  1. MySQL支持的关键字：
    1. IN：传递给存储过程。
    2. OUT：从存储过程传出。
    3. INOUT：对存储过程传入和传出。
  2. 每个参数都必须指明类型：
    1. 就像给出的示例一样，uid除了要指明是in还是out外，还要给出列的数据类型。
  3. 通过into保存到变量中。
  4. 不允许用一个参数返回多行多列的记录集，只能使用多个参数返回多个列。

下面是使用该存储过程：

  1. call get_uname(1, @uname);
  2. select @uname;

call调用该过程， 并把id=1对应的记录的username值保存到@uname变量中，然后select @uname显示查出的结果，如下图：
图片.png
要获得多个列值，只需要都select出来就好了，变量之间用英文逗号分隔。

2. 游标

2.1 什么是游标？

书中原话：

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。 不像多数DBMS，MySQL游标只能用于存储过程（和函数）。

感觉说了和没说一样，抓重点，MySQL游标是结果集且仅能用于存储过程。

2.2 使用游标步骤和注意点

在使用游标的步骤：

  1. 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，只是定义要使用的select语句。
  2. 声明游标后，必须打开游标才能使用。这个过程才执行前面的select语句进行检索。
  3. 对于填有数据的游标，根据需要检索各行。
  4. 结束游标使用时，必须关闭游标。

有几个注意点：

  1. 声明游标后，可以根据需要频繁打开和关闭游标。
  2. 游标关闭后，必须重新打开才能使用它，但是不需要重新声明它。
  3. 如果你不明确关闭游标，MySQL会在达到END语句时自动关闭它。

2.3 创建游标

给出如下存储过程：

  1. drop procedure if exists get_results;
  2. delimiter //
  3. create procedure get_results(
  4.     out uname varchar(32),
  5.     out uage int
  6. )
  7. -- (1)这里用来写注释
  8. comment 'comment 用来写注释'
  9. begin
  10.     -- (2)定义局部变量
  11.     declare uid bigint;
  12.     declare local_uname varchar(32);
  13.     declare local_uage int;
  15.     -- (3)定义有默认值的局部变量
  16.     declare done boolean default 0;
  17.     declare rback boolean default 0;
  19.     -- (4)定义游标
  20.     declare results cursor
  21.     for
  22.     select id, username, age from `user`;
  24.     -- (5)定义继续处理句柄
  25.     declare continue handler for sqlstate '02000' set done = 1;
  26.     declare continue handler for sqlexception set rback = 1;
  28.     -- (6)创建新表，用以返回结果集
  29.     drop table if exists temp_table;
  30.     create table if not exists temp_table
  31.     (username varchar(32), age int);
  33.     -- (7)打开游标
  34.     open results;
  36.     -- (8.1)定义重复处理部分
  37.     label: repeat
  38.         -- (9)获取游标数据并插入给三个局部变量
  39.         fetch results into uid, local_uname, local_uage;
  41.         -- (10.1)关闭自动提交并开启事务
  42.         set autocommit = 0;
  43.         start transaction;
  45.         insert into temp_table(username, age)
  46.         values(local_uname, local_uage);
  48.         if (rback) then 
  49.             rollback;
  50.             leave label;
  51.         else
  52.             commit;
  53.         end if;
  54.         set autocommit = 1;
  55.         -- (10.2)事务结束
  57.         -- (11.1)if语句开始
  58.         if (uid = 1) then
  59.             select local_uname into uname;
  60.         elseif (uid = 5) then
  61.             select local_uage into uage;
  62.             alter table temp_table change username new_username varchar(32); 
  63.         -- (11.2)if语句结束
  64.         end if;
  66.     -- (8.2)重复处理结束
  67.     until done end repeat label;
  69.     -- (12)关闭游标
  70.     close results;
  72. end//
  73. delimiter ;

没有给出注释的地方都是前文讲过的了，接下来主要讲讲注释部分（序号对应）：

  1. 给这个存储过程添加注释，就像建表时一样。使用show procedure status like 'get_results';语句可以看到指定存储过程的详细信息。
  2. 定义局部变量。
    1. declare关键字可以定义局部变量、游标、句柄等。
    2. 定义三者的先后顺序是：局部变量 》游标 》句柄，不遵循会报错。
  3. 定义有默认值的局部变量。配合下面的句柄进行流程控制和事务控制。
  4. 定义游标。语法：declare 游标名 cursor for select语句。不会立刻执行select语句。
  5. 定义句柄。这里定义了两个继续处理句柄，它是在条件出现时被执行的代码。
    1. 当sqlstate '02000'出现时，就set done = 1。sqlstate '02000'是一个未找到条件，当repeat由于没有更多行供循环而不能继续时，出现这个条件。
    2. 当sqlexception出现时，就set rback = 1。 SQLEXCEPTION：不以“00”、“01”或“02”开头的 SQLSTATE 值类的简写，出现异常时出现这个条件。
    3. 关于句柄给出官方文档链接：https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
  6. 创建新表，用以返回结果集。存储过程中可以创建表。
  7. 打开游标，会执行定义游标时的语句，得到结果集。

  8. 定义重复处理部分。语法规则：

    [begin_label:] REPEAT
 statement_list
UNTIL search_condition
END REPEAT [end_label]
    1. repeat语句中的语句列表会重复，直到search_condition表达式为真。因此， 一个REPEAT总是至少进入循环一次。 statement_list由一个或多个语句组成，每个语句以分号 ( ;) 语句分隔符结束。
    2. REPEAT官方文档地址：https://dev.mysql.com/doc/refman/8.0/en/repeat.html
    3. 开始标签后必跟冒号。
    4. 开始标签可以不给结束标签，但是如果结束标签存在，必与开始标签同名。
    5. 标签最长可达16个字符
    6. LABEL官方文档地址：https://dev.mysql.com/doc/refman/8.0/en/statement-labels.html
  9. 获取游标数据。使用fetch获取游标时，游标必须被open打开。
  10. 这里是事务。
    1. 关闭自动提交。
    2. 开启事务。
    3. 插入表，执行一组增删改操作。
    4. 执行成功提交事务，执行失败回滚事务。这里回滚时会退出循环标签。
    5. 开启自动提交。
  11. if语句。if语句支持elseif和else子句，前者使用then子句，后者不使用。这里都给出了样例。
  12. 关闭游标，推荐手动关闭释放资源，即使不关闭到END语句也会自动关闭。

2.4 使用游标

给出例子数据库数据如下：
图片.png
使用如下语句调用存储过程、获取参数值、查看临时表的数据。

-- 调用存储过程
call get_results(@uname, @uage);
-- 查看参数值
select @uname, @uage;
-- 查看临时表数据
select * from temp_table;

得到结果如下：
图片.png
图片.png

2.5 分析结果

  1. 调用存储过程之后再查看参数值，确实是拿到的id=1的username和id=5的age值。
  2. 在id=5时，我们改变了临时表的username字段名为new_username，接下来的id=6时的insert语句肯定出错，然后出现sqlexception，接着继续执行，但是会set rback = 1，（boolean值指定非零都为真，只有0被视为假），这时就会走回滚操作，然后退出标签，剩下的就不执行了。因此出现五条数据。
  3. 如果把存储过程中做如下修改，会出现7条数据，修改部分如下：
    if (rback) then 
 rollback;
 -- leave label;这里做了修改
 alter table temp_table change new_username username varchar(32); 
 set rback = 0;
 -- 不管这里是否commit结果都很诡异
 -- commit;
else
 commit;
 end if;
    虽然是七条数据，但是结果却很诡异，参数的值还是和上面一样，但是临时表数据：
    图片.png
    插入了两条id=7的数据，这是为什么呢？实际上是最后一条数据总是会被插入两次，可是书上给的例子写法也差不多，却不会插入两次最后一条数据，难道是mysql8.0.27版本的问题？

2.6 思考与讨论

如果是你来建立这个事务，你会选择把整个repeat部分都包起来做长事务呢？还是像我这样用短事务？为什么？

3. 使用心得

  1. 合理利用参数，避免联表查询操作。
  2. 先尝试分别写出多个单条sql，保证每条都正确。然后逐渐利用变量和参数代替，最后进行组装。
  3. 对于单个库下面的几张表有用，不同的库不能用存储过程。
  4. 日常开发写业务代码千万别用存储过程，真的没有可移植性和可扩展性。