其实前面演示存储过程和函数都是一种顺序结构,但是和JAVA 中的编程有分支和循环,所以这里就聊一下MYSQL的流程控制。
- 顺序结构:程序由上而下执行
- 分支结构:程序按条件进行选择执行,通过条件在两条以及多条路径中选择一条执行。
- 循环结构:程序满足条件下,重复执行某些SQL语句。
这个是三种执行结构而流程控制有3类(注意:只能用于存储程序):
- 条件判断语句:IF和CASE
- 循环语句:LOOP,WHERE和REPEAT
- 跳转语句:ITERATE和LEAVE
分支
分支结构-IF
具体格式:
IF 条件1 THEN 执行1
[ELSEIF 条件2 THEN 执行2 ]
[ELSE 执行N]
END IF
# 如果条件多个 可以使用 AND OR
这个和其它语言的意思差不多,即使如判断条件满足就执行后面的语句,当然’[]'中都是内容可选的。还是老规矩通过演示理解。
案例1
DELIMITER $
CREATE PROCEDURE test_if1()
BEGIN
DECLARE test_name INT;
IF test_name IS NULL
THEN SELECT 'test_name is null';
ELSE SELECT 'test_name is not null';
END IF;
END $
DELIMITER ;
# 其实这个时候声明的时候没默认值自然是NULL
CALL test_if1();
案例2
DELIMITER $
CREATE PROCEDURE test_if2()
BEGIN
DECLARE num INT DEFAULT 20;
IF num>0
THEN SELECT '这个只是大于0小于20的整数';
ELSEIF num>19
THEN SELECT '这个只是大于19小于100的整数';
ELSEIF num>100
THEN SELECT '这个只是大于99的整数';
END IF;
END $
DELIMITER ;
# 调用这个存储过程
call test_if2();
案例3
# 还老规矩用之前的员工表进行操作。
# 写一个存储过程给输入一个员工号如果满足入职时长超过3年以及员工工资小于5000的工资就增加200
DELIMITER $
CREATE PROCEDURE test_if3(IN emp_num INT)
BEGIN
DECLARE hire_years DOUBLE;
DECLARE emp_sal DOUBLE;
SELECT sal INTO emp_sal FROM emp WHERE empno=emp_num;
SELECT DATEDIFF(CURDATE(),hiredate)/365 INTO hire_years FROM emp WHERE empno=emp_num;
IF emp_sal<5000 AND hire_years>3
THEN UPDATE emp SET sal=sal+200 WHERE empno=emp_num;
END IF;
END $
DELIMITER ;
先查看某个员工更新的数据;
SELECT * FROM emp WHERE empno='7369';
现在调用存储函数:
CALL test_if3(7369);
SELECT * FROM emp WHERE empno='7369';
分支机构-CASE
CASE的格式有两种,
格式如下:
# 方式1 这个类似于 Java 中的SWITCH
CASE 要判断的条件
WHEN 值1 THEN 执行1;
WHEN 值2 THEN 执行2;
.....
ELSE 执行N;
END {CASE} # 这个需要解释,如果放在BEGIN END 中需要添加上CASE ,如果放在SELECT 后面不用
# 方式2 这个类似于 Java 中的 IF
CASE
WHEN 条件1 THEN 执行1;
WHEN 条件2 THEN 执行2;
.....
ELSE 执行N;
END {CASE} # 这个需要解释,如果放在BEGIN END 中需要添加上CASE ,如果放在SELECT 后面不用
# 如果条件多个 可以使用 AND OR
案例1
# 类似有switch格式
DELIMITER $
CREATE PROCEDURE test_case1()
BEGIN
DECLARE var INT DEFAULT 3;
CASE var
WHEN 1 THEN SELECT 'var=1';
WHEN 2 THEN SELECT 'var=2';
WHEN 3 THEN SELECT 'var=3';
ELSE SELECT 'var=你猜';
END CASE;
END $
DELIMITER ;
# 调用
CALL test_case1();
案例2
# 类似于IF的格式
DELIMITER $
CREATE PROCEDURE test_case2()
BEGIN
DECLARE var INT DEFAULT 3;
CASE
WHEN var=1 THEN SELECT 'var=1';
WHEN var=2 THEN SELECT 'var=2';
WHEN var=3 THEN SELECT 'var=3';
ELSE SELECT 'var=你猜';
END CASE;
END $
DELIMITER ;
# 调用
CALL test_case2();
案例3
# 还老规矩用之前的员工表进行操作。
# 写一个存储过程给输入一个员工号如果员工工资小于等于5000就添加200如果大于5000 就添加100
DELIMITER $
CREATE PROCEDURE test_case3(IN emp_num INT)
BEGIN
DECLARE emp_sal DOUBLE;
SELECT sal INTO emp_sal FROM emp WHERE empno=emp_num;
CASE
WHEN emp_sal <=5000
THEN UPDATE emp SET sal=sal+200 WHERE empno=emp_num;
WHEN emp_sal>5000
THEN UPDATE emp SET sal=sal+100 WHERE empno=emp_num;
END CASE;
END $
DELIMITER ;
循环
循环结构-LOOP
LOOP循环语句用来重复执行某些语句,LOOP内的语句一直重复,但是其自然也有循环跳出的关键字(LEAVE),通过判断语句然后执行跳出。
格式如下:
loop_label: LOOP
循环执行的语句
END LOOP loop_label
#loop_label 这个标签是自定义名字,只要满足命名条件可以随便写
还是老规矩看代码
案例1
DELIMITER $
CREATE PROCEDURE loop_test()
BEGIN
DECLARE var INT DEFAULT 0;
l_lable:LOOP
SET var=var+1;
IF var>10 THEN LEAVE l_lable;
END IF;
END LOOP l_lable;
SELECT var;
END $
DELIMITER ;
#调用
CALL loop_test();
案例2
# 要求 每次每人涨薪10%,涨薪几年全体员工的平均工资就超过了3000
# 首先将数据复制到一个新表中
CREATE TABLE emp1 AS SELECT * FROM emp;
# 先看一下其平均工资
SELECT AVG(sal) FROM emp1
DELIMITER $
CREATE PROCEDURE loop_test1(OUT num_years INT )
BEGIN
DECLARE sal_avg DOUBLE;
SET num_years =0;
l_lable:LOOP
SELECT AVG(sal) INTO sal_avg FROM emp1; #这个需要放在循环体之内,不然每次更新收入后不会引起变化,从而变成死循环
IF sal_avg>3000
THEN LEAVE l_lable;
END IF;
SET num_years=num_years+1;
UPDATE emp1 SET sal=sal*1.1;
END LOOP l_lable;
END $
DELIMITER ;
## 调用
SET @num_years=0;
CALL loop_test1(@num_years);
SELECT @num_years;
循环结构-WHILE
WHILE语句创建一个带有条件判断的循环过程。WHILE在执行语句时,先对指定的表达式进行判断,如果真就执行循环语句,否则退出循环。格式如下:
[w_lable:]WHILE 循环条件 DO
循环体
END WHILE [w_lable]
#w_lable 这个标签是自定义名字,只要满足命名条件可以随便写,当然也可以省略,如果有多个套用的话最好带上,只是更命名结束那个循环
案例1
DELIMITER $
CREATE PROCEDURE while_test()
BEGIN
DECLARE var INT DEFAULT 0;
w_lable: WHILE var<10 DO
SET var=var+1;
END WHILE w_lable;
SELECT var;
END $
DELIMITER ;
#调用
CALL while_test();
案例2
# 要求 每次每人涨薪10%,涨薪几年全体员工的平均工资就超过了3000
# 首先将数据复制到一个新表中
CREATE TABLE emp2 AS SELECT * FROM emp;
# 先看一下其平均工资
SELECT AVG(sal) FROM emp2;
DELIMITER $
CREATE PROCEDURE while_test2(OUT num_years INT )
BEGIN
DECLARE sal_avg DOUBLE;
SET num_years =0;
SELECT AVG(sal) INTO sal_avg FROM emp2;
WHILE sal_avg<3000 DO
SET num_years=num_years+1;
UPDATE emp2 SET sal=sal*1.1;
SELECT AVG(sal) INTO sal_avg FROM emp2;
END WHILE;
SELECT num_years;
END $
DELIMITER ;
## 调用
SET @num_years=0;
CALL while_test2(@num_years);
SELECT @num_years;
循环结构-- REPEAT
REPEAT语句创建一个带有判断的循环过程,于WHILE不同是,REPEAT循环首先会执行一次循环,然后再UNTIL中下进行表达式判断,如果满足条件就退出,如果不满足就继续执行。格式如下:
[r_lable:]REPEAT
循环体
UNTIL 结束条件
END REPEAT [r_lable]
#r_lable 这个标签是自定义名字,只要满足命名条件可以随便写
案例1
DELIMITER $
CREATE PROCEDURE repeat_test()
BEGIN
DECLARE var INT DEFAULT 0;
r_lable:REPEAT
SET var=var+1;
UNTIL var>10 # 这个地方不要加; 不然会报错
END REPEAT r_lable;
SELECT var;
END $
DELIMITER ;
# 调用
CALL repeat_test();
案例2
# 要求 每次每人涨薪10%,涨薪几年全体员工的平均工资就超过了3000
# 首先将数据复制到一个新表中
CREATE TABLE emp3 AS SELECT * FROM emp;
# 先看一下其平均工资
SELECT AVG(sal) FROM emp3;
DELIMITER $
CREATE PROCEDURE repeat_test1(OUT num_years INT)
BEGIN
DECLARE sal_avg DOUBLE;
SET num_years=0;
REPEAT
SELECT AVG(sal) INTO sal_avg FROM emp3;
SET num_years=num_years+1;
UPDATE emp3 SET sal=sal*1.1;
UNTIL sal_avg>3000
END REPEAT;
END $
DELIMITER ;
# 调用
SET @num_years=0;
CALL repeat_test1(@num_years);
SELECT @num_years;
满足条件后如果再调用:
SET @num_years=0;
CALL repeat_test1(@num_years);
SELECT @num_years;
**可以REPEAT看出多执行了一次,可以看出先执行循环体,然后再判断条件。**所以执行更新数据等操作的时候,最好不要用REPEAT,因为的特性决定了,哪怕满足条件了还是要执行一次的。
补充:
1:可以看出循环可以省略名称,但是如果循环中添加了循环控制语句(LEAVE或者ITERATE)则必须添加定义的名字。
3:三个的使用场景
- LOOP: 一般用于实现简单死循环
- WHILE:先判断后执行
- REPEAT:先执行后判断,无论是否满足条件都会执行一次
控制语句-跳出语句
跳出语句-LEAVE
LEAVE语句:可以用在循环语句内,或者以BEGIN和END包裹起来的程序体中,表示跳出循环或者跳出程序题的操作。可以理解为JAVA中的BREAK;
格式如下:
LEAVE 自定义标签
注意: LEAVE 后面一定要有一个标签
案例1
#在循环中用方式,其实在LOOP中以及演示了现在体验一个在BEGIN中跳出的案例
DELIMITER $
CREATE PROCEDURE leave_test(INOUT a INT,INOUT b INT)
b_lable:BEGIN
IF a=1
THEN
SET a=3;
SET b=3;
LEAVE b_lable;
END IF;
SET a=10;
SET b=11;
END $
DELIMITER ;
#调用 因为估计用满足条件的赋值
SET @a=1;
SET @b=1;
CALL leave_test(@a,@b);
SELECT @a,@b;
当然也可以用在WHILE当中,这个就不在具体演示,至少举出例子
w_label:WHILE TRUE DO
循环体
IF 条件
THEN LEAVE w_label;
END WHILEw_label
跳出语句-ITERATE
ITERATE语句:只能用在循环语句(LOOP,WHILE和REPEAT)内,表示重新开始循环,执行瞬间转到语句段的开头,这个有点像是JAVA中的continue,意思是跳过这个此循环,继续执行这个循环体。
格式如下:
ITERATE 自定义标签
案例1
DELIMITER $
CREATE PROCEDURE iterate_test(OUT t_flag VARCHAR(100))
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE b VARCHAR(100) ;
SET t_flag='';
l_lable:LOOP
SET a=a+1;
IF a<10 THEN ITERATE l_lable;
ELSEIF a>15 THEN LEAVE l_lable;
END IF;
SELECT CONCAT(t_flag,' ',a) INTO b;
SET t_flag=b;
END LOOP l_lable;
END $
DELIMITER ;
# 调用
SET @t_flag='';
CALL iterate_test(@t_flag);
SELECT @t_flag;