存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。存储过程可以说是由 SQL 语句和流控制语句构成的语句集合，它和我们之前学到的函数一样，可以接收输入参数，也可以返回输出参数给调用者，返回计算结果。

存储过程像是函数.

什么是存储过程，如何创建一个存储过程

定义一个存储过程：

  1. CREATE PROCEDURE 存储过程名称([参数列表])
  2. BEGIN
  3.     需要执行的语句
  4. END

删除已经创建的存储过程:

  1. DROP PROCEDURE

更新存储过程:

  1. ALTER PROCEDURE

实现一个简单的存储过程:

  1. CREATE PROCEDURE `add_num`(IN n INT)
  2. BEGIN
  3.        DECLARE i INT;
  4.        DECLARE sum INT;
  6.        SET i = 1;
  7.        SET sum = 0;
  8.        WHILE i <= n DO
  9.               SET sum = sum + i;
  10.               SET i = i +1;
  11.        END WHILE;
  12.        SELECT sum; -- 在调用的时候会打印这个值
  13. END
  15. mysql> call study_stored_procedure(50);
  16. +------+
  17. | sum  |
  18. +------+
  19. | 1275 |
  20. +------+
  21. 1 row in set (0.00 sec)
  23. Query OK, 0 rows affected (0.00 sec)

使用这个存储过程:

  1. CALL add_num(50);

DELIMITER

如果你使用 Navicat 这个工具来管理 MySQL 执行存储过程，那么直接执行上面这段代码就可以了。如果用的是 MySQL，你还需要用 DELIMITER 来临时定义新的结束符。因为默认情况下 SQL 采用（；）作为结束符，这样当存储过程中的每一句 SQL 结束之后，采用（；）作为结束符，就相当于告诉 SQL 可以执行这一句了。但是存储过程是一个整体，我们不希望 SQL 逐条执行，而是采用存储过程整段执行的方式，因此我们就需要临时定义新的 DELIMITER，新的结束符可以用（//）或者（$$）。如果你用的是 MySQL，那么上面这段代码，应该写成下面这样：

  1. DELIMITER //
  2. CREATE PROCEDURE `add_num`(IN n INT)
  3. BEGIN
  4.        DECLARE i INT;
  5.        DECLARE sum INT;
  7.        SET i = 1;
  8.        SET sum = 0;
  9.        WHILE i <= n DO
  10.               SET sum = sum + i;
  11.               SET i = i +1;
  12.        END WHILE;
  13.        SELECT sum;
  14. END //
  15. DELIMITER ;

存储过程的 3 种参数类型

image.png

IN 参数必须在调用存储过程时指定，而在存储过程中修改该参数的值不能被返回。而 OUT 参数和 INOUT 参数可以在存储过程中被改变，并可返回。
**

  1. CREATE PROCEDURE `get_hero_scores`(
  2.        OUT max_max_hp FLOAT,
  3.        OUT min_max_mp FLOAT,
  4.        OUT avg_max_attack FLOAT,  
  5.        s VARCHAR(255)
  6.        )
  7. BEGIN
  8.        SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
  9. END

调用:

  • 调用的时候需要在变量前面加 @ , 否则报错
  1. CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
  2. SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

流控制语句

  1. BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
  2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
  3. SET：赋值语句，用于对变量进行赋值。
  4. SELECT…INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
  5. IF…THEN…ENDIF：条件判断语句，我们还可以在 IF…THEN…ENDIF 中使用 ELSE 和 ELSEIF 来进行条件判断。
  6. CASE：CASE 语句用于多条件的分支判断，使用的语法是下面这样的。
  7. LOOP、LEAVE 和 ITERATE：LOOP 是循环语句，使用 LEAVE 可以跳出循环，使用 ITERATE 则可以进入下一次循环。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 BREAK，把 ITERATE 理解为 CONTINUE。
  8. REPEAT…UNTIL…END REPEAT：这是一个循环语句，首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。
  9. WHILE…DO…END WHILE：这也是循环语句，和 REPEAT 循环不同的是，这个语句需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环。
  1. CASE 
  2.   WHEN expression1 THEN ...
  3.   WHEN expression2 THEN ...
  4.   ...
  5.     ELSE 
  6.     --ELSE语句可以加，也可以不加。加的话代表的所有条件都不满足时采用的方式。
  7. END

关于存储过程使用的争议

存储过程有很多好处:

  • 存储过程可以一次编译多次使用
  • 存储过程的安全性强
  • 可以减少网络传输量

缺点也是很明显的:

  • 可移植性差
  • 调试困难
  • 版本管理也很困难
  • 不适合高并发的场景

image.png