Mysql存储过程以及实践应用

存储过程

mysql5.0之后开始支持存储的过程。
存储过程 是一种在数据库中存储复杂的程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL 语句集，经编译创建并保存在数据库中 用户可以通过指定的存储过程并设定参数（需要时）来调用执行。
简单解释就是
存储过程其实就是能完成一定操作的一组SQL语句。
优点：

1. 存储过程可封装，并隐藏复杂的商业逻辑
2. 存储过程可以回传值，并可以接受参数
3. 存储过程无法使用SELECT 指令来运行 因为它是子程序 与查看表 数据表或用户定义函数不同。
4. 存储过程可以在数据校验 强行实行商业逻辑等。。
   缺点：
   1.存储过程 往往定制化预特地昂的数据库上，因为支持的编程语言不同 当切换到其他厂商的数据库系统是要重写原有的储存过程
   2，存储过程的性能 受限各种数据库系统

存储过程的创建和调用

1， 存储过程就是具有名字的一段代码 用来完成一个特定的功能
2，创建的存储过程保存在数据库的数据字典中。

创建

# Mysql 存储过程中的关键语法
# 声明语句结束符，可以自定义：
DELIMITER $$
# 或
DELIMITER //
# 声明储存的过程
CREATE PROCEDURE 名字（IN 变量 int）
# 存储过程开始和结束符号
BEGIN... END
# 变量赋值
SET @变量 =1
#变量定义：
DECLARE 变量 int unsigned default 40000
# 创建mysql 存储过程 存储函数
create procedure 存储过程名（参数）
#  存储过程体
create function 存储函数名（参数）

调用存储过程

call 名字[(传参)];

存储过程的参数

Mysql 存储过程的参数用在存储过程的定义 共有三种参数类型 in,out,inout 形式如：

CREATEPROCEDURE 存储过程名([[IN|OUT | INOUT] 参数名 数据类型...])

IN 输入参数 ：表示调用者向过程传入值（传入值可以是字面量或者变量）
OUT 输出参数： 表示过程向调用传出值（可以返回多个值）（传出值只能是变量）
INOUT输入输出参数:即表示调用者向过程传入值 又表示过程向调用者传出值（值只能是变量）
注意:
1,如果过程没有参数，也必须在过程名后面写上小括号列：

CREATE PROCEDURE 名字 （[proc_parameter[,....]]）

2,确保参数的名字不等于列的名字 否则在过程体中 参数名被当做列名来处理
建议：
输入值使用in 参数
返回值使用out参数
inout 参数就尽量的少用

变量

变量定义

局部变量声明一定要放在存储过程体的开始：

DECLARE variable_name [,variable_name] datatype [DEFAULT value];
# 其中 datatype 为Mysql 的数据类型 如 int,float,date,varchar(length)

变量赋值

SET 变量名 = 表达式值 [,variable_name = expression....]

用户变量

在存储过程中使用用户变量

CREATE PROCEDURE Greetword() SELECT CONCAT(@FJJ,'World');
SET @FJJ ='hello'
CALL Greetword()

结果

项目中使用存储过程

需求

具体数据

最后那个是否是父部门 就是说如果是父部门的话就是1 说明他有对应的子部门。
需求
假如现在在乌当区市场下添加了一个新的部门，添加是一条sql 语句计较好写，但是我们的depPath 我们需要先查到 乌当区市场的depPath ，然后在点我们的 我们添加的那条数据的id 需要自查一下自己的id ,然后再去更新我们的 depPath .而且现在我们的乌当区的isParent 是0 默认是没有子部门所以是0 但是当我们在他们下面插入一条子部门的时候这个时候他的isParent 就不在是0 了还需要一条更新语句。
大概需要5条Sql 语句这个时候就用到了存储过程
添加部门的存储过程

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 t_department set name=depName,parentId=parentId,enabled=enabled;
  #查询受影响的行数 并且放回一个Int类型的结果回去
  select row_count() into result;
  # 获取最后一次插入的主键ID 
  select last_insert_id() into did;
  # 把 最后一次插入的主键ID 赋值给我们的 结果2 放回
  set result2=did;
  # 查询我们的 ID 就是上面的储存乌当区的 depPath 
  select depPath into pDepPath from t_department where id=parentId;
  # 然后执行更新 把我们自己插入的 主键ID 拼接 乌当区的parentId
  update t_department set depPath=concat(pDepPath,'.',did) where id=did;
  # 更新一下乌当区部门的 isParent 因为他有了子部门   ps (最后的解释是根据上面例子解释的)
  update t_department set isParent=true where id=parentId;
end

删除的存储过程

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 t_department where id=did and isParent=false;
  # 如果当前结果是0 的话我们的结果就假设是-2 就查不到的意思
  if a=0 then set result=-2;
  else
  # 如果不是flase 的话就去查员工表看看这个部门下有没有员工 假设这个部门下是有员工的话也是不可以删除的
  select count(*) into ecount from t_employee where departmentId=did;
  if ecount>0 then set result=-1;
  else 

  select parentId into pid from t_department where id=did;
  delete from t_department where id=did and isParent=false;
  select row_count() into result;
  select count(*) into pcount from t_department where parentId=pid;
  if pcount=0 then update t_department set isParent=false where id=pid;
  end if;
  end if;
  end if;
end

结合Springboot使用

Controller 类的添加和删除

    //添加部门
    @ApiOperation("添加部门")
    @PostMapping("/add")
    public Results addDep(@RequestBody TDepartment department) {
        return departmentService.addDep(department);
    }
    // 删除部门
    @ApiOperation("删除部门")
    @PostMapping("/delete")
    public Results deleteDep(@RequestBody Integer id) {
        return departmentService.deleteDepp(id);
    }

服务层

    @Override
    public Results addDep(TDepartment department) {
        department.setEnabled(true);
        departmentMapper.addDep(department);
        if (1 == department.getResult()) {
            return Results.fail("添加成功！");
        }
        return Results.fail("添加失败！");
    }

    @Override
    public Results deleteDepp(Integer id) {
        TDepartment department = new TDepartment();
        department.setId(id);
        departmentMapper.deleteDep(department);
        if (-2 == department.getResult()) {
            return Results.fail("该部门下还有子部门，删除失败！");
        }
        if (-1 == department.getResult()) {
            return Results.fail("该部门下还有员工，删除失败！");
        }
        if (1 == department.getResult()) {
            return Results.succ("删除成功！");
        }

        return Results.fail("删除失败！");
    }
}

Dao 层

    <!-- 添加部门 -->
    <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>
    <!-- 删除部门 -->
    <select id="deleteDep" statementType="CALLABLE">
        call deleteDep(#{id,mode=IN,jdbcType=INTEGER},#{result,mode=OUT,jdbcType=INTEGER})
    </select>

结束 这样就可以了

测试删除

添加