MySQL存储过程

什么是存储过程?

SQL命令-MySQL引擎分析语法是否正确-编译SQL命令-执行结果-返回客户端。如果省略语法分析和编译环境可以提高MySQL的执行效率
存储过程是为以后使用而保存的一条或多条SQL语句,是SQL语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。 可将其视为批文件,但作用不限于批处理。存储过程中可以包含控制语句。MySQL最新版本支持存储过程。
存储过程三个优点:
增强SQL语句的功能和灵活性
较快的执行速度
减少网络流量

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE
[DEFINER= {user|CURRENT_USER}]
PROCEDURE sp_name(proc_parameter[,...])
[characteristic...]routine_body

proc_parameter:
[IN|OUT|INOUT] para_name type

IN表示该参数的值必须在调用存储过程中指定(传递值给存储过程)
OUT表示该参数值可以被存储过程改变,并且可以返回。(从存储过程返回值)
INOUT表示该参数在调用时指定,并且可以被指定和返回。
characteristic特性:
COMMENT 'string'|{CONTAINS SQL|NO SQL|READS SQL DATA |MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}
COMMNET 注释
CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句
NO SQL:不包含SQL语句
READS SQL DATA:包含读数据的语句
MODIFIES SQL DATA:包含写数据的语句
SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行
```
### 过程体
过程体由合法的SQL语句构成
过程体可以是任意SQL语句
过程体如果是复合结构(包含两个及以上的语句)则使用BEGIN…END语句。
复合结构内可以包含声明、循环以及控制结构。
``` bash
$ mysql> CREATE PROCEDURE sp1() SELECT VERSION();

创建不带参数的存储过程

显示MySQL版本

1
2
3
4
$ mysql> DELIMITER //
-> CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`()
-> SELECT VERSION()// /*显示MySQL版本*/
-> DELIMITER ;

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

从数据表删除指定记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
$ mysql> DELIMITER //
$ mysql> CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNIED)
-> BEGIN
-> DELETE FROM TEST WHERE id=p_id;
-> END
-> //
```
### 创建INOUT类型的存储过程
$ mysql> DELIMITER //
$ mysql> CREATE PROCEDURE RemoveUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT nums INT UNSIGNED)
-> BEGIN
-> DELETE FROM TEST WHERE id=p_id;
-> SELECT COUNT(ID) FROM TEST INTO NUMS;
-> END
-> //
$ mysql> DELIMITER;
$ mysql> CALL RemoveUserAndReturnUserNums(1,@nums);
@nums为用户变量与,MySQL客户端绑定,只对当前用户客户端有效。

### 创建带多个OUT类型参数的存储过程
SELECT ROW_COUNT();返回前一个SQL进行UPDATE、DELETE、INSERT操作所影响的行数。
``` bash
$ mysql> DELIMITER //
$ mysql> CREATE PROCEDURE RemoveUsersByAgeAndReturnInfos(IN p_id INT 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> CALL RemoveUsersByAgeAndReturnInfos(8,@a,@b);

调用存储过程

1
2
CALL sp_name([parameter[,...]])
CALL sp_name[()]

修改存储过程

1
2
3
ALTER PROCEDURE sp_name[characteristic...]
COMMENT 'string'
|{CONTAINS SQL|NO SQL|READS SQL DATA |MODIFIES SQL DATA}|SQL SECURITY{DEFINER|INVOKER}

修改存储过程只能修改注释以及当前内容类型,不能修改过程体。
若要修改过程体,只能先删除存储过程再创建存储过程。

删除存储过程

1
DROP PROCEDURE [IF EXISTS] sp_name

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

存储过程实现的功能更加复杂,函数针对性更强
存储过程可以返回多个值,自定义函数只能返回一个值
存储过程一般独立来执行,自定义函数可以作为其他SQL语句的组成部分来出现。
实际应用过程中很少使用函数,但是经常会把复杂的过程封装成存储过程。使用程序调用API相对于存储过程来说比较慢,可以把经常用的操作封装成存储过程。