目录
1.什么是存储过程
2.存储过程的语法结构:
3.如何调用存储过程
4.实例
1.什么是存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来调用存储过程。
2.存储过程的语法结构:
create procedure 存储过程名称(参数);//可以无参,也可以带参数,和java的方法名差不多
有参数的设置:(in|out|inout 参数名称 数据类型)
in:输入参数,表示该参数的值必须调用存储过程时指定
out:输出参数,表示可以被存储过程改变,并且可以返回
inout:输入输出参数,在调用时指定,可以被改变和返回
3.如何调用存储过程
call 存储过程名称();//如果定义了有参数这里也需要传入参数,和java调用方法一样
4.实例
我们在删除部门的时候,需要先判断该部门下是否有子部门,如果有的话,则不能删除,如果没有子部门的话,需要判断该部门下是否有员工,如果没有的话,可以直接删除,如果有的话,不能删除,这个业务使用sql语句也可以实现,但明显麻烦许多,所以这里使用存储过程。
begin
-- 声明函数
declare did int;
declare pDepPath varchar(64);
-- 插入一条数据设置它的enable、名称以及父id
-- 设置他的父id是为了找到它的完整路径
insert into t_department set name=depName,parentId=parentId,enabled=enabled;
-- 受影响的行数设置为result
select row_count() into result;
-- 把插入行的id设置为did
select last_insert_id() into did;
-- 将did的值赋值给result2
set result2=did;
select depPath into pDepPath from t_department where id=parentId;
-- concat是连接两个字符串
update t_department set depPath=concat(pDepPath,'.',did) where id=did;
update t_department set isParent=true where id=parentId;
end
begin
declare ecount int;
declare pid int;
declare pcount int;
declare a int;
-- 判断是不是父节点,如果是的话就返回-2
select count(*) into a from t_department where id=did and isParent=false;
if a=0 then set result=-2;
else
-- 判断部门中是否有员工,如果有,就返回-1
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;
-- 删除之后需要判断其父节点下面还有没有其它子节点,如果没有,则将父节点设置为0
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
注意:如果丢了column="id",会报错
//添加子菜单
@ApiModelProperty("部门子菜单列表")
@TableField(exist = false)
private List<Department> children;
@ApiModelProperty("添加返回结果,存储过程使用")
@TableField(exist = false)
private Integer result;
@Override
public RespBean addDepartment(Department department) {
department.setEnabled(true);
departmentMapper.addDepartment(department);
if (1==department.getResult()){
return RespBean.success("添加成功");
}else{
return RespBean.error("添加失败");
}
}
@Override
public RespBean deleteDepartment(Integer id) {
Department dep = new Department();
dep.setId(id);
departmentMapper.deleteDepartment(dep);
if (-2 == dep.getResult()){
return RespBean.error("该部门下有子部门,删除失败");
}else if (-1 == dep.getResult()){
return RespBean.error("该部门下有员工,删除失败");
}else if (1 == dep.getResult()){
return RespBean.error("删除成功");
}
return RespBean.error("删除失败");
}
<resultMap id="departmentAll" type="com.he.server.pojo.Department" extends="BaseResultMap">
<collection property="children" ofType="com.he.server.pojo.Department"
select="com.he.server.mapper.DepartmentMapper.listDepartment" column="id">
</collection>
</resultMap>
<!--删除部门-->
<select id="deleteDepartment" statementType="CALLABLE">
call deleteDep(#{id,mode=IN,jdbcType=INTEGER},#{result,mode=OUT,jdbcType=INTEGER})
</select>
<!--添加部门,以存储过程的形式添加,所以可以写成select-->
<!--CALLABLE存储过程或者执行函数用这个-->
<!--PREPARED预编译用这个,默认是这个-->
<!--STATEMENT先执行后编译是这个,用$写入-->
<select id="addDepartment" 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="listDepartment" resultMap="departmentAll">
select
<include refid="Base_Column_List"/>
from t_department
where parentId = #{parentId}
</select>