组织架构mysql递归存储过程

首先放出表设计 表一：学校部门表 表二：学校部门组织关系表

DROP TABLE IF EXISTS `school_department_1`;
CREATE TABLE `school_department_1`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `department_key` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门唯一key',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `is_delete` int(1) NULL DEFAULT 0 COMMENT '是否删除 0 未删除 1已删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学校部门表' ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `school_department_1_relation`;
CREATE TABLE `school_department_1_relation`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `department_id` bigint(20) NULL DEFAULT NULL COMMENT '部门id',
  `parent_id` bigint(20) NULL DEFAULT NULL COMMENT '父级id',
  `child_id` bigint(20) NULL DEFAULT NULL COMMENT '子节点id',
  `level_depth` int(20) NULL DEFAULT NULL COMMENT '层级深度',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  `is_delete` int(1) NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '学校部门组织关系表' ROW_FORMAT = Dynamic;

现在要做的是，给定一个部门的id，查询出他下面所有的子部门。如给定部门id为3，那么需要输出3,5,6

需要编写两个存储过程，一个是基础的存储过程，执行递归操作，命名为：findChildList,还有一个是调用该存储过程的存储过程，使用递归存储过程执行后的结果,命名为：findDepList。

首先定义执行递归操作的存储过程：findChildList

CREATE DEFINER=`root`@`%` PROCEDURE `findChildList`(IN `parentId` bigint)
BEGIN
  DECLARE v_dep INT DEFAULT -1;
	DECLARE done INT DEFAULT 0;
	DECLARE C_dep CURSOR FOR SELECT
	d.id   
	FROM
		school_department_1_relation d 
	WHERE
		d.parent_id = parentId;
DECLARE CONTINUE HANDLER FOR NOT found SET done = 1;
  SET @@max_sp_recursion_depth = 10;
	INSERT INTO tmp_Dep VALUES (parentId);
	OPEN C_dep;
	FETCH C_dep INTO v_dep;
	WHILE (done =0)
	DO
	 CALL findChildList(v_dep);
	 FETCH C_dep INTO v_dep;
	 END WHILE;
END

再定义调用递归操作的存储过程：findDepList

CREATE DEFINER=`root`@`%` PROCEDURE `findDepList`(IN parentId BIGINT)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS tmp_Dep;
	CREATE TEMPORARY TABLE tmp_Dep(
	  depId INTEGER
	);
	DELETE
	FROM
	tmp_Dep;
	CALL findChildList(parentId);
	SELECT distinct a.depId as id,
	c.level_depth as levelDepth,
	c.parent_id as parentId,
	c.child_id as childId,
	b.department_name,
	b.department_key 
	FROM
	tmp_Dep a  
	left join school_department_1_relation c on a.depId = c.id
	left join school_department_1 b on b.id = c.department_id
	where c.is_delete = 0 and b.is_delete = 0
	order by a.depId;
END

这样输入一个部门id就能查询所有的子级