七、MySQL存储过程

• 存储过程：是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理
• 参数：输入类型 输出类型 输入&&输出
• 创建：CREATE......PROCEDURE......
• 注意事项：
  • 创建存储过程或者自定义函数时需要通过delimiter语句修改定界符
  • 如果函数体或过程体有多个语句，需要包含在BEGIN...END语句块中
  • 存储过程通过call来调用

1、MySQL命令执行流程

执行流程.png

2、概述

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

3、存储过程的优点

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

4、创建存储过程

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

proc_parameter:
[IN|OUT|INOUT] param_name type

5、参数

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

6、特性

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 }：指明谁有权限来执行

7、过程体

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

8、创建不带参数的存储过程

# 获取当前MySQL版本
CREATE PROCEDURE sp1()
SELECT VERSION();

9、调用存储过程

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

10、创建带有IN类型参数的存储过程

DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//

DELIMITER ;

11、修改存储过程

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

12、删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

13、创建带有IN和OUT类型参数的存储过程

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 ;

# 调用上面的带有IN和OUT类型参数的存储过程
# @nums表示接收剩余总数的变量
CALL removeUserAndReturnUserNums(27,@nums);
SELECT @nums;

• 通过DELIMITER声明的变量称之为局部变量，局部变量的范围只在BEGIN... AND语句块之间
• 在BEGIN...AND语句块之间声明局部变量时， DELIMITER语句必须要位于第一行
• SET @i = 7；这种变量为用户变量，只对MySQL客户端生效

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

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