mysql存储过程系列一：递归查询

   项目中需要用到递归查询的功能，所以研究了一下。

 

    首先表的结构如下：

     Table: tb_department

	Field	Type	Comment
	id	bigint(20) NOT NULL	机构编号
	seq_no	varchar(100) NULL	机构编码（规则：省级机构：1-9，市级：10-99，县级100-999，网格：大于1000）
	parent_seq_no	bigint(20) NULL	上级机构编码
	name	varchar(50) NULL	机构名
	flag	int(1) NULL	机构所属的级别（1表示省级，2表示市级，3表示县级，4表示网格级别）
	priority	int(4) NULL	机构排序
	remarks	varchar(500) NULL	备注

     只有上面标注为红色的字段会用到，其他的字段不需要用到。这张表存放的是部门的信息，其中parent_seq_no存放的是上级部门的id号（注意不是上级部门的seq_no）。这样就构成了上下级关系，就可以形成递归查询的条件了。

 

    创建表的sql为： 
CREATE TABLE `tb_department` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '机构编号',
  `seq_no` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT '机构编码（规则：省级机构：1-9，市级：10-99，县级100-999，网格：大于1000）',
  `parent_seq_no` bigint(20) DEFAULT NULL COMMENT '上级机构编码',
  `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '机构名',
  `flag` int(1) DEFAULT NULL COMMENT '机构所属的级别（1表示省级，2表示市级，3表示县级，4表示网格级别）',
  `priority` int(4) DEFAULT NULL COMMENT '机构排序',
  `remarks` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `seq_no_UNIQUE` (`seq_no`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='部门表，在组织机构管理中的部门管理的信息存储在这张表中'
  

   表中的数据暂时有如下几行：

id	seq_no	parent_seq_no	name	flag	priority	remarks
1	1	(NULL)	湖北省	1	1	(NULL)
2	2	1	武汉市	2	2	(NULL)
3	0001	2	洪山区	3	1000	(NULL)
4	0002	2	青山区	3	10001	(NULL)
5	10001	3	洪山区网格1	4	10002	(NULL)
6	10003	3	洪山区网格2	4	10004	(NULL)
7	10005	4	青山区网格1	4	10006	(NULL)
8	10007	4	青山区网格2	4	10008	(NULL)
10	0003	2	江岸区	3	21	而
11	0030	10	一元路网格	322	23	(NULL)
12	0032	10	解放大道网格	3	23	(NULL)
13	0031	10	沿江大道网格	3	12	(NULL)
14	0033	10	分销渠道网格	99	99	(NULL)

 下面这张图列出了部门的上下级关系：

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

 

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

 

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

 CREATE DEFINER = 'root'@'%'
PROCEDURE findChildList(IN departmentId BIGINT)
BEGIN
  DECLARE v_dep         INTEGER DEFAULT -1;
  declare done integer DEFAULT 0;
  DECLARE C_dep CURSOR FOR SELECT d.id
                           FROM
                             tb_department d
                           WHERE
                             d.parent_seq_no = departmentId;
  DECLARE CONTINUE HANDLER FOR NOT found set done=1;
  SET @@max_sp_recursion_depth = 10;
 INSERT INTO tmp_Dep VALUES (departmentId);
  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'@'localhost'
PROCEDURE findDepList(IN departmentId BIGINT)
BEGIN

  DROP TEMPORARY TABLE IF EXISTS tmp_Dep;
  CREATE TEMPORARY TABLE tmp_Dep(
  depId INTEGER
);
  DELETE
FROM
  tmp_Dep;
  CALL findChildList(departmentId);
  SELECT distinct depId
FROM
  tmp_Dep order by depId;
END

 

现在在mysql中创建好这两个存储过程，并且执行 call findDepList(3)就会输出3,5,6

 

临时表是执行递归查询的关键，要想使用存储过程执行后的结果，使用临时表是一种很好的办法，在这个例子中，在主调程序（findDepList）中创建好了临时表tmp_Dep，在被调用程序（findChildList）中往临时表中插入数据，最后在主调程序中可以使用递归查询后的数据。注意：单纯执行findChildList会报语法错误。这样在我们的业务逻辑中如果需要查询一个部门的子部门，只需要将findDepList的代码移植过去，既创建中间表，然后执行存储过程，最后就可以从中间表中查询出需要的结果。