MySQL流程控制语句用法与语法详解

一、条件判断语句

1. IF 语句

语法：

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]...
    [ELSE statement_list]
END IF

示例：学生成绩等级评定

DELIMITER //

CREATE PROCEDURE evaluate_student(IN student_id INT)
BEGIN
    DECLARE v_score DECIMAL(5,2);
    DECLARE v_grade VARCHAR(10);

获取学生成绩

  SELECT score INTO v_score FROM tb_student WHERE id = student_id;

IF 条件判断

 IF v_score >= 90 THEN
        SET v_grade = '优秀';
    ELSEIF v_score >= 80 THEN
        SET v_grade = '良好';
    ELSEIF v_score >= 60 THEN
        SET v_grade = '及格';
    ELSE
        SET v_grade = '不及格';
    END IF;

输出结果

    SELECT CONCAT('学生ID: ', student_id, ', 分数: ', v_score, ', 等级: ', v_grade) AS result;
END //
DELIMITER ;

2. CASE 语句

两种形式：

形式一：简单 CASE（基于值匹配）

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE

形式二：搜索 CASE（基于条件判断）

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list]...
    [ELSE statement_list]
END CASE

示例：根据月份判断季节

DELIMITER //

CREATE FUNCTION get_season(month_num INT) 
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE season VARCHAR(10);

使用搜索CASE

   CASE
        WHEN month_num BETWEEN 3 AND 5 THEN SET season = '春季';
        WHEN month_num BETWEEN 6 AND 8 THEN SET season = '夏季';
        WHEN month_num BETWEEN 9 AND 11 THEN SET season = '秋季';
        WHEN month_num = 12 OR month_num = 1 OR month_num = 2 THEN SET season = '冬季';
        ELSE SET season = '无效月份';
    END CASE;
    
    RETURN season;
END //
DELIMITER ;

调用示例

SELECT get_season(6);  -- 
返回 '夏季'

二、循环控制语句

1. LOOP + LEA VE 循环

语法：

[begin_label:] LOOP

statement_list

END LOOP [end_label]

示例：计算1到100的和

DELIMITER //

CREATE PROCEDURE calculate_sum()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;
    
    sum_loop: LOOP
        SET total = total + i;
        SET i = i + 1;
        
         --使用LEA VE跳出循环
        IF i > 100 THEN
            LEA VE sum_loop;
        END IF;
    END LOOP sum_loop;
    
    SELECT total AS '1到100的和';
END //

DELIMITER ;

2. WHILE 循环

语法：

[begin_label:] WHILE search_condition DO

statement_list

END WHILE [end_label]

示例：生成斐波那契数列

DELIMITER //

CREATE PROCEDURE generate_fibonacci(n INT)
BEGIN
    DECLARE a INT DEFAULT 0;
    DECLARE b INT DEFAULT 1;
    DECLARE temp INT;
    DECLARE counter INT DEFAULT 1;
    DECLARE result VARCHAR(1000) DEFAULT '0,1';
    
     WHILE --循环
    WHILE counter < n - 1 DO
        SET temp = a + b;
        SET a = b;
        SET b = temp;
        SET result = CONCAT(result, ',', temp);
        SET counter = counter + 1;
    END WHILE;
    
    SELECT result AS fibonacci_sequence;
END //

DELIMITER ;

调用生成前10个斐波那契数

CALL generate_fibonacci(10);

3. REPEAT 循环

语法：

[begin_label:] REPEAT

statement_list

UNTIL search_condition

END REPEAT [end_label]

示例：密码重试机制

DELIMITER //

CREATE PROCEDURE login_simulation()
BEGIN
    DECLARE attempt_count INT DEFAULT 0;
    DECLARE max_attempts INT DEFAULT 3;
    DECLARE is_success BOOLEAN DEFAULT FALSE;
    DECLARE input_password VARCHAR(20);
    
     --模拟密码验证
    REPEAT
        SET attempt_count = attempt_count + 1;
        SET input_password = CONCAT('pass', attempt_count);  模拟输入
        
         --这里应该是实际的密码验证逻辑
        IF input_password = 'pass3' THEN
            SET is_success = TRUE;
        END IF;
        
        SELECT CONCAT('第', attempt_count, '次尝试: ', 
                     IF(is_success, '登录成功', '密码错误')) AS login_status;
        
        UNTIL is_success OR attempt_count >= max_attempts
    END REPEAT;
    
    IF NOT is_success THEN
        SELECT '账户已锁定，请联系管理员' AS final_result;
    END IF;
END //

DELIMITER ;

4. ITERATE 语句（继续下一次循环）

示例：跳过偶数的累加

DELIMITER //

CREATE PROCEDURE sum_odd_numbers()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    
    number_loop: LOOP
        SET i = i + 1;
        
         --如果是偶数，跳过本次循环
        IF i % 2 = 0 THEN
            ITERATE number_loop;
        END IF;
        
        SET total = total + i;
        
         --循环终止条件
        IF i >= 10 THEN
            LEA VE number_loop;
        END IF;
    END LOOP number_loop;
    
    SELECT total AS '1到10的奇数和'; -- 结果应为 25 (1+3+5+7+9)
END //

DELIMITER ;

```

三、流程控制综合示例

示例：学生成绩批量处理

DELIMITER //

CREATE PROCEDURE process_student_scores()

BEGIN

DECLARE v_id INT;

DECLARE v_name VARCHAR(50);

DECLARE v_score DECIMAL(5,2);

DECLARE done INT DEFAULT 0;

声明游标

DECLARE student_cursor CURSOR FOR

SELECT id, name, score FROM tb_student;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

创建临时结果表

若不存在临时表temp_results，则创建它：CREATE TEMPORARY TABLE IF NOT EXISTS temp_results (

student_id INT,

student_name VARCHAR(50),

original_score DECIMAL(5,2),

adjusted_score DECIMAL(5,2),

grade VARCHAR(10)

);

OPEN student_cursor;

process_loop: LOOP

FETCH student_cursor INTO v_id, v_name, v_score;

IF done THEN

LEA VE process_loop;

END IF;

条件判断：分数调整

IF v_score < 60 THEN

SET v_score = v_score + 5; 给不及格学生加5分

ELSEIF v_score > 95 THEN

SET v_score = 100; 最高分不超过100

END IF;

CASE 语句：评定等级

CASE

WHEN v_score >= 90 THEN SET @grade = 'A';

WHEN v_score >= 80 THEN SET @grade = 'B';

WHEN v_score >= 70 THEN SET @grade = 'C';

WHEN v_score >= 60 THEN SET @grade = 'D';

ELSE SET @grade = 'F';

END CASE;

插入结果

往临时结果表temp_results中插入值，分别是v_id、v_name、v_score以及变量@grade。

END LOOP process_loop;

CLOSE student_cursor;

输出最终结果

SELECT * FROM temp_results;

DROP TEMPORARY TABLE temp_results;

END //

DELIMITER ;

四、最佳实践和建议

1. 选择合适的循环：

已知循环次数：`WHILE` 或 `LOOP` + 计数器

未知循环次数，但有结束条件：`REPEAT`

需要灵活控制：`LOOP` + `LEA VE`/`ITERATE`

2. 避免无限循环：确保循环有明确的退出条件

3. 性能考虑：尽量减少循环内的数据库操作，优先使用集合操作

4. 标签使用：对于复杂嵌套循环，使用标签提高可读性

5. 错误处理：在循环中加入适当的异常处理

6. 游标结合：流程控制经常与游标结合使用处理逐行数据