mysql：流程控制

流程控制

解决复杂问题不可能通过一个 SQL 语句完成，我们需要执行多个 SQL 操作。流程控制语句的作用就是控制存储过程中 SQL 语句的执行顺序，是我们完成复杂操作必不可少的一部分。只要是执行的程序，流程就分为三大类：

顺序结构：程序从上往下依次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL 的流程控制语句主要有 3 类。注意：只能用于存储程序。

条件判断语句：IF 语句和 CASE 语句
循环语句：LOOP、WHILE 和 REPEAT 语句
跳转语句：ITERATE 和 LEAVE 语句

IF分支结构

语法

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF

举例1

DELIMITER //

CREATE PROCEDURE test_if()

BEGIN	
	#情况1：
	#声明局部变量
	declare stu_name varchar(15);
	
	if stu_name is null 
		then select 'stu_name is null';
	end if;
	
	#情况2：二选一
	declare email varchar(25) default 'aaa';
	
	if email is null
		then select 'email is null';
	else
		select 'email is not null';
	end if;
	
	#情况3：多选一
	DECLARE age INT DEFAULT 20;
	
	IF age > 40
		THEN SELECT '中老年';
	ELSEIF age > 18
		THEN SELECT '青壮年';
	ELSEIF age > 8
		THEN SELECT '青少年';
	ELSE
		SELECT '婴幼儿';
	END IF;
	

END //

DELIMITER ;

举例2

#举例2：声明存储过程“update_salary_by_eid1”，定义IN参数emp_id，
#输入员工编号。判断该员工薪资如果低于8000元并且入职时间超过5年，
#就涨薪500元；否则就不变。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid1(IN emp_id INT)
BEGIN
	DECLARE salary_emp DOUBLE;
	DECLARE hire_year DOUBLE;
	
	SELECT salary INTO salary_emp 
		FROM employees WHERE employee_id=emp_id;
	SELECT DATEDIFF(CURDATE(),hire_date)/365 INTO hire_year 
		FROM employees WHERE employee_id=emp_id;
	IF salary_emp<8000 AND hire_year>5
		THEN 
			UPDATE employees SET salary =salary +500
			WHERE employee_id=emp_id;
			END IF;
END //
DELIMITER ;

SET @emp_id=111;
CALL update_salary_by_eid1(@emp_id);

举例3:

#声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。
#判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元
#且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。
DELIMITER //

