MySQL 存储过程（八）

8、mysql 存储过程

8.1、存储过程简介

存储过程是 SQL 语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理，优点：

增强 SQL 语句的功能和灵活性
实现较快的执行速度
减少网络流量

存储过程可以减少 SQL 语句的编译时间，第一次执行完整的流程。等下一次再执行相同的 SQL 语句时直接从内存中调出 “存储流程即可”，减少了再编译时间。

graph LR A[SQL 命令] -->B(MYSQL 引擎) B(MYSQL 引擎) -->|分析| C[语法正确] C[语法正确] --> D[可识别命令] D[可识别命令] --> |执行| E[执行结果] E[执行结果] --> |返回| F[客户端]

8.2、存储过程语法结构解析

语法

CREATE
[DEFINER = {user | CURRENT_USER }]  /*definer 为创建者，省略了为当前登录mysql客户端的用户*/
PROCEDURE sp_name ([proc_parameter[,...]])  /*sp_name存储过程名字，后面可以跟三个参数*/
[characteristic ...] routine_body  /*过程体*/

proc_parameter:   /*sp_name参数*/
[ IN | out | INOUT ] param_name type

/*
IN：表示该参数的值必须在调用存储过程时指定（输入）
OUT：表示该参数的值可以被存储过程改变，并且可以返回（输出）
INPUT：表示该参数的调用时指定，并且可以被改变和返回
*/
DROP PROCEDURE sp_name         /*删除存储过程*/

特性

COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}

/*
COMMENT：注释
CONTAINS SQL：包含 sql 语句，但不包含读或写数据的语句
NO SQL ：不包含 sql 语句
READS SQL DATA：包含读数据的语句
MODIFIES SQL DATA：包含写数据的语句
SQL SECURITY {DEFINER | INVOKER} ：指明谁有权限来执行
*/

过程体

过程体由合法的 SQL 语句构成
过程体可以是任意 SQL 语句
如果为复合结构则使用 BEGIN..END 语句
复合结构可以包含声明（变量）、循环和控制结构

8.3、创建不带参数的存储过程

调用存储过程语法

CALL sp_name([parameter[,...]])    /*存储过程在封装时，不带参数小括号可省略，否则必须带有小括号*/
CALL sp_name[()]

创建并调用存储过程示例

mysql> CREATE PROCEDURE sp1() SELECT VERSION();   /*创建不带参数的存储过程*/
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp1;                   /*调用*/
+------------+
| VERSION()  |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp1();                           /*调用*/
+------------+
| VERSION()  |
+------------+
| 5.7.20-log |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

8.4、创建带有 IN 类型的参数的存储过程

修改存储过程语法

/*只能修改注释、类型结构等，不能修改过程体（除非删除存储过程再重建）*/
ALTER PROCEDURE sp_name [characteristic...]
COMMENT 'string'
| {CONTAINS SQL | NOT SQL | READS SQL DATA | MODIFIES SQL DATA}
|SQL SECURITY {DEFINE | INVOKER}

示例

# 将删除一个数据表的记录的过程封装为一个存储过程
mysql> select * from users;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | B        | A        |   23 | 0    |
|  2 | C        | A        |   23 | 0    |
|  3 | J        | A        |   22 | 0    |
|  4 | K        | A        |   22 | 0    |
|  5 | M        | A        |   24 | 0    |
|  6 | P        | A        |   20 | 0    |
|  7 | Q        | A        |   24 | 0    |
|  8 | R        | A        |   24 | 0    |
|  9 | D        | A        |   24 | 0    |
+----+----------+----------+------+------+
9 rows in set (0.00 sec)

mysql> delimiter //
mysql> create procedure removeUserById(IN p_id int unsigned)   # in 参数，参数名为 p_id，类型为 int unsigned
    -> begin
    -> delete from users where id = p_id;
    -> end
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call removeUserById(9);      # 调用存储过程
Query OK, 1 row affected (0.02 sec)

mysql> select * from users;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | B        | A        |   23 | 0    |
|  2 | C        | A        |   23 | 0    |
|  3 | J        | A        |   22 | 0    |
|  4 | K        | A        |   22 | 0    |
|  5 | M        | A        |   24 | 0    |
|  6 | P        | A        |   20 | 0    |
|  7 | Q        | A        |   24 | 0    |
|  8 | R        | A        |   24 | 0    |
+----+----------+----------+------+------+
8 rows in set (0.00 sec)

