通过部门ID查询子部门
例如:select getDepartmentListName(1);getDepartmentListName:函数名；1：参数
获取结果：顶级部门/一级子部门/二级子部门/三级子部门/四级部门
DELIMITER $$
USE `sporch`$$
DROP FUNCTION IF EXISTS `getDepartmentListName`$$
CREATE DEFINER=`sporch`@`%` FUNCTION `getDepartmentListName`(depId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
DECLARE depName VARCHAR(1000) DEFAULT '';
DECLARE tmpName VARCHAR(1000) DEFAULT '';
SET @depId = depId;
#循环递归条件
WHILE @depId IS NOT NULL AND @depId > 0 DO
SELECT departmentPid,departmentName INTO @depId,tmpName FROM t_department WHERE departmentId=@depId;
IF tmpName IS NULL OR tmpName = '' THEN
SET @depId = NULL;
ELSE
#判断是否为空
IF tmpName != '' THEN
SET depName = CONCAT(tmpName,'/',depName);
END IF;
#退出
IF @depId = 0 THEN
SET @depId = NULL;
END IF;
END IF;
END WHILE;
IF depName IS NOT NULL AND LENGTH(depName) > 2 THEN
SET depName = REVERSE(SUBSTR(REVERSE(depName),2));
END IF;
RETURN depName;
END$$
DELIMITER ;
