MySQL 存储过程

• MySQL 语句执行流程
  

一、存储过程

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

• 存储过程的优点

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

• 创建存储过程

CREATE
[ DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([ proc_parameter [, …] ])
[ characteristic …] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

1. DEFINER: 指明创建者，省略默认为当前登录用户

2. sp_name: 存储过程的名字，可以带零个或多个参数

3. proc_parameter: 参数的写法

4. IN: 表示该参数的值必须在调用存储过程时指定

5. OUT: 表示该参数的值可以被存储过程改变，并且可以返回

6. INOUT: 表示该参数的调用时指定，并且可以被改变和返回

7. characteristic: 存储过程的特性

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

8. routine_body: 过程体

1. 过程体由合法的SQL语句构成；
2. 过程体可以是任意SQL语句(增、删、改、查、连接)；
3. 过程体如果为复合结构则使用BEGIN……END语句；
4. 复合结构可以包含声明，循环，控制结构；

二、创建不带参的存储过程

• 创建一个简单的存储过程

mysql> CREATE PROCEDURE sp1()
    -> SELECT VERSION();
Query OK, 0 rows affected (0.04 sec)

调用存储过程

CALL sp_name([ parameter[, …] ])
CALL sp_name[()]

对于不带参的存储过程，调用时带不带小括号都可以。

mysql> CALL sp1;
+-----------+
| VERSION() |
+-----------+
| 5.5.37    |
+-----------+
1 row in set (0.00 sec)

mysql> CALL sp1();
+-----------+
| VERSION() |
+-----------+
| 5.5.37    |
+-----------+
1 row in set (0.00 sec)

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

• 创建测试的数据表

mysql> CREATE TABLE  users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL ,
    -> password VARCHAR(40) NOT NULL,
    -> age TINYINT UNSIGNED NOT NULL,
    -> sex BOOLEAN DEFAULT FALSE
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> DESC users;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
| password | varchar(40)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | NULL    |                |
| sex      | tinyint(1)           | YES  |     | 0       |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

• 写入数据

INSERT users(username, password, age, sex) VALUES('A', MD5('A'), 14, true);
INSERT users(username, password, age, sex) VALUES('B', MD5('B'), 11, true);
INSERT users(username, password, age, sex) VALUES('C', MD5('C'), 13, false);
INSERT users(username, password, age, sex) VALUES('D', MD5('D'), 16, true);
INSERT users(username, password, age, sex) VALUES('E', MD5('E'), 15, false);

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  14 |    1 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |  11 |    1 |
|  3 | C        | 0d61f8370cad1d412f80b84d143e1257 |  13 |    0 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  16 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  15 |    0 |
+----+----------+----------------------------------+-----+------+

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

  注意传入存储过程的参数，不要和列名重复，避免意外的结果

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id = p_id;
    -> END
    -> //
Query OK, 0 rows affected (0.09 sec)

• 执行存储过程

mysql> DELIMITER ;
mysql> CALL removeUserById(3);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  14 |    1 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |  11 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  16 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  15 |    0 |
+----+----------+----------------------------------+-----+------+
4 rows in set (0.00 sec)

修改存储过程

只能修改 characteristic , 如注释等，不能修改存储过程的其他内容。

ALTER PROCEDURE sp_name [ characteristic …]
COMMENT ‘string’
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

删除存储过程

DROP PROCEDURE [ IF EXISTS ] sp name

mysql> DROP PROCEDURE removeUserById;
Query OK, 0 rows affected (0.04 sec)

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

创建一个存储过程：完成删除一行记录并返回剩余行的总数。

mysql> DELIMITER //
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;
    -> END
    -> //
Query OK, 0 rows affected (0.04 sec)

• 调用存储过程并查看结果

mysql> DELIMITER ;
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  14 |    1 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |  11 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |  16 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  15 |    0 |
+----+----------+----------------------------------+-----+------+
4 rows in set (0.00 sec)

mysql> CALL removeUserAndReturnUserNums(4, @nums);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT @nums;
+-------+
| @nums |
+-------+
|     3 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  14 |    1 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |  11 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  15 |    0 |
+----+----------+----------------------------------+-----+------+
3 rows in set (0.00 sec)

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

函数 ROW_COUNT()

​ 返回修改（插入、更新、删除）数据的总数。

mysql> INSERT users (username, password, age) VALUES ('X', MD5('X'), 21), ('Y',MD5('Y'), 23), ('Z', MD5('Z'), 25);
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> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  14 |    1 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |  11 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  15 |    0 |
|  6 | X        | 02129bb861061d1a052c592e2dc6b383 |  21 |    0 |
|  7 | Y        | 57cec4137b614c87cb4e24a3d003a3e0 |  23 |    0 |
|  8 | Z        | 21c2e59531c8710156d34a3c30ac81d5 |  25 |    0 |
+----+----------+----------------------------------+-----+------+
6 rows in set (0.00 sec)

• 创建存储过程：根据年龄来删除用户，并返回删除的用户数和剩余的用户数

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserByAgeReturnInfos
    -> (IN p_age SMALLINT UNSIGNED,
    -> OUT deleteUsers SMALLINT UNSIGNED,
    -> OUT userCounts SMALLINT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE age > p_age;
    -> SELECT ROW_COUNT() INTO deleteUsers;
    -> SELECT COUNT(id) FROM users INTO userCounts;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

• 调用存储过程

mysql> DELIMITER ;
mysql> CALL removeUserByAgeReturnInfos(20, @a, @b);
Query OK, 1 row affected (0.01 sec)

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

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |  14 |    1 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |  11 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |  15 |    0 |
+----+----------+----------------------------------+-----+------+
3 rows in set (0.00 sec)

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

1. 存储过程实现的功能要复杂一些；而函数的针对性更强；
2. 存储过程可以返回多个值；函数只能有一个返回值；
3. 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。

七、用户变量、系统变量、局部变量

1、用户变量

1. 定义

以"@“开始，形式为”@变量名"

注：用户变量跟mysql客户端是绑定的，设置的变量只对当前用户使用的客户端生效。也叫回话变量。

2. 赋值及查看

mysql> SET @test = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @test;
+-------+
| @test |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT @test2, @test3 = 2;
+--------+------------+
| @test2 | @test3 = 2 |
+--------+------------+
| NULL   |       NULL |
+--------+------------+
1 row in set (0.00 sec)

注1：如果使用没有初始化的变量，其值是NULL。

注2：使用set赋值时，使用的是“=”，使用select赋值时使用的是“:="。

2、系统变量

MySQL可以设置许多系统变量，当服务器启动时，会将所有全局变量初始化为默认值，并允许服务器运行期间动态修改，但需要具有SUPER权限。

• 修改系统变量

// 方式1
mysql>  SET GLOBAL wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

//方式2（推荐）
mysql> SET @@GLOBAL.wait_timeout = 28800;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

3、局部变量

设置在begin到end语句块之间，且作用范围也在begin到end内的变量。

1. 定义
   declare语句专门用于定义局部变量。

BEGIN
DECLACE test INT UNSIGNED;
…
END

2. 赋值

   set语句进行赋值。

SET test = 200;