mysql中递归函数返回值

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1 -- 假设查询员工ID为1的所有下属
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

CREATE TABLE tree_nodes (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    parent_id INT
);

DELIMITER //

CREATE FUNCTION get_all_children(node_id INT)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(1000);
    DECLARE child_id INT;
    DECLARE done INT DEFAULT FALSE;

    DECLARE cur CURSOR FOR SELECT id FROM tree_nodes WHERE parent_id = node_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET result = '';

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO child_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET result = CONCAT(result, child_id, ', ');
        SET result = CONCAT(result, get_all_children(child_id));
    END LOOP;

    CLOSE cur;

    RETURN result;
END //

DELIMITER ;

SELECT get_all_children(1); -- 假设查询节点ID为1的所有子节点