mysql获取部门及以下部门的id、获取上级部门的名称拼接完整名称

-- 获取当前id包含以下层级的所有id
CREATE DEFINER=`root`@`%` FUNCTION `func_getChildIds`(orgid varchar(512)) RETURNS varchar(2048) CHARSET utf8
BEGIN
    #根据当前组织id获取组织及以下所有组织id集合
    DECLARE oTemp VARCHAR(4000);
    DECLARE oTempChild VARCHAR(4000);

    set oTemp = '';
    set oTempChild = CAST(orgid AS CHAR);
    
    WHILE oTempChild IS NOT NULL
    DO
    IF oTemp = '' THEN 
        SET oTemp = oTempChild;
    ELSE 
        SET oTemp = CONCAT(oTemp,',',oTempChild);
    END IF;
    SELECT GROUP_CONCAT(ID) INTO oTempChild FROM tf_store_info WHERE FIND_IN_SET(pid,oTempChild) > 0;
    END WHILE;
    RETURN oTemp;
END

 mysql执行函数报错 1418 错误：

1.先查看函数功能是否开启：show variables like '%func%';
2.开启：SET GLOBAL log_bin_trust_function_creators = 1;
3.关闭：SET GLOBAL log_bin_trust_function_creators = 0;
————————————————

福建移动/测试1/66666666

CREATE DEFINER=`root`@`%` FUNCTION `getParentDeptName`(`depatId` INT) RETURNS varchar(512) CHARSET utf8
BEGIN
    DECLARE dept_id INT;
    DECLARE parentDept INT DEFAULT 0;
    DECLARE tag INT DEFAULT 0;
    DECLARE dcode VARCHAR(64) DEFAULT 0;
    DECLARE parentDeptNames VARCHAR(512) DEFAULT NULL;
    DECLARE deptName VARCHAR(128) DEFAULT NULL;
    SET dept_id = depatId;
    
    loop2: WHILE tag=0 DO
    
        SELECT last_level_name INTO deptName FROM tf_store_info WHERE id=dept_id;
        
        IF (deptName IS NOT NULL) THEN
            IF (parentDeptNames IS NOT NULL) THEN
              SET parentDeptNames = CONCAT(deptName, '/', parentDeptNames);
            ELSE
              SET parentDeptNames = CONCAT(deptName);
            END IF;
        END IF;
        
        IF dcode='1'  THEN
            SET parentDept = dept_id;
            SET tag=1;
            LEAVE loop2;
        ELSE
            SET dept_id = (SELECT t.pid FROM tf_store_info t WHERE t.id=dept_id);
        END IF;
        
        IF dept_id = 0 THEN
            SET tag=1;
            LEAVE loop2;
        END IF;
        
    END WHILE loop2;
    RETURN parentDeptNames;
END

-- 查完整名称
select
            t1.id,
            t1.pid,t1.last_level_name,
            CONCAT(
                Case when t7.last_level_name Is not Null Then CONCAT(t7.last_level_name, '/') Else '' End,
                Case when t6.last_level_name Is not Null Then CONCAT(t6.last_level_name, '/') Else '' End,
                Case when t5.last_level_name Is not Null Then CONCAT(t5.last_level_name, '/') Else '' End,
                Case when t4.last_level_name Is not Null Then CONCAT(t4.last_level_name, '/') Else '' End,
                Case when t3.last_level_name Is not Null Then CONCAT(t3.last_level_name, '/') Else '' End,
                Case when t2.last_level_name Is not Null Then CONCAT(t2.last_level_name, '/') Else '' End,
                t1.last_level_name
            ) As name11
        from tf_store_info t1
        left join tf_store_info t2 on t2.id = t1.pid
        left join tf_store_info t3 on t3.id = t2.pid
        left join tf_store_info t4 on t4.id = t3.pid
        left join tf_store_info t5 on t5.id = t4.pid
        left join tf_store_info t6 on t6.id = t5.pid
        left join tf_store_info t7 on t7.id = t6.pid