MySQL中流程控制和游标的示例分析

61次阅读

没有评论

共计 14744 个字符，预计需要花费 37 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 MySQL 中流程控制和游标的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

学习或者了解过编程语言的都知道，任何一门编程语言都不可能只通过一两句语句代码完成。

流程控制语句的作用就是在程序运行过程中控制语句的执行顺序，是我们完成复杂操作必不可少的一部分。

只要是执行的程序，流程就分为三大类：

顺序结构：程序从上往下依次执行

分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行

循环结构：程序满足一定条件下，重复执行一组语句

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

条件判断语句：IF 语句和 CASE 语句

循环语句：LOOP、WHILE 和 REPEAT 语句

跳转语句：ITERATE 和 LEAVE 语句

准备工作：

创建数据库以及两张表，并插入数据：

create database dbtest16;
use dbtest16;
create table employees 
as 
select * from atguigudb.employees;
create table departments
select * from atguigudb.departments;
select * from employees;
select * from departments;

分支结构

分支，即二选一。在 SQL 中分支结构主要有两种展现形式：

IF

CASE

IF 语句

IF 语句的语法结构是：

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

根据表达式的结果为 TRUE 或 FALSE 执行相应的语句。这里“[]”中的内容是可选的。

特点：① 不同的表达式对应不同的操作 ② 使用在 begin end 中

举例一：单判断

delimiter //
create procedure test_if()
begin
 #  声明局部变量
 declare stu_name varchar(15);
 if stu_name is null
 then select  stu_name is null 
 end if;
end//
delimiter ;
call test_if();

举例二：二选一

delimiter //
create procedure test_if2()
begin
 declare email varchar(25);
 if email is null
 then select  email is null 
 else
 select  email is not null 
 end if;
end//
delimiter ;
call test_if2();

举例三：多选一

delimiter //
create procedure test_if3()
begin
 declare age int default 20;
 if age   40
 then select  中老年 
 elseif age   18
 then select  青壮年 
 elseif age   10
 then select  青少年 
 else 
 select  孩童 
 end if;
end//
delimiter ;
call test_if3();

场景举例一：声明存储过程“update_salary_by_eid1”，定义 IN 参数 emp_id，输入员工编号。判断该员工薪资如果低于 8000 元并且入职时间超过 5 年，就涨薪 500 元；否则就不变。

delimiter //
create procedure update_salary_by_eid1(in emp_id int)
begin
 #  声明变量  
 declare sal double; #  记录员工工资
 declare hiredate date; #  记录入职日期
 #  查询赋值
 select salary,hire_date into sal,hiredate from employees 
 where employee_id = emp_id;
 #  判断条件并修改
 if sal   8000 and datediff(now(),hiredate)/365   5 
 then update employees set salary = salary + 500 where employee_id = emp_id;
 end if;
end//
delimiter;
set @emp_id = 104;
#  更新前 104 号员工工资情况：select salary,employee_id from employees where employee_id = @emp_id;
#  更新工资：call update_salary_by_eid1(@emp_id);
#  再次查询 104 号员工工资情况：select salary,employee_id from employees where employee_id = @emp_id;

场景举例二：声明存储过程“update_salary_by_eid2”，定义 IN 参数 emp_id，输入员工编号。判断该员工薪资如果低于 9000 元并且入职时间超过 5 年，就涨薪 500 元；否则就涨薪 100 元。

delimiter //
create procedure update_salary_by_eid2(in emp_id int)
begin
 #  声明变量  
 declare sal double; #  记录员工工资
 declare hiredate date; #  记录入职日期
 #  查询赋值
 select salary,hire_date into sal,hiredate from employees 
 where employee_id = emp_id;
 #  判断条件并修改
 if sal   9000 and datediff(now(),hiredate)/365   5 
 then update employees set salary = salary + 500 where employee_id = emp_id;
 else 
 update employees set salary = salary + 100 where employee_id = emp_id;
 end if;
end//
delimiter;
#  以 103,104 员工为例
#  更新前员工工资情况：select salary,employee_id from employees where employee_id in (103,104);
#  更新工资：call update_salary_by_eid2(103);
call update_salary_by_eid2(104);
#  再次查询员工工资情况：select salary,employee_id from employees where employee_id in (103,104);

场景举例三: 声明存储过程“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 sal double; #  记录员工工资
 declare emp_commission_pct double; #  记录奖金比例
 #  查询赋值
 select salary,commission_pct into sal,emp_commission_pct from employees 
 where employee_id = emp_id;
 #  判断条件并修改
 if sal   9000
 then update employees set salary = 9000 where employee_id = emp_id;
 elseif sal   10000 and emp_commission_pct 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;
