DELIMITER $$
DROP FUNCTION IF EXISTS `getChildList`$$
CREATE FUNCTION `getChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempChd VARCHAR(1000);
SET sTemp = '$';
SET sTempChd =CAST(rootId AS CHAR);
WHILE sTempChd IS NOT NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(`deptId`) INTO `sTempChd` FROM dept WHERE FIND_IN_SET(`parentId`,`sTempChd`)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;
SELECT getChildList(1);
-- 擷取父ID
DELIMITER $$
DROP FUNCTION IF EXISTS `getParList`$$
CREATE FUNCTION `getParList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8 READS SQL DATA
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =rootId;
WHILE sTempPar is not null DO
SET sTemp = concat(sTemp,',',sTempPar);
SELECT group_concat(parent_id) INTO sTempPar FROM doc where parent_id<>id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;