创建数据库函数脚本

DROP FUNCTION IF EXISTS queryChildrenList;
DELIMITER $$
CREATE DEFINER = `root`@`%` FUNCTION queryChildrenList(Id INT,CompanyId INT)
RETURNS varchar(4000)
    READS SQL DATA
BEGIN
#Routine body goes here...
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
#设置当前节点Id
SET sTempChd = CAST(Id AS CHAR);
#遍历的终止条件为当前节点的Id小于0
WHILE sTempChd > 0 DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
#查找当前节点的父节点
SELECT GROUP_CONCAT(parent_id) INTO sTempChd FROM channel WHERE company_id = CompanyId AND FIND_IN_SET(channel_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;
$$

DELIMITER ;