mysql存储过程

一.存储过程概念

存储过程 (Stored Procedure) 是在大型数据库系统中 , 一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 , 用户通过指定存储过程的名字并给出参数 (如果该存储过程带有参数) 来执行它 , 存储过程是数据库中的一个重要对象 ; 存储过程中可以包含 逻辑控制语句 和 数据操纵语句 , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值 ;

1.1 为什么要使用存储过程

在数据库编程过程中经常会用到存储过程 , 相比 SQL 语句 , 存储过程更方便 , 快速 , 安全

• 优点：
  1.由于应用程序随着时间推移会不断更改 , 增删功能 , SQL 语句会变得更复杂 , 存储过程为封装此类代码提供了一个替换位置 ;
  2.由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中 , 所以存储过程运行要比单个的 SQL 语句块要快 ;
  3.由于在调用时只需用提供存储过程名和必要的参数信息 , 所以在一定程度上也可以减少网络流量 , 简单网络负担 ;
  4.可维护性高 , 更新存储过程通常比更改 , 测试以及重新部署程序集需要较少的时间和精力 ;
  5.代码精简一致 , 一个存储过程可以用于应用程序代码的不同位置 ;
  6.增强安全性 :
  6.1通过向用户授予对存储过程 (而不是基于表) 的访问权限 , 它们可以提供对特定数据的访问 ;
  6.2提高代码安全 , 防止 SQL注入 (但未彻底解决 , 例如将数据操作语言 DML 附加到输入参数) ;
  6.3SQLParameter 类指定存储过程参数的数据类型 , 作为深层次防御性策略的一部分 , 可以验证用户提供的值类型 (但也不是万无一失 , 还是应该传递至数据库前得到附加验证) ;

• 缺点：
  1.当数据量大的时候维护起来十分麻烦。
  2.可移植性差 , 由于存储过程将应用程序绑定到 Server , 因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

二.MyBatis中的statementType详解

• 在mapper文件中可以使用 statementType标记 使用什么的对象操作SQL语句
• statementType：标记操作SQL的对象
  • 取值说明：
    1. STATEMENT – 直接操作sql，不进行预编译，获取数据：$—Statement
    2. PREPARED – 预处理，参数，进行预编译，获取数据：#—–PreparedStatement: 默认
    3. CALLABLE – 执行存储过程——CallableStatement
• 其中如果在文件中，取值不同，那么获取参数的方式也不相同

<update id="update4" statementType="STATEMENT">
    update tb_car set price=${price} where id=${id}
</update>

<update id="update5" statementType="PREPARED">
    update tb_car set xh=#{xh} where id=#{id}
</update>

注意：

• 如果只为STATEMENT，那么sql就是直接进行的字符串拼接，这样如果为字符串需要加上引号
• 如果为PREPARED，是使用的参数替换，也就是索引占位符，我们的#会转换为?再设置对应的参数的值

三.mybatis的xml文件调用存储过程案例

3.1 案例一

