定义

  • 存储在数据库端的一组 SQL 语句集。
  • 用户可以通过存储过程名和传参多次调用的程序模块。
  • 存储过程的特点：
    • 使用灵活，可以使用流控语句、自定义变量等完成复杂的业务逻辑。
    • 提高数据安全性，屏蔽应用程序直接对表的操作，易于进行审计。
    • 减少网络传输。
    • 提高代码维护的复杂度，实际使用需要结合业务评估。

语法

  1. CREATE
  2.     [DEFINER = { user | CURRENT_USER }]
  3.     PROCEDURE sp_name ([proc_parameter[,...]])
  4.     [characteristic ...] routine_body
  6. proc_parameter:
  7.     [ IN | OUT | INOUT ] param_name type 
  9. characteristic:
  10.     COMMENT 'string'
  11.   | LANGUAGE SQL
  12.   | [NOT] DETERMINISTIC
  13.   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  14.   | SQL SECURITY { DEFINER | INVOKER }
  16. routine_body:
  17.     Valid SQL routine statement
  19. -- 删除
  20. DROP PROCEDURE  procedure_name;

:::warning 注意：只有 PROCEDURE 才有 IN（传入）、OUT（传出）、INOUT（传入传出）参数，自定义函数（只有）默认就是 IN。 :::

流程控制语句

