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

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

 

    首先表的结构如下：

     Table: tb_department

FieldTypeComment   idbigint(20) NOT NULL机构编号
seq_novarchar(100) NULL机构编码（规则：省级机构：1-9，市级：10-99，县级100-999，网格：大于1000）
parent_seq_nobigint(20) NULL上级机构编码
namevarchar(50) NULL机构名
flagint(1) NULL机构所属的级别（1表示省级，2表示市级，3表示县级，4表示网格级别）
priorityint(4) NULL机构排序
remarksvarchar(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='部门表，在组织机构管理中的部门管理的信息存储在这张表中'
  

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

idseq_noparent_seq_nonameflagpriorityremarks11(NULL)湖北省11(NULL)221武汉市22(NULL)300012洪山区31000(NULL)400022青山区310001(NULL)5100013洪山区网格1410002(NULL)6100033洪山区网格2410004(NULL)7100054青山区网格1410006(NULL)8100074青山区网格2410008(NULL)1000032江岸区321而11003010一元路网格32223(NULL)12003210解放大道网格323(NULL)13003110沿江大道网格312(NULL)14003310分销渠道网格9999(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的代码移植过去，既创建中间表，然后执行存储过程，最后就可以从中间表中查询出需要的结果。