MySQL 存储过程

CURD: 插入,删除,更新,查找

MySQL执行流程

存储过程定义

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

存储过程的优点:

• 预编译

只在第一次调用时进行语法分析和编译，以后直接从内存中得到结果.可以避免重复的语法分析和编译，提高执行效率.

• 灵活性

可以通过控制语句对流程进行控制和判断,可以有多个返回值等.

• 减少网络流量

客户端不再需要提交一大段SQL语句到服务器执行,只需像函数调用一样,提交函数名和参数给服务器.

创建存储过程

语法结构

CREATE [DEFINER = {user|CURRENT_USER}]
 -- 定义时的用户，若是不写就默认为当前用户
PROCEDURE sp_name ([proc_parameter[,...]]) 
-- 可以带0到多个参数 sp_name存储过程的名字
[characteristic ...] 
-- 特性characteristic解释见下文
routine_body

-- 其中参数
 proc_parameter:
[IN|OUT|INOUT] param_name type

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

特性

characteristic

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

过程体

存储过程的过程体与自定义函数的函数体类似.

• 过程体由合法的SQL语句构成；
• 过程体可以是任意SQL语句(对表进行增删改查,连接,但是不能创建数据表)
• 过程体如果为复合结构则使用BEGIN...END 语句
• 复合结构可以使用条件、循环等控制语句

创建没有参数的存储过程

--sp1()获取mysql版本
CREATE PROCEDURE sp1() 
SELECT VERSION();

调用存储过程

-- 不带参数的存储过程调用 
CALL sp_name[()] 

--示例,无参数可以不加小括号
CALL sp1();
CALL sp1; 

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

DESC users;
DELIMITER //
CREATE PROCEDURE removeUserByID(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = id;
END
//
DELIMITER ; --恢复分隔符
--调用
CALL removeUserById(3);

参数名称id最好不要和表中的字段相同,修改过程体只能删掉存储过程重新定义如下:

--重新定义id为p_id
DROP PROCEDURE removeUserById;
--SELECT * FROM users; 
DELIMITER //
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN 
DELETE FROM users WHERE id = p_id;
END
//
DELIMITER ; --恢复分隔符

--调用
--SELECT * FROM users;
CALL removeUserById(22);
SELECT * FROM users WHERE id = 22;

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

DELIMITER //
CREATE PROCEDURE removerUserAndReturnUserName
(IN showID INT UNSIGNED,OUT showName INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = showID;
--该语句中的 INTO 含义就是将 SELECT 语句结果的表达式返回到 showName 变量中
SELECT count(ID) FROM user INTO showName;
END

--@nums 定义用户变量，可用 SELECT @nums 输出
CALL removerUserAndReturnUserName(10,@nums);

用 DECLARE 声明的变量是 局部变量，局部变量只能存在于 BEGIN...END 之间，且声明时必须置于 BEGIN...END 的第一行.
而通过 SELECT...INTO... 或者 SET @id = 7 这种方法定义的变量称为 用户变量，只能存在于当前用户所使用的mysql客户端有效。

--通过@或SET设置的变量称为用户变量
CALL rmUserAndRtUserNums(27, @nums);
SELECT @nums; --@nums是用户变量
SET @i = 7;  --@i是用户变量

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

根据年龄删除用户,返回删除的用户数和剩下的用户数.

--ROW_COUNT()函数相当于PHP的MySQL函数库中的mysql_affected_rows()函数，两者的作用相同，都是统计当插入和修改、添加和删除记录时影响的行数。

DELIMITER //
CREATE PROCEDURE rmUserByAgeAndRtInfos
(IN p_age SMALLINT UNSIGNED, OUT delNums SMALLINT UNSIGNED, OUT leftNums SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE WHERE age = p_age; --注意变量不同
SELECT ROW_COUNT() INTO delNums;
SELECT COUNT(id) FROM users INTO leftNums;
END
//

--调用存储过程
DELIMITER ;
CALL rmUserByAgeAndRtInfos(20, @a, @b);
SELECT @a, @b;

修改存储过程

不能修改过程体,修改过程体只能删掉重新定义.

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;
--示例
DROP PROCEDURE removeUserById;

与自定义函数的区别

存储过程实现的功能相对复杂，函数的针对性更强

函数一般不对表进行操作,存储过程经常操作表

存储过程可以返回多个值，函数只能有一个返回值

存储过程一般独立执行，函数往往作为 sql 语句的组成部分出现

存储过程用 CALL name 来调用,多个复杂功能的函数常常封装成一个存储过程,通过预编译提高执行效率.