## mybatis的xml文件调用存储过程函数
<!-- 执行存储过程 必须使用  statementType="CALLABLE";  --> 
例如：<select id="getUserById" parameterType="java.lang.Integer" resultType="java.util.Map" statementType="CALLABLE"> </select>
<!-- 语法 { call 存储过程名称([参数]) }--> 
例如：{call getUserById(#{id,mode=IN})}
 
[mybatis]Mapper.mxl中mode=IN，mode=OUT使用场景
需配合 statementType=“CALLABLE” 使用
存储过程有三种类型的参数，分别为 IN（输入参数），OUT（输出参数），INOUT（输入输出参数）。一个存储过程，可以有多个 IN 参数，至多有一个 OUT 或 INOUT 参数
实例：
创建getUserById存储过程
CREATE PROCEDURE getUserById(IN u_id INTEGER)
BEGIN
    select id,name,age from t_user where id=u_id;
END
<!-- 根据id查询用户 -->
<select id="getUserById" parameterType="java.lang.Integer" resultType="java.util.Map" statementType="CALLABLE">
	{call getUserById(#{id,mode=IN})}
</select>

xml文件调用存储过程.例子

<select id="test" parameterType="java.util.HashMap" statementType="CALLABLE">
	{call test(#{id,mode=IN},#{okFlag,mode=OUT,jdbcType=INTEGER})}
</select>

3.2 案例二

3.2.1 数据库

3.2.2业务流程

1.查询要插入用户是否存在，
2.通过parentId查找父部门Dep
3.插入用户，除去dep和id其他四个插入，其他四个中，有 parentId和name我们知道的
4.查询插入的用户，获取返回的id
5.id+父dep = 现在的dep
6.插入dep

3.2.3 sql中编写业务流程

# 修改数据库中命令的结束符号
DELIMITER $$

USE `vhr`$$

DROP PROCEDURE IF EXISTS `addDep`$$
# 五个参数 in 表示这是一个输入参数，out 表示这是一个输出参数
CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(in depName varchar(32),in parentId int,in enabled boolean,out result int,out result2 int)
# 开始存储过程的定义
begin
  # 声明一个变量
  #did父类id 
  #pDepPath 父类的DepPath
  declare did int;
  declare pDepPath varchar(64);
  
  # 执行插入操作，插入用户名和parentId，还有另外两个不重要的固定值
  insert into department set name=depName,parentId=parentId,enabled=enabled;
  # 查询受影响的行数，并将查询结果赋值给 result 变量，这个变量有什么作用？
  select ROW_COUNT() into result;
  # 查询刚刚插入记录的 id 并将查询结果赋值给 did 变量
  select LAST_INSERT_ID() into did;
  set resuldeleteDept2=did;
  # 查询父部门的 depPath，并将查询结果赋值给 pDepPath 这个变量
  select depPath into pDepPath from department where id=parentId;
  # 更新刚刚插入记录的 depPath，concat 是一个字符串拼接函数，把父部门的DepPath+子部门的id拼接成我们当前的depPath，再进行插入
  update department set depPath=CONCAT(pDepPath,'.',did) where id=did;
  # 修改父部门的 isParent 属性，因为我们的这个isParent属性默认是false即0也就是默认没有子部门
  update department set isParent=true where id=parentId;
  
# 存储过程定义结束
end$$

DELIMITER ;

3.2.4 java中的mapper

    <insert id="addDepartment" statementType="CALLABLE">
        call addDep(#{name,jdbcType=VARCHAR,mode=IN}, #{parentId,jdbcType=INTEGER,mode=IN},
                    #{enabled,jdbcType=BOOLEAN,mode=IN}, #{result,jdbcType=INTEGER,mode=OUT},
                    #{id,jdbcType=INTEGER,mode=OUT})
    </insert>

3.3 案例三

3.3.1数据库

和案例二相同

3.3.2业务流程

具体业务步骤：
1.通过传入的id查询当前部门的isParent，为0则继续下面，为1则说明其下还有子部门结束业务
2.通过传入的id找到parentId
3.通过parentId找到parnetId的总数
4.parentId总数>1 ,可以删除，且不用改父部门的isParent，直接删除
5.parentId总数=1 ,可以删除，要把父部门的isParent改成false
6.通过parentId找到父部门的id
7.再通过id去修改父部门的IsParent

3.3.3 sql中编写存储过程

DELIMITER $$

USE `vhr`$$

DROP PROCEDURE IF EXISTS `deleteDep`$$


#实际要编写的步骤
 
# in did int 输入参数为int类型，你要删除的部门的id  ，  out result int输出参数为int类型，你要删除的部门的结果
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDep`(in did int,out result int)
begin
# ecount保存部门下的员工数量
  declare ecount int;
# 要删除部门的父部门的id  
  declare pid int;
# 要删除的部门下的子部门的数量
  declare pcount int;
# 
  declare a int;
# 满足条件id=did 且  isParent=false 的部门的数量赋值给 a，即如果你的传入id为did的时候你的要删除部门isParent为false（其下无子部门），
#这时候的查询结果如果不是1，即上面两个条件不满足，（-2，表示当前部门不存在，或者说）
#或者统计到的a！=1（查询结果赋值给了a），即当前部门不满足上面两个条件，就自动的判定为false就说明当前部门无法删除
  select count(*) into a from department where id=did and isParent=false;
  
  if a=0 then set result=-2;
  else
#查询要删除的部门下的员工数量，有员工则不能够删除
  select count(*) into ecount from employee where departmentId=did;
#-1表示部门下有员工，删除失败
  if ecount>0 then set result=-1;
  else
#查询要删除部门的父部门id，并将结果赋值给pid 
  select parentId into pid from department where id=did;
#执行删除操作  
  delete from department where id=did and isParent=false;
#查询手影响的行数  
  select row_count() into result;
#查询被删除部门的父部门下的子部门的数量  
  select count(*) into pcount from department where parentId=pid;
#将父部门的IsParent设置为 false  
  if pcount=0 then update department set isParent=false where id=pid;
  end if;
  end if;
  end if;
  
  
  
  
end$$

DELIMITER ;

3.3.4 java中的mapper

    <select statementType="CALLABLE" id="deleteDepartmentById">
        call deleteDep(#{id,jdbcType=INTEGER,mode=IN},#{result,jdbcType=INTEGER,mode=OUT})
    </select>