DELIMITER $$
USE `rcj`$$
DROP FUNCTION IF EXISTS `getInviteMemberList`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `getInviteMemberList`(fromInviteCode VARCHAR(1000)) RETURNS INT
BEGIN
DECLARE total INT DEFAULT 0; # 返回叶子节点个数
DECLARE tempChild VARCHAR(1000); # 临时存放子节点
SET tempChild = fromInviteCode; # 将int类型转换为String
WHILE tempChild IS NOT NULL DO # 循环,用于查询节点下所有的子节点
SET total=total+1; # 存入到返回结果中
SELECT GROUP_CONCAT(INVITE_CODE) INTO tempChild FROM PERM_USER WHERE FIND_IN_SET(FROM_INVITE_CODE, tempChild) > 0; # 查询节点下所有子节点
END WHILE;
RETURN total-1; # 返回数量
END$$
DELIMITER ;