CREATE PROCEDURE update_salary_by_eid3(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE bonus DECIMAL(3,2);

	SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
	SELECT commission_pct INTO bonus FROM employees WHERE employee_id = emp_id;

	IF emp_salary < 9000
		THEN UPDATE employees SET salary = 9000 WHERE employee_id = emp_id;
	ELSEIF emp_salary < 10000 AND bonus IS NULL
		THEN UPDATE employees SET commission_pct = 0.01 WHERE employee_id = emp_id;
	ELSE
		UPDATE employees SET salary = salary + 100 WHERE employee_id = emp_id;
	END IF;
END //

DELIMITER ;

CASE分支结构

语法：

#情况一：类似于switch
CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

#情况二：类似于多重if
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

举例

#声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，
#输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为
#9000元；薪资大于等于9000元且低于10000的，但是奖金比例为NULL的，
#就更新奖金比例为0.01；其他的涨薪100元。
DELIMITER //
CREATE PROCEDURE update_salary_by_eid4(IN emp_id INT)
BEGIN
	DECLARE emp_salary DOUBLE;
	DECLARE bonus DECIMAL(3,2);
	
	SELECT salary into emp_salary 
		FROM employees WHERE employee_id=emp_id;
	SELECT commission_pct INTO bonus 
		FROM employees WHERE employee_id=emp_id;
	
	CASE
	WHEN emp_salary<9000
	THEN UPDATE employees SET salary=9000 
				WHERE employee_id=emp_id;
	WHEN emp_salary>=9000 AND emp_salary<1000 AND bonus IS NULL
	THEN UPDATE employees SET commission_pct=0.01
				WHERE employee_id=emp_id;
	ELSE UPDATE employees SET salary=salary+100
				WHERE employee_id=emp_id;
	END CASE;
END //
DELIMITER ; 

CALL update_salary_by_eid4(100)

举例2

#声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。
#判断该员工的入职年限，如果是0年，薪资涨50；如果是1年，薪资涨100；如果是2年，
#薪资涨200；如果是3年，薪资涨300；如果是4年，薪资涨400；其他的涨薪500。
DELIMITER //

CREATE PROCEDURE update_salary_by_eid5(IN emp_id INT)
BEGIN
	DECLARE emp_sal DOUBLE;
	DECLARE hire_year DOUBLE;

	SELECT salary INTO emp_sal FROM employees WHERE employee_id = emp_id;
	
	SELECT ROUND(DATEDIFF(CURDATE(),hire_date)/365) INTO hire_year FROM employees WHERE employee_id = emp_id;

	CASE hire_year
		WHEN 0 THEN UPDATE employees SET salary=salary+50 WHERE employee_id = emp_id;
		WHEN 1 THEN UPDATE employees SET salary=salary+100 WHERE employee_id = emp_id;
		WHEN 2 THEN UPDATE employees SET salary=salary+200 WHERE employee_id = emp_id;
		WHEN 3 THEN UPDATE employees SET salary=salary+300 WHERE employee_id = emp_id;
		WHEN 4 THEN UPDATE employees SET salary=salary+400 WHERE employee_id = emp_id;
		ELSE UPDATE employees SET salary=salary+500 WHERE employee_id = emp_id;
	END CASE;
END //

DELIMITER ;

LOOP循环结构

语法

[loop_label:] LOOP
	循环执行的语句
END LOOP [loop_label]

其中，loop_label表示LOOP语句的标注名称，该参数可以省略。

举例

DECLARE id INT DEFAULT 0;
add_loop:LOOP
	SET id = id +1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;

END LOOP add_loop;

举例

#当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
#声明存储过程“update_salary_loop()”，声明OUT参数num，输出循环次数。
#存储过程中实现循环给大家涨薪，薪资涨为原来的1.1倍。直到全公司的平
#均薪资达到12000结束。并统计循环次数。

DELIMITER //
CREATE PROCEDURE update_salary_loop(OUT num INT)
BEGIN
	DECLARE loop_count INT DEFAULT 0;
	DECLARE avg_salary INT;
	loop_update:LOOP
		SELECT AVG(salary) INTO avg_salary
			FROM employees;
		IF avg_salary<=12000
			THEN UPDATE employees SET salary = salary *1.1;
		ELSE  LEAVE loop_update;
		END IF;
		SET loop_count=loop_count+1;
	END LOOP loop_update;
	SET num=loop_count;
END //
DELIMITER ;

CALL update_salary_loop(@num);
SELECT @num;

WHILE循环结构

语法

[while_label:] WHILE 循环条件  DO
	循环体
END WHILE [while_label];
#举例
DELIMITER //
CREATE PROCEDURE test_while()
BEGIN	
	#初始化条件
	DECLARE num INT DEFAULT 1;
	#循环条件
	WHILE num <= 10 DO
		#循环体（略）
		
		#迭代条件
		SET num = num + 1;
	END WHILE;
	
	#查询
	SELECT num;

END //

DELIMITER ;

举例

#市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
#声明存储过程“update_salary_while()”，声明OUT参数num，输出循环次数。
#存储过程中实现循环给大家降薪，薪资降为原来的90%。
#直到全公司的平均薪资达到5000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_while(OUT num INT)
BEGIN
	DECLARE avg_salary DOUBLE;
	DECLARE while_count INT DEFAULT 0;
	SELECT AVG(salary) INTO avg_salary FROM employees;
	
	WHILE avg_salary>=5000 DO
		UPDATE employees SET salary = salary *0.9;
		SET while_count=while_count+1;
		SELECT AVG(salary) INTO avg_salary FROM employees;
	END WHILE;
	SET num =while_count;
END //
DELIMITER ;

REPEAT 循环结构

repeat循环至少会循环一次。与do-while类似

语法

[repeat_label:] REPEAT
　　　　循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

#举例1：

DELIMITER //

CREATE PROCEDURE test_repeat()
BEGIN
	#声明变量
	DECLARE num INT DEFAULT 1;
	
	REPEAT
		SET num = num + 1;
		UNTIL num >= 10
	END REPEAT;
	
	#查看
	SELECT num;

END //


DELIMITER ;

举例

#当市场环境变好时，公司为了奖励大家，决定给大家涨工资。
#声明存储过程“update_salary_repeat()”，声明OUT参数num，
#输出循环次数。存储过程中实现循环给大家涨薪，薪资涨为
#原来的1.15倍。直到全公司的平均薪资达到13000结束。
#并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_repeat(OUT num INT)
BEGIN
	DECLARE avg_salary DOUBLE;
	DECLARE while_count INT DEFAULT 0;
	SELECT AVG(salary) INTO avg_salary FROM employees;
	REPEAT
		UPDATE employees SET salary = salary *1.15;
		SELECT AVG(salary) INTO avg_salary FROM employees;
		SET while_count =while_count +1;
		UNTIL avg_salary>=13000 
	END REPEAT;
	SET num = while_count;
END //
DELIMITER ;

对比三种循环结构

这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。
LOOP：一般用于实现简单的"死"循环
WHILE：先判断后执行
REPEAT：先执行后判断，无条件至少执行一次

posted @ 2022-03-18 23:01 Boerk 阅读(110) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· mysql:视图、存储过程、存储函数

· mysql：流程控制与mysql8新特性

· mysql流程控制：IF、CASE、LOOP、WHILE、REPEAT、LEAVE、ITERATE

· 流程控制-分支结构

· Mysql数据库基础第七章：流程控制结构

阅读排行：
· 地球OL攻略 —— 某应届生求职总结
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 提示词工程——AI应用必不可少的技术
· .NET周刊【3月第1期 2025-03-02】

公告

昵称： Boerk
园龄： 3年3个月
粉丝： 2
关注： 0

+加关注

2025年3月

日

一

二

三

四

五

六

Boerk