mysql 递归更新

DELIMITER //

CREATE PROCEDURE UpdateSalary(IN emp_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_id INT;
    DECLARE cur_salary DECIMAL(10, 2);
    
    -- 创建一个游标来遍历员工及其下属
    DECLARE cur CURSOR FOR SELECT id, salary FROM employees WHERE id = emp_id OR manager_id = emp_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO cur_id, cur_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 更新当前员工的薪水
        UPDATE employees SET salary = salary * 1.1 WHERE id = cur_id;
        
        -- 递归调用存储过程来更新下属的薪水
        CALL UpdateSalary(cur_id);
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

CALL UpdateSalary(1); -- 假设我们要给ID为1的员工及其所有下属涨薪