MySQL基础八：MySQL存储过程

最新推荐文章于 2023-03-26 13:59:26 发布

原创最新推荐文章于 2023-03-26 13:59:26 发布 · 370 阅读

1 ·

CC 4.0 BY-SA版权

MySQL基础专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍MySQL存储过程的创建与使用，包括无参数存储过程、带有IN类型参数的存储过程及如何通过存储过程返回多种信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文参考：http://www.imooc.com/video/2998

创建没有参数的存储过程；

mysql> #创建存储过程；
mysql> CREATE PROCEDURE sp1() SELECT VERSION();
Query OK, 0 rows affected (0.00 sec)

mysql> #调用存储过程；
mysql> CALL sp1;
+------------+
| VERSION()  |
+------------+
| 5.7.10-log |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 sec)

带IN类型的存储过程；

mysql> #创建存储过程；
mysql> CREATE PROCEDURE sp1() SELECT VERSION();
Query OK, 0 rows affected (0.00 sec)

mysql> #调用存储过程；
mysql> CALL sp1;                                                +------------+
| VERSION()  |
+------------+
| 5.7.10-log |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Query OK, 0 rows affected (0.00 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(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> #更换结束符；
mysql> DELIMITER //
mysql>
mysql> #创建IN类型的存储过程；
mysql> CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM users WHERE id=id;#<span style="color:#ff0000;">注意这里的id会把数据表中全部元素都删除</span>
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL removeUserById(3);
Query OK, 8 rows affected (0.07 sec)

mysql> SELECT * FROM users;
Empty set (0.00 sec)

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

mysql> 
mysql> SHOW TABLES;
+------------------+
| Tables_in_world  |
+------------------+
| city             |
| country          |
| countrylanguage  |
| provinces        |
| tdb_goods        |
| tdb_goods_brands |
| tdb_goods_cate   |
| tdb_goods_types  |
| test             |
| users            |
+------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM users;
Empty set (0.00 sec)

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | 111      |
|  3 | tom%     |
|  4 | NULL     |
|  5 | AA       |
|  6 | BB       |
|  7 | Rose     |
+----+----------+
7 rows in set (0.00 sec)

mysql> DELIMITER //
mysql>
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> 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 test WHERE id=p_id;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL removeUserById(2);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  3 | tom%     |
|  4 | NULL     |
|  5 | AA       |
|  6 | BB       |
|  7 | Rose     |
+----+----------+
6 rows in set (0.00 sec)

mysql> USE world;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_world  |
+------------------+
| city             |
| country          |
| countrylanguage  |
| provinces        |
| tdb_goods        |
| tdb_goods_brands |
| tdb_goods_cate   |
| tdb_goods_types  |
| test             |
| users            |
+------------------+
10 rows in set (0.00 sec)

mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
    -> BEGIN
    -> DELETE FROM test WHERE id=p_id;
    -> SELECT count(id) FROM test INTO userNums;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

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

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

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

mysql> #@是全局变量；
mysql> #@是用户变量；
mysql> SET @i=7;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DESC test;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  3 | tom%     |
|  5 | AA       |
|  6 | BB       |
|  7 | Rose     |
+----+----------+
5 rows in set (0.00 sec)

mysql> #下面这个存储过程返回两个值；
mysql> #得到行数；
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

mysql> DESC test;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)         | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT test(username) VALUES('A'),('B'),('C');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> UPDATE test SET username=CONCAT(username,'--imooc') WHERE id>6;
Query OK, 4 rows affected (0.05 sec)
Rows matched: 4  Changed: 4  Warnings: 0


mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

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


mysql> DELIMITER ;
mysql> SELECT * FROM test;
+----+-------------+
| id | username    |
+----+-------------+
|  1 | John        |
|  3 | tom%        |
|  5 | AA          |
|  6 | BB          |
|  7 | Rose--imooc |
|  8 | A--imooc    |
|  9 | B--imooc    |
| 10 | C--imooc    |
+----+-------------+
8 rows in set (0.00 sec)


mysql> SELECT COUNT(id) FROM test WHERE username='%imooc';
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)


mysql> SELECT COUNT(id) FROM test WHERE username='%imooc%';
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)


mysql> SELECT COUNT(id) FROM test WHERE username='B%';
+-----------+
| COUNT(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)


mysql> CALL removeUserByIdAndReturnInfos(23,@a,@b);
Query OK, 1 row affected (0.00 sec)


mysql> CALL removeUserByIdAndReturnInfos(1,@a,@b);
Query OK, 1 row affected (0.04 sec)


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


mysql> SELECT * FROM test;
+----+-------------+
| id | username    |
+----+-------------+
|  3 | tom%        |
|  5 | AA          |
|  6 | BB          |
|  7 | Rose--imooc |
|  8 | A--imooc    |
|  9 | B--imooc    |
| 10 | C--imooc    |
+----+-------------+
7 rows in set (0.00 sec)