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 语句的一部分出现
