【第二十一部分】流程控制
文章目录
21. 流程控制
解决复杂问题不可能通过一个 SQL 语句完成,我们需要执行多个 SQL 操作。
学过其他编程语言的同学来说都会知道,流程分为三大类,MySQL也是如此: 顺序结构、分支结构、循环结构
顺序结构
:程序从上往下依次执行分支结构
:程序按条件进行选择执行,从两条或多条路径中选择一条执行循环结构
:程序满足一定条件下,重复执行一组语句
21.1 分支结构
21.1.1 IF
语法结构:
IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]……
[ELSE 操作N]
END IF
简单案例:
# 写一个人群年龄分类的存储过程
DELIMITER //
CREATE PROCEDURE identify_age(IN age INT)
BEGIN
IF age < 8 THEN SELECT "小孩";
ELSEIF age < 18 THEN SELECT "青少年";
ELSEIF age < 30 THEN SELECT "青壮年";
ELSEIF age < 50 THEN SELECT "壮年";
ELSE SELECT "老年";
END IF;
END //
DELIMITER ;
CALL identify_age(20);
举例演示:
#声明存储过程“update_salary”,定义IN参数emp_id,输入员工编号,判断该员工薪资如果低于9000元,就更新薪资为9000元;薪资如果大于等于9000元且低于10000的,但是奖金比例为NULL的,就更新奖金比例为0.01;其他的涨薪100元
DELIMITER //
CREATE PROCEDURE update_salary(IN emp_id INT)
BEGIN
DECLARE emp_sal DOUBLE DEFAULT 0.0;
DECLARE com_pct DOUBLE;
SELECT salary,commission_pct INTO emp_sal,com_pct
FROM tab_emp
WHERE employee_id = emp_id;
IF emp_sal < 9000 THEN UPDATE tab_emp SET salary = 9000 WHERE employee_id = emp_id;
ELSEIF emp_sal < 10000 AND commission_pct IS NULL THEN UPDATE tab_emp SET commisstion_pct = 0.01 WHERE employee_id = emp_id;
ELSE UPDATE tab_emp SET salary = salary + 100 WHERE employee_id = emp_id;
END IF;
END //
DELIMITER ;
CALL update_salary(106)
21.1.2 CASE
CASE语句结构一:
#情况一:类似于switch
CASE 填写某个判断的字段
WHEN 匹配值1 THEN 结果1或语句1(如果是语句,需要加分号)
WHEN 匹配值2 THEN 结果2或语句2(如果是语句,需要加分号)
...
ELSE 结果n或语句n(如果是语句,需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)
DELIMITER //
CREATE PROCEDURE classify_num(IN num INT)
BEGIN
CASE num
WHEN 1 THEN SELECT 'this is 1';
WHEN 2 THEN SELECT 'this is 2';
ELSE SELECT 'this is unknown';
END CASE;
END //
DELIMITER ;
CALL classify_num(2)
CASE语句结构二:
#情况二:类似于多重if
CASE
WHEN 判断条件1 THEN 结果1或语句1(如果是语句,需要加分号)
WHEN 判断条件2 THEN 结果2或语句2(如果是语句,需要加分号)
...
ELSE 结果n或语句n(如果是语句,需要加分号)
END [case](如果是放在begin end中需要加上case,如果放在select后面不需要)
DELIMITER //
CREATE PROCEDURE classify_age(IN age INT)
BEGIN
CASE
WHEN age < 8 THEN SELECT "小孩";
WHEN age < 18 THEN SELECT "青少年";
WHEN age < 30 THEN SELECT "青壮年";
WHEN age < 50 THEN SELECT "壮年";
ELSE SELECT "老年";
END CASE;
END //
DELIMITER ;
CALL classify_age(20)
举例说明:
# 声明存储过程“update_salary”,定义IN参数emp_id,输入员工编号,判断该员工薪资如果低于9000元,就更新薪资为9000元;薪资如果大于等于9000元且低于10000的,但是奖金比例为NULL的,就更新奖金比例为0.01;其他的涨薪100元
DELIMITER //
CREATE PROCEDURE update_salary(IN emp_id INT)
BEGIN
DECLARE emp_salary DOUBLE DEFAULT 0.0;
DECLARE com_pct DOUBLE;
SELECT salary,commission_pct INTO emp_salary,com_pct
FROM tab_emp
WHERE employee_id = emp_id;
CASE
WHEN emp_salary < 9000 THEN UPDATE tab_emp SET salary = 9000 WHERE employee_id = emp_id;
WHEN emp_salary < 10000 AND com_pct IS NULL THEN UPDATE tab_emp SET commission_pct = 0.01 WHERE employee_id = emp_id;
ELSE UPDATE tab_emp SET salary = salary + 100 WHERE employee_id = emp_id;
END CASE;
END //
DELIMITER;
CALL update_salary(109)
21.2 跳转语句
21.2.1 LEAVE
LEAVE语句:可以用在循环语句内或者以 BEGIN 和 END 包裹起来的程序体内,表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验,你可以把 LEAVE 理解为 break。
使用时需要在循环语句或者是BEGIN前面加上标签,该标签自定义名称,想要跳出循环的时候执行 LEAVE 标签名
DELIMITER //
CREATE PROCEDURE leave_begin(IN num INT)
# 添加标签
begin_label: BEGIN
IF num<=0
THEN LEAVE begin_label; # 跳出程序体
ELSEIF num=1
THEN SELECT 'this is 1';
ELSEIF num=2
THEN SELECT 'this is 2';
ELSE
SELECT 'this is unknown';
END IF;
END //
DELIMITER ;
# -----------------------------------------------------
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
THEN LEAVE my_loop;
END IF;
END LOOP my_loop;
END //
DELIMITER ;
21.2.2 ITERATE
ITERATE语句:只能用在循环语句(LOOP、REPEAT和WHILE语句)内,表示跳过本次循环。如果你有面向过程的编程语言的使用经验,你可以把 ITERATE 理解为 continue,意思为“跳过本次循环”。
使用时需要在循环语句前面加上标签,该标签自定义名称,想要跳出本次循环的时候执行 ITERATE 标签名
DELIMITER //
CREATE PROCEDURE test_iterate()
BEGIN
DECLARE num INT DEFAULT 0;
my_loop:LOOP
SET num = num + 1;
IF num < 10
THEN ITERATE my_loop;
ELSEIF num > 15
THEN LEAVE my_loop;
END IF;
END LOOP;
END //
DELIMITER ;
21.3 循环结构
21.3.1 LOOP
语法结构:
[loop_label:] LOOP
循环执行的语句
END LOOP [loop_label];
# loop_label表示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 DOUBLE;
SELECT AVG(salary) INTO avg_salary
FROM tab_emp;
loop_label:loop
IF avg_salary >= 12000 THEN LEAVE loop_label;
ELSE UPDATE tab_emp SET salary = salary * 1.1;
END IF;
SELECT AVG(salary) INTO avg_salary
FROM tab_emp;
SET loop_count = loop_count + 1 ;
END loop;
SET num = loop_count;
END //
DELIMITER ;
CALL update_salary_loop(@num);
SELECT @num;
21.3.2 WHILE
语法结构:
[while_label:] WHILE 循环条件 DO
循环体
END WHILE [while_label];
# while_label表示WHILE语句的标注名称,该参数可以省略。
# 当市场环境变好时,公司为了奖励大家,决定给大家涨工资。声明存储过程“update_salary_WHILE()”,声明OUT参数num,输出循环次数。存储过程中实现循环给大家涨薪,薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_WHILE(OUT num INT)
BEGIN
DECLARE while_count INT DEFAULT 0;
DECLARE avg_salary DOUBLE;
SELECT AVG(salary) INTO avg_salary
FROM tab_emp;
WHILE avg_salary < 12000 DO
UPDATE tab_emp SET salary = salary * 1.1;
SELECT AVG(salary) INTO avg_salary
FROM tab_emp;
SET while_count = while_count +1;
END WHILE;
SET num = while_count;
END //
DELIMITER ;
CALL update_salary_WHILE(@num);
SELECT @num;
21.3.3 REPEAT
语法结构:
[repeat_label:] REPEAT
循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label];
# repeat_label表示REPEAT语句的标注名称,该参数可以省略。
# 当市场环境变好时,公司为了奖励大家,决定给大家涨工资。声明存储过程“update_salary_REPEAT()”,声明OUT参数num,输出循环次数。存储过程中实现循环给大家涨薪,薪资涨为原来的1.1倍。直到全公司的平均薪资达到12000结束。并统计循环次数。
DELIMITER //
CREATE PROCEDURE update_salary_REPEAT(OUT num INT)
BEGIN
DECLARE repeat_count INT DEFAULT 0;
DECLARE avg_salary DOUBLE;
REPEAT
UPDATE tab_emp SET salary = salary * 1.1;
SELECT AVG(salary) INTO avg_salary
FROM tab_emp;
SET repeat_count = repeat_count + 1;
UNTIL avg_salary > 12000 # 在这里需要注意UNTIL后面不能加分号
END REPEAT;
SET num = repeat_count;
END //
DELIMITER;
CALL update_salary_REPEAT(@num);
SELECT @num;
总结
以上就是今天要讲的内容,希望对大家有所帮助!!!