MySQL学习笔记8：MySQL存储过程（storage procedure）

来源：互联网发布：mac装系统开机按哪个键编辑：程序博客网时间：2024/06/06 00:22

存储过程简介

MySQL命令的执行流程，如下图：

如果我们将上述过程简化一下，省略掉其中的语法分析和编译这2个环节,MySQL的执行效率就可以提高。
本文介绍的存储过程可以实现上述任务。。。
存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
存储过程存储在数据库内，可以由应用程序调用执行；允许用户声明变量，以及进行流程控制；
存储过程可以接收输入类型的参数，也可以接收输出类型的参数；可以存在多个返回值！
存储过程的效率比单一的SQL语句的效率高。如果我们写了2个SQL语句，MySQL引擎对这2个SQL语句逐一的进行语法分析，
逐一的进行编译，最后，逐一的执行。如果采用存储过程，第一次调用的时候会进行语法分析和编译，以后客户端再进行
调用的时候，直接调用第一次编译的结果就可以了，省略了语法分析和编译这2个环节，效率自然就提高了。
存储过程的优点，如下图：

存储过程语法结构解析
创建存储过程语法，如下图：

[DEFINER={user|CURRENT_USER}] DEFINER是创建者，若省略了这句话，默认的创建者是当前登录到MySQL客户端的用户。
sp_name指的是存储过程的名字。存储过程可以带0个，1个，或多个参数。
参数的前面可以有3个可选的选项，如下图：

存储过程的特性，如下图：

关于过程体的几点说明，如下图：

创建不带参数的存储过程
mysql> CREATE PROCEDURE sp1() SELECT VERSION();#创建不带参数的存储过程
Query OK, 0 rows affected (0.07 sec)
调用存储过程，如下图：

