SQL 存储过程：循环降薪至平均薪资达到 5000 元

CREATE PROCEDURE updata_salary_while(OUT num INT) BEGIN DECLARE avg_salary DECIMAL(10,2); DECLARE total_salary DECIMAL(10,2); DECLARE count_employee INT; DECLARE loop_count INT DEFAULT 0;

SELECT SUM(salary), COUNT(*) INTO total_salary, count_employee FROM employee;
SET avg_salary = total_salary/count_employee;

WHILE(avg_salary > 5000) DO
    UPDATE employee SET salary = salary * 0.98;
    SET loop_count = loop_count + 1;
    
    SELECT SUM(salary), COUNT(*) INTO total_salary, count_employee FROM employee;
    SET avg_salary = total_salary/count_employee;
END WHILE;

SET num = loop_count;

END$$

DELIMITER ;