官方文档：https://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html

  1. --
  2. -- IF
  3. --
  4. -- 语法
  5. IF search_condition THEN statement_list
  6.     [ELSEIF search_condition THEN statement_list] ...
  7.     [ELSE statement_list]
  8. END IF
  10. -- 例子
  11. mysql> delimiter //
  12. mysql> create procedure pcd_test_1 (in param_a int) -- 创建一个
  13.     -> begin
  14.     -> declare a int;  -- delcare声明了该变量的作用域在该procedure中
  15.     ->     if param_a > 10 then set a:=11;        
  16.     ->         elseif param_a = 10 then set a:=10;
  17.     ->         else set a:=9;
  18.     ->     end if;
  19.     -> end;//
  20. Query OK, 0 rows affected (0.01 sec)
  22. mysql> select @a;  -- 查看当前会话中变量a的值
  23. +------+
  24. | @a   |
  25. +------+
  26. | NULL |  -- 当前会话中a为NULL
  27. +------+
  28. 1 row in set (0.00 sec)
  30. mysql> call pcd_test_1(1);
  31. +------+
  32. | a    |
  33. +------+
  34. |    9 |
  35. +------+
  36. 1 row in set (0.00 sec)
  38. Query OK, 0 rows affected (0.00 sec)
  40. mysql> call pcd_test_1(10);
  41. +------+
  42. | a    |
  43. +------+
  44. |   10 |
  45. +------+
  46. 1 row in set (0.00 sec)
  48. Query OK, 0 rows affected (0.00 sec)
  50. mysql> call pcd_test_1(20);
  51. +------+
  52. | a    |
  53. +------+
  54. |   11 |
  55. +------+
  56. 1 row in set (0.00 sec)
  58. Query OK, 0 rows affected (0.00 sec)
  60. mysql> select @a;
  61. +------+
  62. | @a   |
  63. +------+
  64. | NULL | -- 使用了declare，使得procedure中a的作用域限制在了procedure内
  65. +------+
  66. 1 row in set (0.00 sec)
  68. --
  69. -- CASE WHEN
  70. --
  71. -- CASE WHEN 语法
  72. CASE case_value
  73.     WHEN when_value THEN statement_list
  74.     [WHEN when_value THEN statement_list] ...
  75.     [ELSE statement_list]
  76. END CASE
  77. -- 或者是
  78. CASE
  79.     WHEN search_condition THEN statement_list
  80.     [WHEN search_condition THEN statement_list] ...
  81.     [ELSE statement_list]
  82. END CASE
  84. --
  85. -- CASE WHEN 例子
  86. --
  87. mysql> delimiter //
  88. mysql> 
  89. mysql> create procedure pcd_test_2(in param_1 int)
  90.     ->   begin
  91.     ->     case param_1  
  92.     -- 当case后面有value时，该value会和when中的when_value进行"="判断
  93.     -- 相等则执行then后面的语句，然后跳出；否则就进行下一次when的匹配
  94.     ->       when 2 then select 200;
  95.     ->       when 3 then select 300;
  96.     ->       else
  97.     ->         begin  
  98.                     -- 当没有匹配时，且else中没有要执行的语句
  99.                     -- 则给一个begin/end的空语句；
  100.                     -- 或者不写else语句；
  101.     ->         end;
  102.     ->     end case;
  103.     -> end;//
  104. Query OK, 0 rows affected (0.03 sec)
  106. mysql> delimiter ;
  107. mysql> call pcd_test_2(1);
  108. Query OK, 0 rows affected (0.00 sec)
  110. mysql> call pcd_test_2(2);
  111. +-----+
  112. | 200 |
  113. +-----+
  114. | 200 |
  115. +-----+
  116. 1 row in set (0.00 sec)
  118. Query OK, 0 rows affected (0.00 sec)
  120. mysql> call pcd_test_2(3);
  121. +-----+
  122. | 300 |
  123. +-----+
  124. | 300 |
  125. +-----+
  126. 1 row in set (0.00 sec)
  128. Query OK, 0 rows affected (0.00 sec)
  130. -- 另外一种SQL语法请参考rank排名作业；注意when后跟的是condition
  132. --
  133. -- WHILE  循环
  134. --
  135. -- WHILE 语法
  136. [begin_label:] WHILE search_condition DO
  137.     statement_list
  138. END WHILE [end_label]
  140. -- WHILE举例
  141. mysql> delimiter //
  142. mysql> 
  143. mysql> create procedure pcd_test_3(in param_1 int)
  144.     -> begin
  145.     ->     declare a int default 1;
  146.     ->     while param_1 > 10 do
  147.     ->         set param_1 = param_1 - 1;
  148.     ->         set a = a + 1;
  149.     ->     end while;
  150.     ->     select a;
  151.     -> end;//
  153. Query OK, 0 rows affected (0.01 sec)
  154. mysql> delimiter ;
  156. mysql> call pcd_test_3(15); -- 15 - 10 = 5；需要5次循环
  157. +------+
  158. | a    |
  159. +------+
  160. |    6 |  -- a + 5 = 6
  161. +------+
  162. 1 row in set (0.00 sec)
  164. Query OK, 0 rows affected (0.00 sec)
  166. --
  167. -- REPEAT   循环
  168. --
  169. -- REPEAT 语法
  170. [begin_label:] REPEAT
  171.     statement_list
  172. UNTIL search_condition
  173. END REPEAT [end_label]
  175. mysql> delimiter //
  176. mysql> create procedure pcd_test_4(in param_1 int)
  177.     -> begin
  178.     ->     SET @x = 0;  -- 没有使用declare，所以x是会话级别的
  179.     ->     REPEAT
  180.     ->         SET @x = @x + 1;
  181.     ->     UNTIL @x > param_1 END REPEAT;
  182.     -> end;//
  183. Query OK, 0 rows affected (0.01 sec)
  184. mysql> delimiter ;
  186. mysql> call pcd_test_4(10);
  187. Query OK, 0 rows affected (0.00 sec)
  189. mysql> select @x; -- x是会话级别的
  190. +------+
  191. | @x   |
  192. +------+
  193. |   11 |  -- 一共循环11次(10>10 为False，11 > 10为True，才跳出)
  194. +------+
  195. 1 row in set (0.00 sec)
  197. --
  198. -- loop 循环
  199. --
  200. -- loop语法
  201. [begin_label:] LOOP
  202.     statement_list
  203. END LOOP [end_label]
  205. -- ITERATE 和label相结合，表示继续从label处执行
  206. -- LEAVE   和label相结合，表示从label 标记的代码段离开
  208. -- loop 例子
  209. mysql> delimiter //
  210. mysql> create procedure pcd_test_5(in param_1 int)
  211.     -> begin
  212.     ->     test_label: loop
  213.     ->         set param_1 := param_1 + 1; -- 参数累加
  214.     ->         if param_1 < 10 then        -- 如果累加的值小于10
  215.     ->             iterate test_label;     -- 继续执行 标签 test_label
  216.     ->         end if;
  217.     ->         leave test_label;  -- 如果>=10则离开这个test_label(loop)
  218.     ->     end loop test_label; 
  219.     ->     set @x = param_1; -- 设置会话级别的变量 
  220.     -> end;//
  222. Query OK, 0 rows affected (0.02 sec)
  223. mysql> delimiter ;
  225. mysql> call pcd_test_5(5); -- 5<10 ，累加5次后>=10为true，离开循环
  226. Query OK, 0 rows affected (0.00 sec)
  228. mysql> select @x;
  229. +------+
  230. | @x   |
  231. +------+
  232. |   10 |  -- 累加到10的 param_1 赋值给 x, 即为10
  233. +------+
  234. 1 row in set (0.00 sec)
  236. -- 老师给出的例子， 阶乘
  237. mysql> create table test_proc_1(a int, b int); -- 给一个存放数据的表
  238. Query OK, 0 rows affected (0.15 sec)
  240. mysql> delimiter //
  241. mysql> create procedure proc_test1(in total int, out res int)
  242.     -> begin
  243.     ->     declare i int;
  244.     ->     set i := 1;
  245.     ->     set res := 1;
  246.     ->     if total <= 0 then
  247.     ->         set total := 1;
  248.     ->     end if;
  249.     ->     while i <= total do
  250.     ->         set res := res * i;
  251.     ->         insert into test_proc_1 values(i, res);
  252.     ->         set i := i + 1;
  253.     ->     end while;
  254.     -> end;//
  255. Query OK, 0 rows affected (0.01 sec)
  256. mysql> delimiter ;
  258. mysql> set @res_value := 0;
  259. Query OK, 0 rows affected (0.00 sec)
  261. mysql> call proc_test1(5, @res_value); -- 因为res是out变量，要预先有这个变量，这里上面设置了res_value(实参和形参不必同名)
  262. Query OK, 1 row affected (0.15 sec)
  264. mysql> select @res_value;
  265. +------------+
  266. | @res_value |
  267. +------------+
  268. |        120 | -- 5的阶乘的结果是120
  269. +------------+
  270. 1 row in set (0.00 sec)
  272. mysql> select * from test_proc_1;
  273. +------+------+
  274. | a    | b    |
  275. +------+------+
  276. |    1 |    1 |
  277. |    2 |    2 |
  278. |    3 |    6 |
  279. |    4 |   24 |
  280. |    5 |  120 |  -- 每次insert的结果
  281. +------+------+
  282. 5 rows in set (0.00 sec)

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/wgmill 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。