Mysql存储过程必知必会

存储过程

为什么使用存储过程

• 通过把 “某个处理” 封装在一个易用的单元中，可以简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道 这些变化。
• 因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令所需的工作量少，提高了性能。

简单来说，使用存储过程有三个主要的好处：简单、安全、高性能。

创建存储过程

我们来看一个简单的存储过程例子，对 草帽海贼团 人数进行计数。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE personCount()
    -> BEGIN
    ->     SELECT COUNT(*) AS num_person
    ->     FROM one_piece
    ->     WHERE pirates = '草帽海贼团';
    -> END$$

在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为 $$，使得过程体中使用的分号被直接传递到服务器，而不会被 Mysql 解释。

使用 CALL 存储过程名(参数) 来调用存储过程。

mysql> DELIMITER ;
mysql> CALL personCount();

存储过程体

存储过程体包含了在过程调用时必须执行的语句，例如：dml、ddl 语句，if-then-else 和 while-do 语句、声明变量的 declare 语句等。

过程体格式：以 begin 开始，以 end 结束(可嵌套)

BEGIN
　　BEGIN
　　　　BEGIN
　　　　　　statements; 
　　　　END
　　END
END

注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的 begin-end 块(又叫做复合语句 compound statement )，则不需要分号。

存储过程参数

存储过程可以有0个或多个参数，用于存储过程的定义。

3种参数类型：

• IN (输入参数)：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT (输出参数)：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT (输入输出参数)：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

IN输入参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE in_test(IN p_in INT)
    -> BEGIN
    ->     SELECT p_in;    -- 第一次查询
    ->     SET p_in = 2;   -- 修改p_in的值
    ->     SELECT p_in;    -- 第二次查询
    -> END$$
    
mysql> DELIMITER ;

mysql> SET @p_in = 1;

mysql> CALL in_test(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+

+------+
| p_in |
+------+
|    2 |
+------+

mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+

虽然 p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为 in_test 只接受输入参数，并不输出参数，所以相当于在函数内改变，但并未将这个值输出给 @p_in 。

OUT输入参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE out_test(OUT p_out INT)
    -> BEGIN
    ->     SELECT p_out;    -- 第一次查询
    ->     SET p_out = 2;   -- 修改p_out的值
    ->     SELECT p_out;    -- 第二次查询
    -> END$$

mysql> DELIMITER ;

mysql> SET @p_out = 1;

mysql> CALL out_test(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+

+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> SELECT @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

第一个返回结果为 NULL 是因为 OUT 是向调用者输出参数，不接收输入的参数，所以第一次查询时 p_out 还未赋值，所以是 NULL 。最后 @p_out 变量的值变为2是因为调用了 out_test 存储过程，输出参数，改变了 p_out 变量的值。

INOUT输入输出参数

mysql> DELIMITER $$
mysql> CREATE PROCEDURE inout_test(INOUT p_inout INT)
    -> BEGIN
    ->     SELECT p_inout;    -- 第一次查询
    ->     SET p_inout = 2;   -- 修改p_inout的值
    ->     SELECT p_inout;    -- 第一次查询
    -> END$$
    
mysql> DELIMITER ;

mysql> SET @p_inout = 1;

mysql> CALL inout_test(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+

+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> SELECT @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+

调用 inout_test 存储过程，既接受了输入的参数，也输出参数， @p_inout 的值被改变。

删除存储过程

使用 DROP PROCEDURE 来删除存储过程。

mysql> DROP PROCEDURE in_test;

这就是今天要分享的内容，微信搜 Python新视野，每天带你了解更多有用的知识。