你相信吗，相信那一天的夕阳吗?

上一章简单介绍了 MySQL的索引(二十三),如果没有看过,请观看上一章

一. 存储过程

MySQL从5.0版本开始支持存储过程和函数。存储过程和函数能够将复杂的SQL逻辑封装在一起，

应用程序无须关注存储过程和函数内部复杂的SQL逻辑，

而只需要简单地调用存储过程和函数即可。

一.一概述

含义：存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。

执行过程：存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，

服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。

好处：

1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2、减少操作过程中的失误，提高效率
3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

和视图、函数的对比：

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

一.二分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1、没有参数（无参数无返回）
2、仅仅带 IN 类型（有参数无返回）
3、仅仅带 OUT 类型（无参数有返回）
4、既带 IN 又带 OUT（有参数有返回）
5、带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

二. 创建存储过程

创建语法分析

语法：

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN存储过程体END

类似于Java中的方法：

修饰符 返回类型 方法名(参数类型 参数名,...){方法体;
}

1、参数前面的符号的意思

IN：当前参数为输入参数，也就是表示入参；

存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。
OUT：当前参数为输出参数，也就是表示出参；

执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
[NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。
- CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
- NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
- READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
- MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
- 默认情况下，系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
- DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
- INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
- 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
COMMENT 'string'：注释信息，可以用来描述存储过程。

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

BEGIN…END：BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
SET：赋值语句，用于对变量进行赋值。
SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

5、需要设置新的结束标记

DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

比如：“DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

示例:

DELIMITER //CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGINsql语句1;sql语句2;END //

存储过程举例，有一个表 auth_user

二.一创建一个无参的存储过程

查询一下邮箱:

-- 切换标识符
delimiter //-- 创建存储语句
create PROCEDURE p1() 
begin
select email from auth_user where username = 'admin';-- 结束
end //-- 恢复标识符为  ;
delimiter ;

将这些 sql 一起执行，执行完成后，会展示到函数下面 Px 表示存储过程 (Navicat 展示)

调用存储过程 call p1()

call p1();

二.二创建一个入参的存储过程并调用


-- 切换标识符
delimiter //-- 创建存储语句
create PROCEDURE p2(IN usernameParam varchar(20)) 
beginselect email from auth_user where username = usernameParam;-- 结束
end //-- 恢复标识符为  ;
delimiter ;call p2('admin');    --  会展示邮箱call p2 ('admin222');  -- 查询不出来，为空

二.三创建一个出参的存储过程并调用

-- 切换标识符
delimiter //-- 创建存储语句
create PROCEDURE p3(OUT s_email varchar(25)) 
beginselect email into s_email from auth_user where username = 'admin';-- 结束
end //-- 恢复标识符为  ;
delimiter ;-- 定义一个变量，要有默认值为  '' 
set @s_email = '';-- 查询一下自定义的变量值
select @s_email;-- 调用存储函数
call p3(@s_email);-- 查询一下变量，发现是有值的   为正常的邮箱
select @s_email;

二.四创建一个入参和出参均有的存储过程并调用


-- 切换标识符
delimiter //-- 创建存储语句
create PROCEDURE p4(OUT s_email varchar(25), IN s_username varchar(20)) 
beginselect email into s_email from auth_user where username = s_username ;-- 结束
end //-- 恢复标识符为  ;
delimiter ;-- 定义一个变量，要有默认值为  '' 
set @s_email = '';-- 查询一下自定义的变量值
select @s_email;-- 调用存储函数
call p4(@s_email, 'admin');-- 查询一下变量，发现是有值的 
select @s_email;

二.五创建一个 INOUT 形成的存储过程并调用

给邮箱 email 添加一个前缀，并返回拼接后的新邮箱信息


-- 切换标识符
delimiter //-- 创建存储语句
create PROCEDURE p5(INOUT s_email varchar(100), IN s_username varchar(100)) 
begin
DECLARE dbEmail varchar(100) default '';
select email into dbEmail from auth_user where username = s_username ;
set  s_email = concat(s_email,dbEmail);
-- 结束
end //-- 恢复标识符为  ;
delimiter ;-- 定义一个变量，要有默认值为  '' 
set @s_email = 'hello:';-- 查询一下自定义的变量值
select @s_email;-- 调用存储函数
call p5(@s_email, 'admin');-- 查询一下变量，发现是有值的   hello:1290513799@qq.com 
select @s_email;

二.六 IF ELSE 形式的判断存储过程

DELIMITER $$
CREATEPROCEDURE `demo`.`p11`(IN `day` INT)-- 存储过程体BEGINIF `day` = 0 THENSELECT '星期天';ELSEIF `day` = 1 THENSELECT '星期一';ELSEIF `day` = 2 THENSELECT '星期二';ELSESELECT '无效日期';END IF;END$$
DELIMITER ;-- 星期天 
call p11(0);-- 星期一 
call p11(1);-- 无效日期
call p11 (5);

二.七 CASE 形式的判断存储过程

DELIMITER $$
CREATE PROCEDURE p12(IN num INT)BEGINCASE -- 条件开始WHEN num<0 THEN SELECT '负数';WHEN num>0 THEN SELECT '正数';ELSE SELECT '不是正数也不是负数';END CASE; -- 条件结束END$$DELIMITER;-- 正数
call p12(3);-- 负数
call p12 (-2);-- 不是正数也不是负数 
call p12 (0);

二.八 While 循环计算总和

DELIMITER $$
CREATE PROCEDURE p13(IN num INT,OUT SUM INT)BEGINDECLARE i INT;set i = 1;set sum = 0;WHILE i<= num DO -- 循环开始SET SUM = SUM+ i;SET i = i+1;END WHILE; -- 循环结束END$$
DELIMITER;set @SUM = 0;call p13(100, @SUM);select @SUM;  -- 5050

二.九定义一个 saveOrUpdate 的存储过程

DELIMITER $$
CREATE PROCEDURE mysaveOrUpdate(IN s_username VARCHAR(50),IN s_password VARCHAR(50) ,OUT s_result varchar(100))BEGIN-- 声明一个变量 用来决定这个名字是否已经存在DECLARE s_count INT DEFAULT 0;-- 验证这么名字是否已经存在SELECT COUNT(*) INTO s_count FROM auth_user WHERE `username` = s_username;	IF s_count = 0 THENINSERT INTO auth_user (`username`, `password`) VALUES(s_username, s_password);SET s_result = '数据添加成功';ELSEupdate auth_user set password = s_password where username = s_username;SET s_result = '进行更新';END IF;END$$
DELIMITER;-- 刚开始查询没有数据
select * from auth_user where username ='yjl';set @s_result ='';-- 调用  abc123call mysaveOrUpdate('yjl','abc123',  @s_result);-- 数据添加成功 
select @s_result;-- 查询的时候，也是有的。 
select username,password from auth_user where username ='yjl';call mysaveOrUpdate('yjl','123456',  @s_result);-- 进行更新  
select @s_result;-- 查询的时候, 会发现数据已经更新了    123456 
select username,password from auth_user where username ='yjl';

二.十 Mybatis 调用存储过程

需要设置 statementType=“CALLABLE”, 然后 { call 存储过程名(参数1,参数2,参数3) }

如插入的话:

存储函数定义为：

CREATE PROCEDURE insert_user(OUT u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGIN
INSERT INTO t_user (name,sex,age) VALUES (u_name,u_sex,u_age);
SET u_id=LAST_INSERT_ID();
END

UserMapper.xml:

<!-- 添加用户 --><insert id="addUser" parameterType="com.po.User" statementType="CALLABLE">{call insert_user(#{id,mode=OUT,jdbcType=INTEGER},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN})}</insert>

更新的话:

CREATE PROCEDURE updateUser(IN u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGINUPDATE t_user SET name=u_name,sex=u_sex,age=u_age WHERE id=u_id;
END

UserMapper.xml:


<!-- 更新用户 --><update id="updateUser" parameterType="user" statementType="CALLABLE">{call updateUser(#{id,mode=IN},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN})}</update>

查询的话:

CREATE PROCEDURE getUserById(IN u_id INTEGER)
BEGINSELECT id,name,sex,age FROM t_user WHERE id=u_id;
END

UserMapper.xml:

<!-- 根据id查询用户 --><select id="getUserById" parameterType="Integer" resultType="user" statementType="CALLABLE">{call getUserById(#{id,mode=IN})}</select>

如删除的话:

CREATE PROCEDURE deleteUser(IN u_id INTEGER)
BEGINDELETE FROM t_user WHERE id=u_id;
END

UserMapper.xml:

<!-- 删除用户 --><delete id="deleteUser" parameterType="Integer" statementType="CALLABLE">{call deleteUser(#{id,mode=IN})}</delete>

三. 存储过程用法

三.一调用存储过程 call

call 存储函数名( 对应的参数1，对应的参数2，对应的参数3)

set @SUM = 0;call p13(100, @SUM);select @SUM;  -- 5050

三.二查询存储过程

SHOW CREATE PROCEDURE 存储过程名

show create PROCEDURE p13;

从information_schema.Routines表中查看存储过程和函数的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='p13' AND ROUTINE_TYPE = 'PROCEDURE';

三.三删除存储过程

DROP PROCEDURE  [IF EXISTS] 存储过程名

-- 删除
drop PROCEDURE if EXISTS p13;-- 删除之后再查询就查询不到了
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='p13' AND ROUTINE_TYPE = 'PROCEDURE';