8.5、创建带有 IN 和 OUT 类型的参数的存储过程

用户变量：以"@"开始，形式为"@变量名"

用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效

全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.变量名

对所有客户端生效。只有具有super权限才可以设置全局变量

会话变量：只对连接的客户端有效。
局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量

declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

# 创建带有 IN 和 OUT 类型的参数的存储过程
mysql> select * from users;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | B        | A        |   23 | 0    |
|  2 | C        | A        |   23 | 0    |
|  3 | J        | A        |   22 | 0    |
|  4 | K        | A        |   22 | 0    |
|  5 | M        | A        |   24 | 0    |
|  6 | P        | A        |   20 | 0    |
|  7 | Q        | A        |   24 | 0    |
|  8 | R        | A        |   24 | 0    |
+----+----------+----------+------+------+
8 rows in set (0.00 sec)

mysql> DELIMITER //      # userNums为 out 参数的返回值（是一个变量，没有固定值）
mysql> CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id = p_id;
    -> SELECT count(id) FROM users INTO userNums;  # userNums 为局部变量（将countary（id）的返回值传递给 userNums,使用 into）
    -> END
    -> //
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> SELECT COUNT(id) FROM users;      
+-----------+
| COUNT(id) |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)

mysql> CALL removeUserAndReturnUserNums(8,@nums);  # @nums 为用户变量
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @nums;     # 返回值
+-------+
| @nums |
+-------+
|     7 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users;
+----+----------+----------+------+------+
| id | username | password | age  | sex  |
+----+----------+----------+------+------+
|  1 | B        | A        |   23 | 0    |
|  2 | C        | A        |   23 | 0    |
|  3 | J        | A        |   22 | 0    |
|  4 | K        | A        |   22 | 0    |
|  5 | M        | A        |   24 | 0    |
|  6 | P        | A        |   20 | 0    |
|  7 | Q        | A        |   24 | 0    |
+----+----------+----------+------+------+
7 rows in set (0.00 sec)

8.6、创建带有多个 OUT 类型参数的存储过程

ROW_COUNT（）函数

ROW_COUNT()   # 这个函数记录的是被插入/更新/删除的记录的总数

mysql> INSERT tb_4(username,age) VALUES('john',20),('rose',19),('lila',24);  # 插入三条记录
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT ROW_COUNT();        # 记录插入记录的总数
+-------------+
| ROW_COUNT() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> update tb_4 set username = concat(username,'--imooc') where id <= 2;  # 更新两条记录
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM tb_4;
+----+-------------+------+
| id | username    | age  |
+----+-------------+------+
|  1 | john--imooc |   20 |
|  2 | rose--imooc |   19 |
|  3 | lila        |   24 |
+----+-------------+------+
3 rows in set (0.00 sec)

mysql> select row_count();            # 记录更新记录的总数
+-------------+
| row_count() |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

创建带有多个 OUT 类型参数的存储过程

mysql> DELIMITER //         # 将定界符修改为 //
mysql> CREATE PROCEDURE removeUserByAgeReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUser SMALLINT UNSIGNED,OUT userCounts SMALLINT UNSIGNED)  # 第一个参数为要删除的年龄，第二个（deleteUser）为被删除的记录总数，第三个（userCounts）为剩余的记录总数。
    -> BEGIN
    -> DELETE FROM tb_4 WHERE age = p_age;
    -> SELECT ROW_COUNT() INTO deleteUser;
    -> SELECT COUNT(id) FROM tb_4 INTO userCounts;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> SELECT * FROM tb_4;       # tb_4 中有三条记录
+----+-------------+------+
| id | username    | age  |
+----+-------------+------+
|  1 | john--imooc |   20 |
|  2 | rose--imooc |   19 |
|  3 | lila        |   24 |
+----+-------------+------+
3 rows in set (0.00 sec)

mysql> CALL removeUserByAgeReturnInfos(20,@a,@b);  # 调用存储过程，删除年龄为20的记录，@a、@b分别为被删除记录总数和剩余记录总数
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @a,@b;    
+------+------+
| @a   | @b   |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

8.7、存储过程与自定义函数的区别

实际开发中，自定义函数一般很少使用，而存储过程则恰好相反
存储过程功能复杂些，而函数则针对性更强
存储过程可以有多个返回值，而函数只有一个
、存储过程一般独立的来执行，而函数可以作为其他 SQL 语句的一部分出现