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
