mysql数据库递归函数

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

WITH RECURSIVE manager_hierarchy AS (
    -- Anchor member: select the initial employee
    SELECT id, name, manager_id
    FROM employees
    WHERE id = ? -- Replace ? with the target employee ID

    UNION ALL

    -- Recursive member: select the manager of the current employee
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN manager_hierarchy mh ON e.id = mh.manager_id
)
SELECT * FROM manager_hierarchy;

DELIMITER //

CREATE FUNCTION count_subordinates(employee_id INT) RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE count INT;
    SELECT COUNT(*) INTO count FROM employees WHERE manager_id = employee_id;

    IF count > 0 THEN
        DECLARE sub_count INT;
        DECLARE cur_id INT;
        DECLARE done INT DEFAULT FALSE;

        DECLARE cur CURSOR FOR SELECT id FROM employees WHERE manager_id = employee_id;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN cur;

        read_loop: LOOP
            FETCH cur INTO cur_id;
            IF done THEN
                LEAVE read_loop;
            END IF;

            SET sub_count = sub_count + count_subordinates(cur_id);
        END LOOP;

        CLOSE cur;

        RETURN count + sub_count;
    ELSE
        RETURN count;
    END IF;
END //

DELIMITER ;