一、条件判断语句
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 + LEAVE 循环
语法:
[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; --使用LEAVE跳出循环 IF i > 100 THEN LEAVE 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 LEAVE 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;
创建临时结果表
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
LEAVE 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;
插入结果
INSERT INTO temp_results VALUES(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` + `LEAVE`/`ITERATE`
2. 避免无限循环:确保循环有明确的退出条件
3. 性能考虑:尽量减少循环内的数据库操作,优先使用集合操作
4. 标签使用:对于复杂嵌套循环,使用标签提高可读性
5. 错误处理:在循环中加入适当的异常处理
6. 游标结合:流程控制经常与游标结合使用处理逐行数据
C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:
一点编程也不会写的:零基础C语言学练课程
解决困扰你多年的C语言疑难杂症特性的C语言进阶课程
从零到写出一个爬虫的Python编程课程
只会语法写不出代码?手把手带你写100个编程真题的编程百练课程
信息学奥赛或C++选手的 必学C++课程
蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程
手把手讲解近五年真题的蓝桥杯辅导课程