mysql递归查询 存储过程

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

DELIMITER //

CREATE PROCEDURE GetAllManagers(IN employee_id INT)
BEGIN
    DECLARE current_manager_id INT;
    DECLARE done INT DEFAULT FALSE;

    CREATE TEMPORARY TABLE IF NOT EXISTS temp_managers (
        id INT,
        name VARCHAR(100)
    );

    SET current_manager_id = employee_id;

    WHILE NOT done DO
        INSERT INTO temp_managers (id, name)
        SELECT id, name FROM employees WHERE id = current_manager_id;

        SET current_manager_id = (SELECT manager_id FROM employees WHERE id = current_manager_id);

        IF current_manager_id IS NULL THEN
            SET done = TRUE;
        END IF;
    END WHILE;

    SELECT * FROM temp_managers;
END //

DELIMITER ;

CALL GetAllManagers(5); -- 假设我们要查询员工ID为5的所有上级