一、什么是mysql存储过程
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。MySQL 5.0 版本开始支持存储过程。
直白点就是，本该在service层完成的逻辑操作，直接放到数据库内完成了。
好处：减少了与数据进行连接完成操作的过程，提高了执行效率
缺点：增加了数据库的负担，且移植性较差
二、存储过程的语法
以添加部门的代码为例
DELIMITER $$
USE `vhr`$$
DROP PROCEDURE IF EXISTS `addDep`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(IN depName VARCHAR(32),IN parentId INT,IN enabled BOOLEAN,OUT result INT,OUT result2 INT)
BEGIN
DECLARE did INT;
DECLARE pDepPath VARCHAR(64);
INSERT INTO department SET NAME=depName,parentId=parentId,enabled=enabled;
SELECT ROW_COUNT() INTO result;
SELECT LAST_INSERT_ID() INTO did;
SET result2=did;
SELECT depPath INTO pDepPath FROM department WHERE id=parentId;
UPDATE department SET depPath=CONCAT(pDepPath,'.',did) WHERE id=did;
UPDATE department SET isParent=TRUE WHERE id=parentId;
END$$
DELIMITER ;
DELIMITER 定义
定义
为语句的结束符， 默认语句的结束符为";" 分号 ，防止语句写一句提交一句先设置为$$
USE `vhr`$$ 使用数据库
DROP PROCEDURE IF EXISTS `addDep`$$ 如果存在就删除addDep程序
CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(IN depName VARCHAR(32),IN parentId INT,IN enabled BOOLEAN,OUT result INT,OUT result2 INT) 创建addDep程序 IN代表传入参数，OUT代表传出结果
BEGIN 开始 END 结束
DECLARE 声明变量
INSERT INTO department SET NAME=depName,parentId=parentId,enabled=enabled; 用传进来的参数添加一条语句
SELECT ROW_COUNT() INTO result; 查询刚才成功添加语句的行数 并把结果存入 result
SELECT LAST_INSERT_ID() INTO did; 查询刚刚添加的语句 生成的id 并把结果存入did
SET result2=did; 把did赋值给result2
SELECT depPath INTO pDepPath FROM department WHERE id=parentId; 查询parentId的depPath 存入 pDepPath
UPDATE department SET depPath=CONCAT(pDepPath,'.',did) WHERE id=did; 更新新添加的语句的 depPath， CONCAT(pDepPath,'.',did)拼接字符串
UPDATE department SET isParent=TRUE WHERE id=parentId; 将父ID下的isParent设置为true执行完成之后 数据库中显示创建的存储过程
三、controller、service、mapper层以及mapper.xml的语句
1、mapper.xml
Statement的实现类：PreparedStatement 和 CallableStatement。
CALLABLE对应CallableStatement
call addDep（）就是调用addDep方法 里面就是创建的时候 设置的输入参数 与输出结果
<!--调用存储过程进行数据存储-->
<select id="addDep" statementType="CALLABLE">
call addDep(#{name,mode=IN,jdbcType=VARCHAR},#{parentId,mode=IN,jdbcType=INTEGER},#{enabled,mode=IN,jdbcType=BOOLEAN},
#{result,mode=OUT,jdbcType=INTEGER},#{id,mode=OUT,jdbcType=INTEGER})
</select>2、mapper层、service层、controller层
其中controller层中值得说的一点是 传入的参数dep 会在执行完语句后 将存储过程返回的结果封装到dep里 不需要返回值 直接调用
//mapper层
void addDep(Department dep);
//service层
public void addDepartment(Department dep) {
dep.setEnabled(true);
departmentMapper.addDep(dep);
}
//controller层
@PostMapping("/")
public RespBean addDepartment(@RequestBody Department dep){
departmentService.addDepartment(dep);
if(dep.getResult() == 1){
return RespBean.ok("添加成功",dep);
}
return RespBean.error("添加失败");
}四、测试结果fuji
"status": 200,
"msg": "添加成功",
"obj": {
"id": 108,
"name": "运维三部",
"parentId": 92,
"depPath": null,
"enabled": true,
"children": [],
"result": 1,
"parent": null
}
}附加：删除部门的存储过程的语句
语句逻辑：先判断要删除的部门是否是父部门，如果是，删除失败，返回结果为-2
再判断该部门是否有员工，如果有删除失败
然后就是直接删除 并判断是否还存在parentId与删除部门相同的部门，如果不存在
将 父部门的isParent设置为false
DELIMITER $$
USE `vhr`$$
DROP PROCEDURE IF EXISTS `deleteDep`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDep`(IN did INT,OUT result INT)
BEGIN
DECLARE ecount INT;
DECLARE pid INT;
DECLARE pcount INT;
DECLARE a INT;
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;
IF ecount>0 THEN SET result=-1;
ELSE
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;
IF pcount=0 THEN UPDATE department SET isParent=FALSE WHERE id=pid;
END IF;
END IF;
END IF;
END$$
DELIMITER ;
