MySQL存储过程

MySQL命令执行流程

当成功输入SQL命令以后，MySQL引擎会首先对输入的命令进行语法分析，语法正确的话将编译成MySQL引擎可以识别的命令，最后开始执行，并且把执行结果返回给客户端。

存储过程

存储过程是SQL语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理，存储过程采用预编译，即第一次编译之后，会把结果存于内存中供下次调用。存储过程省略了语法分析和编译的过程，可以实现MySQL命令执行流程的简化。

存储过程的优点

• 增强SQL语句的功能和灵活性
• 实现了较快的执行速度（但第一次调用存储过程的速度和MySQL语句执行流程的速度是一样的，因为要进行语法分析）
• 减少网络流量

创建存储过程

语法结构分析

CREATE
[DEFINER = {user | CURRENT_USER}]   //定义时的用户，若是不写就默认为当前用户
PROCEDURE sp_name([proc_parameter[,...]])   //可以带0到多个参数
[characteristic ...]routine_body
//其中参数
proc_parameter:
[IN | OUT | INOUT]param_name type

参数

• IN（输入类型）：表示该参数的值必须在调用存储过程时指定
• OUT（输出类型）：表示该参数的值可以被存储过程改变，并且可以返回
• INOUT（输入&&输出）：表示该参数的调用时指定，并且可以被改变和返回

特性

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

过程体

• 过程体由合法的SQL语句构成
• 过程体可以是“任意”SQL语句，“任意”主要指的是对于记录的增删改查以及多表的连接，但是不能创建数据表或数据库
• 过程体如果为复合结构则使用BEGIN…END语句
• 复合结构可以使用条件、循环等控制语句

注意事项

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

调用存储过程

CALL sp_name([parameter[,...]])     //带参数的存储过程的调用
CALL sp_name[()]                    //不带参数的存储过程调用

示例：

// 若sp1为不带参数的存储过程，则可以这样调用
CALL sp1;
CALL sp1();

修改存储过程

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

修改存储过程只能修改简单的特性，并不能修改过程体。

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

创建不带参数的存储过程

示例：

mysql> CREATE PROCEDURE sp1() SELECT VERSION();

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

示例：

DELIMITER //                //修改定界符为//
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN 
DELETE FROM users WHERE id = p_id;
END
//                          //结束
DELIMITER ;                 //修改定界符为;
CALL removeUserById(20);    // 调用该存储过程

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

示例：

DELIMITER //
CREATE PROCEDURE removerUserAndReturnUserName(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;      //语句1
END
//
DELIMITER ;
CALL removerUserAndReturnUserName(27,@nums);    //语句2
SELECT @nums;                                   //语句3

说明：

• 语句1中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 userNums 变量中
• 语句2中的@nums 所代表的就是用户变量，可用语句3输出
• 用 DECLARE 声明的变量是局部变量，局部变量只能存在于 BEGIN…END 之间有效，且声明时必须置于 BEGIN…END 的第一行
• 通过 SELECT…INTO… 或者 SET @id = 7 这种方法声明的变量称之为用户变量，只对当前用户所使用的客户端有效。

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

示例：

DELIMITER //
CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED, OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;            //返回增加、更新、删除被影响的记录数
SELECT COUNT(id) FROM users INTO userCounts;    //剩余的记录数
END
//
DELIMITER ;
CALL removeUserByAgeAndReturnInfos(23, @a, @b);
SELECT @a, @b;

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

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