#  以 102,103,104 员工为例
#  更新前员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (102,103,104);
#  更新工资：call update_salary_by_eid3(102);
call update_salary_by_eid3(103);
call update_salary_by_eid3(104);
#  再次查询员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (102,103,104);

CASE 语句

CASE 语句的语法结构 1：

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

CASE 语句的语法结构 2：

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

举例一：case … when … then …

delimiter //
create procedure test_case()
begin
 declare var int default 2;
 case var
  when 1 then select  var = 1 
 when 2 then select  var = 2 
 when 3 then select  var = 3 
 else select  other 
 end case;
end //
delimiter ;
call test_case();

举例二：case when … then …

delimiter //
create procedure test_case2()
begin
 declare var1 int default 10;
 case when var1  = 100 then select  三位数 
 when var1  =10 then select  两位数 
 else select  个位数 
 end case;
end //
delimiter ;
call test_case2();

场景举例一：– 声明存储过程“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 sal double; #  记录员工工资
 declare emp_commission_pct double; #  记录奖金比例
 #  查询赋值
 select salary,commission_pct into sal,emp_commission_pct from employees 
 where employee_id = emp_id;
 #  判断条件并修改
 case
 when sal   9000
 then update employees set salary = 9000 where employee_id = emp_id;
 when sal   10000 and emp_commission_pct 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;
#  以 103,104,105 员工为例
#  更新前员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (103,104,105);
#  更新工资：call update_salary_by_eid3(103);
call update_salary_by_eid3(104);
call update_salary_by_eid3(105);
#  再次查询员工工资情况：select salary,employee_id,commission_pct from employees where employee_id in (103,104,105);

场景举例二：– 声明存储过程 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 sal double; #  记录员工工资
 declare hire_year double; #  记录入职日期
 #  查询赋值
 select salary,round(datediff(now(),hire_date)/365) into sal,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;
#  以 107 员工为例
#  更新前员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 107;
#  更新工资：call update_salary_by_eid5(107);
#  再次查询员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 107;

#  针对场景二，此种写法略显不足，重复的书写相同的更新语句，#  其实观察下来也就金额不同，可以有改进改进如下：delimiter //
create procedure update_salary_by_eid6(in emp_id int)
begin
 #  声明变量  
 declare sal double; #  记录员工工资
 declare hire_year double; #  记录入职日期
 declare add_sal double; #  保存更新的金额
 #  查询赋值
 select salary,round(datediff(now(),hire_date)/365) into sal,hire_year from employees 
 where employee_id = emp_id;
 #  判断条件并修改
 case hire_year
 when 0 then set add_sal = 50;
 when 1 then set add_sal = 100;
 when 2 then set add_sal = 200;
 when 3 then set add_sal = 300;
 when 4 then set add_sal = 400;
 else set add_sal = 500;
 end case;
 #  根据当前 add_sal 值修改
 update employees set salary = salary + add_sal where employee_id = emp_id;
end//
delimiter;
#  以 108 员工为例
#  更新前员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 108;
#  更新工资：call update_salary_by_eid5(108);
#  再次查询员工工资情况：select salary,employee_id,hire_date from employees where employee_id = 108;

循环结构

有时候我们需要重复的执行某条语句，而借助循环结构可以很好地实现。在 MySQL 中我们可以有三种方式实现循环：

LOOP

WHILE

REPEAT

凡是循环结构都遵循的四要素：

1. 初始化条件

2. 循环条件

3. 循环体

4. 迭代条件

LOOP 语句

LOOP 循环语句用来重复执行某些语句。LOOP 内的语句一直重复执行直到循环被退出（使用 LEAVE 子句），跳出循环过程。

LOOP 语句的基本格式如下：

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

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

举例一：

delimiter //
create procedure test_loop()
begin
 #  声明变量
 declare num int default 1;
 soberw:loop
 #  重新赋值
 set num = num + 1;
 if num  = 10 
 then leave soberw;
 end if;
 end loop soberw;
 #  查看 num
 select num;
end //
delimiter ;
call test_loop();

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