mysql> CALL sp1();#调用存储过程
+-----------+
| VERSION() |
+-----------+
| 5.5.37 |
+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 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(32) NOT NULL,
-> age TINYINT(3) UNSIGNED NOT NULL,
-> sex TINYINT NOT NULL
-> );
Query OK, 0 rows affected (0.16 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 | | NULL | |
| sex | tinyint(4) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.04 sec)
mysql> INSERT users VALUES(DEFAULT,'A',md5('jsdfdfggf'),22,1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users VALUES(DEFAULT,'B',md5('jfdfggf'),32,1);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT users VALUES(DEFAULT,'C',md5('jgfdfggf'),36,0);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users VALUES(DEFAULT,'D',md5('jgfdfg'),26,1);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT users VALUES(DEFAULT,'E',md5('jfdfg'),29,1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users VALUES(DEFAULT,'F',md5('jfg'),19,0);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 3 | C | 858f56e159d975d18b3eb798d08b4adf | 36 | 0 |
| 4 | D | 4db6a6fb1c6b1284a9b80e220144305f | 26 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
6 rows in set (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.03 sec)
mysql> DELIMITER ;
mysql> CALL removeUserById(3);
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM users;#可以看到，id=3的记录已经被删除
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 4 | D | 4db6a6fb1c6b1284a9b80e220144305f | 26 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
5 rows in set (0.00 sec)
创建带有IN和OUT类型参数的存储过程
删除指定用户，并返回剩余用户总数
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserAndReturnNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
-> BEGIN
-> DELETE FROM users WHERE id=p_id;
-> SELECT COUNT(*) FROM users INTO userNums;#将SELECT的结果（剩余用户总数）存入userNums中。
-> END
-> //
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;
mysql> SELECT COUNT(*) FROM users;#目前，数据表users中有5条记录
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
1 row in set (0.04 sec)
注意下面的命令：调用存储过程时，@nums是用户变量！调用存储过程之后，可以查看其值。
mysql> CALL removeUserAndReturnNums(4,@nums);#调用存储过程（删除id=4的记录，并返回剩余记录总数）
Query OK, 1 row affected (0.06 sec)
mysql> SELECT @nums;#查看变量的值（查看剩余记录总数）
+-------+
| @nums |
+-------+
| 4 |
+-------+
1 row in set (0.04 sec)
mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
关于变量：
通过DECLARE语句声明的变量称为局部变量，局部变量的作用范围仅仅在BEGIN和END语句块之间。
即局部变量只在BEGIN和END语句块之间有效；BEGIN和END语句块运行结束后，局部变量就失效了。
注意：在BEGIN和END语句块之间声明局部变量的时候，DECLARE语句必须位于语句块的第一行！
mysql> SET @var=8;#声明用户变量（对当前用户所使用的客户端有效）
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @var;
+------+
| @var |
+------+
| 8 |
+------+
1 row in set (0.00 sec)
用户变量和MySQL客户端绑定在一起，用户变量只对当前用户所使用的客户端生效！
mysql> SELECT COUNT(*) FROM users INTO @var;#将SELECT的结果存入@var
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @var;
+------+
| @var |
+------+
| 4 |
+------+
1 row in set (0.00 sec)
创建带有多个OUT类型参数的存储过程
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
4 rows in set (0.00 sec)
下面，我们创建一个存储过程，可以根据age来删除用户，并且返回删除的用户数和剩余的用户数。
在创建存储过程之前，我们先学习一个系统函数ROW_COUNT()，该函数返回插入，更新或删除的记录总数。
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| -1 |
+-------------+
1 row in set (0.02 sec)
下面，我们示例一下ROW_COUNT()函数的使用：
mysql> CREATE TABLE test(
-> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.14 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.01 sec)
mysql> INSERT INTO test(username) VALUES('A'),('B'),('C');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();#此处的ROW_COUNT()返回的是插入的记录数
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
mysql> UPDATE test SET username = CONCAT(username,'--haha') WHERE id<=2;
Query OK, 2 rows affected (0.07 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT ROW_COUNT();#此处的ROW_COUNT()返回的是更新的记录数
+-------------+
| ROW_COUNT() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A--haha |
| 2 | B--haha |
| 3 | C |
+----+----------+
3 rows in set (0.00 sec)
mysql> DELETE FROM test WHERE id=3;
Query OK, 1 row affected (0.05 sec)
mysql> SELECT ROW_COUNT();#此处的ROW_COUNT()返回的是删除的记录数
+-------------+
| ROW_COUNT() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
| 1 | A--haha |
| 2 | B--haha |
+----+----------+
2 rows in set (0.00 sec)
综上，通过ROW_COUNT()函数可以得到插入，更新或删除的记录总数！
下面，我们来创建一个存储过程，它可以根据age来删除用户，并且返回删除的用户数和剩余的用户数。
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteNums SMALLINT UNSIGNED,OUT restNums SMALLINT UNSIGNED)
-> BEGIN
-> DELETE FROM users WHERE age=p_age; #根据年龄删除记录
-> SELECT ROW_COUNT() INTO deleteNums;#将删除记录总数存入deleteNums中
-> SELECT COUNT(*) FROM users INTO restNums;#将剩余记录数存入restNums中
-> END
-> //
Query OK, 0 rows affected (0.05 sec)
mysql> DELIMITER ;
mysql> SELECT * FROM users;此时，数据表中有4条记录
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 2 | B | f18b458ea7e594b115e5b43483165ac9 | 32 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM users WHERE age=32;#数据表中，age=32的记录只有1条
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> CALL removeUserByAgeAndReturnInfos(32,@deleteN,@restN);#调用存储过程（删除age=32的记录）
Query OK, 1 row affected (0.22 sec)
mysql> SELECT @deleteN,@restN;#查看删除的记录数和剩余的记录数
+----------+--------+
| @deleteN | @restN |
+----------+--------+
| 1 | 3 |
+----------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM users;#调用存储过程之后，数据表中只剩下3条记录
+----+----------+----------------------------------+-----+-----+
| id | username | password | age | sex |
+----+----------+----------------------------------+-----+-----+
| 1 | A | 544a4a46a7ed903e3fda227692eb59b7 | 22 | 1 |
| 5 | E | ab077a4191b2d4bb59add420215e140d | 29 | 1 |
| 6 | F | fd6976a3f3843c433933c30c21738346 | 19 | 0 |
+----+----------+----------------------------------+-----+-----+
3 rows in set (0.00 sec)
存储过程与自定义函数的区别，如下图：

修改存储过程，如下图：

存储过程只能修改一些简单的特性，不能修改过程体；如果要修改过程体，只能先将存储过程删除，再重新创建。
删除存储过程，如下图：

（完）

1 0