第八章 MySQL存储过程

MySQL存储过程.png

一、存储过程

1.定义

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理

2.存储过程的优点

• 增强SQL语句的功能和灵活性
• 实现较快的执行速度
• 减少网络流量

3.创建存储过程

CREATE
[DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter:
[IN|OUT|INOUT] param_name type

4.参数

• IN，表示该参数的值必须在调用存储过程时指定
• OUT，表示该参数的值可以被存储过程改变，并且可以返回
• INOUT，表示该参数在调用时指定，并且可以被改变和返回

5.特性

COMMENT 'string'
|{CONTAINS SQL| NO SQL| READS SQL DATA| MODIFIES SQL DATA
|SQL SECURITY{DEFINER| INVOKER}}

• COMMENT：注释
• CONTAINS SQL：包含SQL语句，但不包含读或写数据的语句
• NO SQL：不包含SQL语句
• READS SQL DATA：包含读数据的语句
• MODIFIES SQL DATA：包含写数据的语句
• SQL SECURITY{DEFINER| INVOKER}指明谁有权限来执行

6.过程体

• 过程体由合法SQL语句构成
• 过程体可以是任意SQL语句
• 过程体如果为复合结构则使用BEGIN...END语句
• 复合结构可以包含声明，循环，控制结构

二、MySQL创建存储过程

1.MySQL创建不带参数的存储过程

创建

example:

CREATE PROCEDURE sp1() SELECT VERSION();

调用存储过程

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

example:

CALL sp1;
/*Result
+------------+
| VERSION()  |
+------------+
| 5.7.17-log |
+------------+
*/

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

example:

DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ;
CALL removeUserById(3);
SELECT * FROM users;
/* Result
+----+------+----------------------------------+------+------+
| id | name | password                         | age  | sex  |
+----+------+----------------------------------+------+------+
|  1 | A    | 202cb962ac59075b964b07152d234b70 |   25 |    1 |
|  2 | B    | 250cf8b51c773f3f8dc8b4be867a9a02 |   24 |    1 |
|  4 | D    | 38b3eff8baf56627478ec76a704e9b52 |   25 |    1 |
|  5 | E    | ec8956637a99787bd197eacd77acce5e |   27 |    0 |
|  6 | F    | 6974ce5ac660610b44d9b9fed0ff9548 |   21 |    1 |
|  7 | G    | c9e1074f5b3f9fc8ea15d152add07294 |   20 |    0 |
|  8 | H    | 65b9eea6e1cc6bb9f0cd2a47751a186f |   23 |    1 |
|  9 | J    | f0935e4cd5920aa6c7c996a5ee53a70f |   18 |    1 |
| 10 | K    | a97da629b098b75c294dffdc3e463904 |   30 |    0 |
| 11 | L    | a3c65c2974270fd093ee8a9bf8ae7d0b |    8 |    0 |
| 12 | M    | 2723d092b63885e0d7c260cc007e8b9d |   12 |    1 |
| 13 | N    | 3644a684f98ea8fe223c713b77189a77 |   27 |    1 |
| 14 | P    | 94f6d7e04a4d452035300f18b984988c |   26 |    0 |
| 15 | Q    | 18d8042386b79e2c279fd162df0205c8 |   25 |    0 |
| 16 | R    | cee631121c2ec9232f3a2f028ad5c89b |   23 |    0 |
| 17 | S    | d490d7b4576290fa60eb31b5fc917ad1 |   25 |    1 |
+----+------+----------------------------------+------+------+
*/

3.MySQL创建带有IN和OUT类型参数的存储过程

example:

DELIMITER //
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
//
DELIMITER ;
CALL removeUserANDReturnUserNums(17, @nums);
SELECT @nums;
/*Result
+-------+
| @nums |
+-------+
|    15 |
+-------+
*/

4.创建带有多个OUT类型参数的存储过程

example:

DELIMITER //
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
//
DELIMITER ;
CAll removeUserByAgeAndReturnInfos(20, @a, @b);
SELECT @a, @b;
/*Result
+------+------+
| @a   | @b   |
+------+------+
|    1 |   14 |
+------+------+
*/

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

• 存储过程实现的功能要复杂一些；而函数的针对性更强
• 存储过程可以返回多个值；函数只能有一个返回值
• 存储过程一般独立的来执行；而函数可以作为其他SQL语句的组成部分来出现。

6.修改存储过程

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

7.删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

三、小结

1. 存储过程：是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理。
2. 参数：输入类型 输出类型 输入&&输出
3. 创建：CREATE ...... PROCEDURE ......
4. 注意事项

• 创建存储过程或者自定义函数时需要通过DELIMITER语句修改界定符。
• 如果函数体或过程体有多个语句，需要包含在BEGIN......END语句块中。
• 存储过程通过call来调用。