delimiter //
create procedure update_salary_loop(out num int)
begin
 #  声明变量
 #  记录保存平均薪资
 declare avg_sal double default 0;
 #  记录循环次数
 declare count int default 0;
 #  获取当前平均薪资
 select avg(salary) into avg_sal from employees;
 soberw:loop
 #  结束条件
 if avg_sal  = 12000 
 then leave soberw;
 end if;
 #  更新工资
 update employees set salary = salary * 1.1;
 #  保证当前平均薪资为最新
 select avg(salary) into avg_sal from employees;
 #  记录次数
 set count = count + 1;
 end loop soberw;
 #  返回 num
 set num = count;
end //
delimiter ;
call update_salary_loop(@num);
select @num;
select avg(salary) from employees;

WHILE 语句

WHILE 语句创建一个带条件判断的循环过程。WHILE 在执行语句执行时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则退出循环。WHILE 语句的基本格式如下：

[while_label:] WHILE  循环条件  DO
END WHILE [while_label];

while_label 为 WHILE 语句的标注名称；如果循环条件结果为真，WHILE 语句内的语句或语句群被执行，直至循环条件为假，退出循环。

举例一：WHILE 语句示例，i 值小于 10 时，将重复执行循环过程

delimiter //
create procedure test_while()
begin
 #  初始化条件
 declare i int default 1;
 #循环条件
 while i   10 do
 #  循环体略
 #迭代条件
 set i = i + 1;
 end while;
 select i;
end//
delimiter ;
call test_while();

举例二：
— 市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
— 声明存储过程“update_salary_while()”，声明 OUT 参数 num，输出循环次数。
— 存储过程中实现循环给大家降薪，薪资降为原来的 90%。
— 直到全公司的平均薪资达到 5000 结束。
— 并统计循环次数。

delimiter //
create procedure update_salary_while(out num int)
begin
 #  声明变量
 #  记录保存平均薪资
 declare avg_sal double default 0;
 #  记录循环次数
 declare count int default 0;
 #  获取当前平均薪资   初始化条件
 select avg(salary) into avg_sal from employees;
 #循环条件
 soberw:while avg_sal   5000 do
 #  循环体 
 #  更新工资
 update employees set salary = salary * 0.9;
 #  记录次数
 set count = count + 1;
 #  迭代条件
 #  保证当前平均薪资为最新
 select avg(salary) into avg_sal from employees;
 end while soberw;
 #  返回 num
 set num = count;
end //
delimiter ;
call update_salary_while(@num);
select @num;
select avg(salary) from employees;

REPEAT 语句

REPEAT 语句创建一个带条件判断的循环过程。与 WHILE 循环不同的是，REPEAT 循环首先会执行一次循环，然后在 UNTIL 中进行表达式的判断，如果满足条件就退出，即 END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。

REPEAT 语句的基本格式如下：

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

repeat_label 为 REPEAT 语句的标注名称，该参数可以省略；REPEAT 语句内的语句或语句群被重复，直至 expr_condition 为真。

举例一：

DELIMITER //
CREATE PROCEDURE test_repeat()
BEGIN 
 DECLARE i INT DEFAULT 0;
 REPEAT 
 SET i = i + 1;
 UNTIL i  = 10
 END REPEAT;
 SELECT i;
END //
DELIMITER ;
call test_repeat();

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

delimiter //
create procedure update_salary_repeat(out num int)
begin
 #  声明变量
 #  记录保存平均薪资
 declare avg_sal double default 0;
 #  记录循环次数
 declare count int default 0;
 #  获取当前平均薪资   初始化条件
 select avg(salary) into avg_sal from employees;
 #循环条件
 soberw:repeat 
 #  循环体 
 #  更新工资
 update employees set salary = salary * 1.15;
 #  记录次数
 set count = count + 1;
 #  迭代条件
 #  保证当前平均薪资为最新
 select avg(salary) into avg_sal from employees;
 until avg_sal  = 13000
 end repeat soberw;
 #  返回 num
 set num = count;
end //
delimiter ;
call update_salary_repeat(@num);
select @num;
select avg(salary) from employees;

对比三种循环结构：

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

跳转语句

跳转语句可以协助我们更好的控制循环。

LEAVE 语句

LEAVE 语句：可以用在循环语句内，或者以 BEGIN 和 END 包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把 LEAVE 理解为 break。

基本格式如下：

LEAVE  标记名

其中，label 参数表示循环的标志。LEAVE 和 BEGIN … END 或循环一起被使用。

举例 1：创建存储过程“leave_begin()”，声明 INT 类型的 IN 参数 num。
给 BEGIN…END 加标记名，并在 BEGIN…END 中使用 IF 语句判断 num 参数的值。

