第八章 MySQL存储过程

课程回顾

自定义函数：简称UDF；是对MySQL扩展的一种途径
创建自定义函数：CREATE FUNCTION......
自定义函数的两个必要条件
- 参数：可以有零个或多个
- 返回值：只能有一个返回值
具有符合结构的函数体需要使用BEGIN...END来包含

存储过程简介

在对数据表进行插入，更新，查询，删除（CURD）MySQL的执行过程如下：

Paste_Image.png

如果省略了语法分析和编译的环节，MySQL的执行效率就会提高，所以就可以使用存储过程

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

存储在数据库内，可以由应用程序调用执行，而且允许用户声明变量，进行流程控制，可以接收参数、输入类型的参数、输出类型的参数，并且可以存在多个返回值

如果有两条插入的语句，不使用存储过程时需要对每条语句进行编译执行，如果使用存储过程，就可以直接调用，省去了一次编译的过程，效率会提高很多

存储过程的优点：

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

存储过程语法结构分析

创建存储过程：
CREATE
[DEFINER={user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type

DEFINER为创建者，如果省略DEFINER语句默认为当前登陆到MySQL的用户

参数：

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

过程体：

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

任意SQL语句：并不是所欲的SQL语句，不能通过存储过程创建数据表和数据库，任意是指对记录的增删改查以及多表的连接等操作

创建不带参的存储过程

创建存储过程sp1功能是获取MySQL客户端的版本号

CREATE PROCEDURE sp1() SELECT VERSION();

调用存储过程：

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

如果存储过程没有参数：带不带小括号都可以，如果还有参数，则必须带有参数
CALL sp1;#不带有小括号，执行成功，结果为：5.5.37
CALL sp1();#带有小括号，执行成功，结果为：5.5.37

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

创建一个存储过程，带有一个IN参数id，功能是删除参数为id的记录
修改MySQL定界符：DELIMITER //

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

调用：
因为这个存储过程有参数，所以需要使用小括号
修改定界符：DELIMITER ;
CALL removeUserById(3);调用成功
使用查看表记录，发现i数据库中所有记录都被删除
因为MySQL把id=id中的两个id都当成了字段，而不是参数
所以，存储引擎的参数名称不能和数据库中的字段名一样
所以需要修改存储过程，存储过程的修改注释，内容类型等等特性，并不能修改过程体，如果需要修改过程体，只能先删除再重新创建

修改存储过程：
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;#删除成功

修改MySQL定界符：DELIMITER //

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

修改定界符：DELIMTER ;
调用存储过程：
CALL removeUserById(22);#修改成功

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

从users表中删除id不固定的记录，并且返回剩余的记录。需要两条语句，一个是删除记录的语句，一个是获取剩余记录的语句，所以必须要添加BEGIN...END语句，第二个参数是需要返回的，所以需要时OUT类型
演示：
DELIMITER //#修改定界符

CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;#INTO是把查询结果放到变量里
END
//

创建成功
DELIMITER ;#修改定界符
调用存储过程：
CALL removeUserAndReturnUserNums(27,@nums);#删除id为27的记录并且把剩余记录的条数放到nums中
SELECT @nums;#得到的结果为剩余记录的条数

在BEGIN...END之间也可以声明变量，但是在在BEGIN...END之间声明的变量作用域只是在BEGIN...END之间，在BEGIN...END语句执行完成后，局部变量就消失了。而且在BEGIN...END之间声明变量时DECLARE语句必须要位于语句的第一行。@nums是一个用户变量，用户变量可以通过SELECT...INTO...语句或者SET语句声明（如SET @i = 7;#声明变量i的值为7），用户变量是对MySQL的客户端定义的，通过这两种方法只针对当前用户所使用的客户端生效

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

系统函数ROW_COUNT(),功能是得到被影响的条数（插入、删除、更新）
演示：
在test表中插入三条记录：
INSERT test(username) VALUES('A','B','C');
使用ROW_COUNT()函数：
SELECT ROW_COUNT();#得到上一条语句被影响的条数，结果为3

更新test表中的userame字段：
UPDATE test SET username = CONCAT(username,'--imooc') WHERE id<=2;#将id<=2的记录的用户名后加上'--imooc'
SELECT ROW_COUNT();#得到刚才更新记录被影响的条数，结果为2

创建一个存储过程，有一个IN类型参数age，和两个OUT类型参数，功能是将age记录删除，并且返回被删除记录和剩余的记录
DELIMITER //#修改定界符

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCOUNTS;
END
//

创建成功
DELIMITER ;#修改定界符
调用存储过程：
CALL removeUserByAgeAndReturnInfos(20,@a,@b);

SELETE @a,@b;#结果为3和13

如果创建错误，可以删除存储过程DROP PROCEDURE removeUserByAgeAndReturnInfos;

如果存储过程中有某些语句书写错误，在调用过程中没有错误的语句会执行成功

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

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

实际应用中，通常把一些复杂的需要重复调用的过程封装成存储过程，因为存储过程比一条一条语句查询效率高很多，经常使用存储过程操作数据表，很少使用函数操作表

修改存储过程：
ALTER PROCEDURE sp_name [characteristic...]
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
不能修改过程体，如果需要修改，就要先删除存储过程然后重新创建