微人事中的存储过程

前言

　　说起存储过程，可能少数人跟我一样是第一次听说；没关系，以下这篇博客，我会简要地讲述存储过程的作用、使用以及项目中的应用

1.存储过程作用和使用

　　存储过程是由一系列的SQL语句组成

　　1.1 作用

1. 存储过程只在编译时进行编译，以后调用都不用再次编译，提升了数据库执行速度
2. 复杂SQL语句与实际相结合
3. 可重复使用，减少开发人员工作量
4. 安全性高，可防止SQL注入

　　1.2 定义

delimiter $$ -- 声明结束符，可自定义，我这里结束符定义为$$
create procedure 存储过程名(参数1,参数 2,...)
begin
    一系列SQL语句
end $$

　　1.3 调用

call 存储过程名(参数1,参数2,...)

　　1.4 定义和调用简单示例

-- 1.定义一个存储过程查询student表中的所有数据
delimiter $$
create procedure exp()
begin
    select * from student
end $$

-- 2.调用该存储过程
call exp() $$

2.存储过程参数和变量

　　2.1 参数和变量

1. MySQL中支持存储过程中的参数调用，MySQL支持的参数有IN、OUT、INOUT三种
2. IN：调用存储过程时由外部输入的参数
3. OUT：存储过程调用后输出的参数
4. INOUT：传入存储过程并进行修改的参数
5. 在存储过程调用时，需要指定参数类型
6. into 变量是将前面参数的值赋给后面参数，常用与select语句
7. set变量是将后面参数的值赋值给前面变量，常用于update语句

　　2.2 示例　

　　　　2.2.1 创建数据库test以及数据表student

drop database if exists `test`
create database `test`
use `test`
drop table if exists `student`
create table `studnet`(
    `id` int(11) not null,
    `name` vachar(32) default null,
    `mobile` varchar(11) default null,
    `address` varchar(255) default null,
    primary key(`id`)
)engine=innodb default chrset=utf8;

insert into studnet (`id`, `name`, `mobile`, `address`)
values (1, '小库', '18571464245', '湖北省黄冈市')

　　　　2.2.2 创建存储过程（这个也是在数据库中创建的）

delimiter $$
create pro_student (in i_id int(11), out o_name varchar(32), out o_mobile varchar(11), out o_address varchar(255))
begin
    select name, mobile, address into o_name, o_mobile, o_address
    from student where id = i_id
end $$

　　　　2.2.3 xml中的存储过程调用

<select id="getStudentById" statementType="CALLABLE">
    {
        call pro_student(
            #{id,mode=IN,jdbcType=Integer},
            #{name,mode=OUT,jdbvType=varchar},
            #{mobile,mode=OUT,jdbcType=varchar},
            #{address,mode=OUT,jdbvType=varchar}
        )
    }
</select>  

3.微人事中的存储过程调用

　　3.1 addDep

　　　　3.1.1 addDep存储过程定义

1. 插入指定的部门信息
2. 查找插入后部门的id
3. 查找父部门的depPath
4. 更新新插入部门的depPath
5. 更新新插入部门父部门的isParent为true

delimiter $$
use `vhr_product` $$
drop procedure if exists `add_dep` $$
create definer=`root`@`localhost`procedure `add_dep`(
IN name varchar(32), IN parentId int, IN enabled boolean,
OUT result int, OUT result2 int)
begin
    declare did int;
    declare p_dep_path varchart(32);
    -- 1.插入指定的部门信息
    insert into department set name=name,     
    parent_id=parentId,enabled=enabled;
    -- 2.1 根据ROW_COUNT()方法将受影响的行数(即成功为1)存入result中
    select ROW_COUNT() into result;
    -- 2.2 查找插入之后的部门id，将结果存入did中
    select LAST_INSERT_ID() into did;
    -- 3. 查找父部门的dep_path,并存入p_dep_path中
    select dep_path into p_dep_path from department where     
    id = parentId
    -- 4 更新插入语句的dep_path,contact(p_dep_path,'.',did)
    update department dep_path=contact(p_dep_path,'.',did) 
    where id = did;
    -- 5.更新新插入部门的isParent为1
    update department set is_parent=true where id = parentId;
end $$

　　　　3.1.2 addDep存储过程调用

<select id="addDep" statementType="CALLABLE" resultType="java.lang.Integer">
    call add_dep(#{name,mode=IN,jdbcType=VARCHAR},     
   #{parentId,mode=IN,jabcType=VARCHAR},  
   #{enabled,mode=in,jdbcType=BOOLEAN},
   #{result,mode=OUT,jabcType=INTEGER},  
   #{id,mode=OUT,jdbcType=INTEGER}
    )
</select>

　　3.2 deleteDep

　　　　3.2.1 deleteDep存储过程定义

1. 先判断删除部门是不是父部门，如果是，删除失败，返回-2
2. 再判断该部门是否有员工，如果有，删除失败
3. 查找父部门的depPath
4. 直接删除部门
5. 判断是否存在与删除部门相同parentId的部门，如果不存在将父部门的is_parent设置为false

delimiter $$
use `vhr_project` $$
drop procedure if exists `delete_dep` $$
create definer=`root`@`localhost`procedure `delete_dep`(
IN did int, OUT result int)
begin
    declare e_count int;
    declare pid int;
    declare p_count int;
    -- 1.1通过传入的id删除没有子部门的指定部门，并将删除部门数量赋值给a
    select COUNT(*) into a from department where id=did and 
    is_parent=true;
    -- 1.2 如果a不为0，那么删除的指定部门有子部门，返回-2
    if a != 0 then
        set result = -2;
    else
        -- 2.1 查询该员工部门的员工数
        select COUNT(*) into e_count from employee where 
        department_id = did; 
        -- 如果有，删除失败，返回-1
        if e_count>0 then
            set result=-1;
        else
            -- 3.查询指定删除部门的parent_id，赋值给pid
            select parent_id into pid from department where id=did;
            -- 4.删除没有子部门的指定部门
            delete from department where id = did and 
            is_parent = false;
            -- 5.查询删除部门的父部门还有没有子部门
            select COUNT(*) into p_count from department where 
            parent_id = pid;
            if p_count=0 then
                update department set is_parent=false where id=pid;
            end if;
        end if;
    end if;
end $$    

　　　　3.2.2 deleteDep存储过程调用

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