mysql递归函数返回值

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

WITH RECURSIVE employee_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE id = 1 -- 假设查询员工ID为1的员工及其下属
    UNION ALL
    SELECT e.id, e.name, e.manager_id, et.level + 1
    FROM employees e
    JOIN employee_tree et ON e.manager_id = et.id
)
SELECT * FROM employee_tree;

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

DELIMITER //

CREATE FUNCTION get_parent_categories(category_id INT)
RETURNS VARCHAR(1000)
DETERMINISTIC
BEGIN
    DECLARE result VARCHAR(1000);
    DECLARE parent_id INT;
    
    SET result = '';
    SET parent_id = category_id;
    
    WHILE parent_id IS NOT NULL DO
        SELECT name INTO @parent_name FROM categories WHERE id = parent_id;
        IF @parent_name IS NOT NULL THEN
            SET result = CONCAT(@parent_name, ' > ', result);
            SET parent_id = (SELECT parent_id FROM categories WHERE id = parent_id);
        ELSE
            SET parent_id = NULL;
        END IF;
    END WHILE;
    
    RETURN result;
END //

DELIMITER ;

SELECT get_parent_categories(5) AS parent_categories;

SET GLOBAL max_recursion_depth = 200;