MySQL简单操作(9)-MySQL存储过程

SQL命令 –> MySQL引擎 -分析-> 语法正确 –> 可识别命令 -执行-> 执行结果 -返回-> 客户端

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

优点：

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

存储过程语法结构解析

CREATE
[DEFINER = { user | CURRENT_USER}]
PROCEDURE sp_name([proc_parameter[,...]])
[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 { DEFINER | INVOKER } 指明谁有权限来执行

过程体

1. 由合法的SQL语句构成
2. 可以是任意的SQL语句（不能创建数据库和表）
3. 如果为复合结构则使用BEGIN…END语句
4. 复合结构可以包含声明，循环，控制结构

调用存储过程

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

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

创建不带参数的存储过程

CREATE PROCEDURE sp1 SELECT VERSION();

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

举例：

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

创建带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 ;

CALL removeUserAndReturnUserNums(27, @nums);

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

举例：

DELIMITER //

CREATE PROCEDURE removeUserByAgeAndReturnUserNums(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) INTO userCounts;
END
//

DELIMITER ;

修改存储过程

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

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

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