如果 num =0，则使用 LEAVE 语句退出 BEGIN…END；

如果 num=1，则查询“employees”表的平均薪资；

如果 num=2，则查询“employees”表的最低薪资；

如果 num 2，则查询“employees”表的最高薪资。

IF 语句结束后查询“employees”表的总人数。

delimiter //
create procedure leave_begin(in num int)
soberw:begin
 if num  = 0 
 then leave soberw;
 elseif num = 1
 then select avg(salary) from employees;
 elseif num = 2
 then select min(salary) from employees;
 elseif num   2
 then select max(salary) from employees;
 end if;
 select count(1) from employees;
end//
delimiter ;
call leave_begin(2);
call leave_begin(-1);

举例 2：
— 当市场环境不好时，公司为了渡过难关，决定暂时降低大家的薪资。
— 声明存储过程“leave_while()”，声明 OUT 参数 num，输出循环次数，
— 存储过程中使用 WHILE 循环给大家降低薪资为原来薪资的 90%，
— 直到全公司的平均薪资小于等于 10000，
— 并统计循环次数。

delimiter//
create procedure leave_while(out num int)
begin
 declare avg_sal double;
 declare count int default 0;
 select avg(salary) into avg_sal from employees;
 soberw:while true do
 if(avg_sal  = 10000) 
 then leave soberw;
 end if;
 update employees set salary = salary * 0.9;
 select avg(salary) into avg_sal from employees;
 set count = count + 1;
 end while soberw;
 set num = count;
end//
delimiter;
call leave_while(@num);
select @num;
select avg(salary) from employees;

ITERATE 语句

ITERATE 语句：只能用在循环语句（LOOP、REPEAT 和 WHILE 语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把 ITERATE 理解为 continue，意思为“再次循环”。

语句基本格式如下：

ITERATE label

label 参数表示循环的标志。ITERATE 语句必须跟在循环标志前面。

举例一：定义局部变量 num，初始值为 0。循环结构中执行 num + 1 操作。
— 如果 num 10，则继续执行循环；
— 如果 num 15，则退出循环结构；

delimiter //
create procedure test_iterate()
begin
 declare num int default 0;
 soberw:loop
 set num = num + 1;
 if num   10
  then iterate soberw;
  end if;
 if num  15
 then leave soberw;
  end if;
 end loop soberw;
 select num;
end//
delimiter ;
call test_iterate();

游标

虽然我们也可以通过筛选条件 WHERE 和 HAVING，或者是限定返回记录的关键字 LIMIT 返回一条记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。

这个时候，就可以用到游标。游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

MySQL 中游标可以在存储过程和函数中使用。

游标使用的步骤：

1) 声明游标 DECLARE cursor_name CURSOR FOR select_statement;

2) 打开游标 OPEN cursor_name

3) 使用游标（从游标中获取数据） FETCH cursor_name INTO var_name [, var_name] …

4) 关闭游标 CLOSE cursor_name

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。

有 OPEN 就会有 CLOSE，也就是打开和关闭游标。当我们使用完游标后需要关闭掉该游标。因为游标会占用系统资源，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。而关闭游标的操作，会释放游标占用的系统资源。

举例：
— 创建存储过程“get_count_by_limit_total_salary()”，
— 声明 IN 参数 limit_total_salary，DOUBLE 类型；
— 声明 OUT 参数 total_count，INT 类型。
— 函数的功能可以实现累加薪资最高的几个员工的薪资值，
— 直到薪资总和达到 limit_total_salary 参数的值，
— 返回累加的人数给 total_count。

delimiter //
create procedure get_count_by_limit_total_salary(in limit_total_salary double,out total_count int)
begin
 #  保存薪资和
 declare sum_sal double default 0;
 #  保存累加人数
 declare count int default 0;
 #  定义单个工资
 declare emp_salary double default 0;
 #  定义游标
 declare cursor_sal cursor for select salary from employees order by salary desc;
 #  打开游标
 open cursor_sal;
 #  使用游标
 while sum_sal   limit_total_salary do
  fetch cursor_sal into emp_salary;
 set sum_sal = sum_sal + emp_salary;
 set count = count + 1;
 end while;
 #关闭游标
 close cursor_sal;
 #  给 total_count 赋值
 set total_count = count;
end//
delimiter ;
set @limit_total_salary = 200000;
call get_count_by_limit_total_salary(@limit_total_salary,@total_count);
select @total_count;

看完了这篇文章，相信你对“MySQL 中流程控制和游标的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！

正文完