存储过程

image.png
image.png
image.png
image.png
image.png

mysql> create procedure sp1() select version();
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call sp1;//sp1()也可以的
    -> //
+------------+
| version()  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

image.png

ps 不能修改过程体，要修改过程体必须先删除存储过程，再重建

mysql> drop procedure removeUserById;
Query OK, 0 rows affected (0.00 sec)

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.00 sec)

mysql> delimiter ;
mysql> call removeUserById(3);
Query OK, 1 row affected (0.02 sec)

mysql变量的术语分类：

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

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

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

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

3.会话变量：只对连接的客户端有效。

4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量

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

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.00 sec)
mysql> call removeUserAndReturnUserNums(16,@nums);
Query OK, 1 row affected (0.02 sec)

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

row_count()函数，记录表中发生变化的条数。

mysql> insert test(first_name)values('a'),('b'),('c');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

创建带有多个out类型参数的存储过程
下面的代码块是为了删除年龄为x的数据，然后输出删除了多少条和输出还剩下多少条

mysql> delimiter //
mysql> create procedure removeUserByAgeAndReturnInfos(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> select*from users;
+----+----------+----------------------------------+------+------+
| id | username | password                         | age  | sex  |
+----+----------+----------------------------------+------+------+
|  1 | A        | 7fc56270e7a70fa81a5935b72eacbe29 |   20 |    0 |
|  2 | B        | 9d5ed678fe57bcca610140957afab571 |   23 |    1 |
|  4 | D        | f623e75af30e62bbd73d6df5b50bb7b5 |   24 |    1 |
|  5 | E        | 3a3ea00cfc35332cedf6e5e9a32e94da |   24 |    0 |
|  6 | F        | 800618943025315f869e4e1f09471012 |   23 |    0 |
|  7 | G        | dfcf28d0734569a6a693bc8194de62bf |   22 |    0 |
|  8 | H        | c1d9f50f86825a1a2302ec2449c17196 |   23 |    0 |
|  9 | I        | dd7536794b63bf90eccfd37f9b147d7f |   23 |    0 |
| 10 | J        | ff44570aca8241914870afbc310cdb85 |   22 |    1 |
| 11 | K        | a5f3c6a11b03839d46af9fb43c97c188 |   22 |    1 |
| 12 | L        | d20caec3b48a1eef164cb4ca81ba2587 |   22 |    0 |
| 13 | M        | 69691c7bdcc3ce6d5d8a1361f22d04ac |   24 |    1 |
| 14 | N        | 8d9c307cb7f3c4a32822a51922d1ceaa |   21 |    0 |
| 15 | O        | f186217753c37b9b9f958d906208506e |   20 |    0 |
| 17 | Q        | f09564c9ca56850d4cd6b3319e541aee |   24 |    1 |
| 18 | R        | e1e1d3d40573127e9ee0480caf1283d6 |   24 |    1 |
+----+----------+----------------------------------+------+------+
16 rows in set (0.00 sec)
mysql> select count(id) from users where age=23;
+-----------+
| count(id) |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

mysql> call removeUserByAgeAndReturnInfos(23,@a,@b);
Query OK, 1 row affected (0.03 sec)

mysql> select @a,@b;
+------+------+
| @a   | @b   |
+------+------+
|    4 |   12 |
+------+------+
1 row in set (0.00 sec)

image.png