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语句的组成部分来出现