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

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

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

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

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

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

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

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

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

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

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

跳转语句：ITERATE 和 LEAVE 语句

准备工作：

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

create databasedbtest16;use dbtest16; create table employees  as  select * from atguigudb.employees; create table departments as 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 saldouble;  # 记录员工工资 declare hiredate date;  # 记录入职日期 # 查询赋值 select salary,hire_date into sal,hiredate from employees  where employee_id = emp_id; # 判断条件并修改 if sal < 8000 anddatediff(now(),hiredate)/365 > 5  then update employees set salary = salary + 500 whereemployee_id = emp_id; endif; end// delimiter; set @emp_id = 104; # 更新前104号员工工资情况： selectsalary,employee_idfrom 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 procedureupdate_salary_by_eid2(in emp_id int) begin   # 声明变量  declare saldouble;  # 记录员工工资 declare hiredate date;  # 记录入职日期 # 查询赋值 select salary,hire_date into sal,hiredate fromemployeeswhere employee_id = emp_id; # 判断条件并修改 if sal < 9000 and datediff(now(),hiredate)/365 > 5  then update employeesset salary = salary + 500 where employee_id = emp_id; else  update employees setsalary = 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 whereemployee_idin (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_pctdouble;  # 记录奖金比例 # 查询赋值 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 whereemployee_id = emp_id; elseif sal <10000 and emp_commission_pct is null     then update employees setcommission_pct =0.01 where employee_id = emp_id; else update employees set salary = salary + 100 whereemployee_id = emp_id; endif; end// delimiter; # 以102,103,104员工为例 # 更新前员工工资情况： selectsalary,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); # 再次查询员工工资情况： selectsalary,employee_id,commission_pct from employees where employee_id in (102,103,104);CASE语句

CASE 语句的语法结构1：

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

CASE 语句的语法结构2：

#情况二：类似于多重ifCASE  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 thenselect var = 1; when 2 then select var = 2; when 3 thenselect 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 saldouble;  # 记录员工工资declare emp_commission_pctdouble;  # 记录奖金比例 # 查询赋值 select salary,commission_pct into sal,emp_commission_pct from employees  whereemployee_id = emp_id;# 判断条件并修改 case when sal < 9000 then update employees set salary = 9000 whereemployee_id = emp_id;when sal < 10000 and emp_commission_pct is null     then update employees set commission_pct = 0.01 whereemployee_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 fromemployeeswhere 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 fromemployeeswhere 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  whereemployee_id = emp_id;# 判断条件并修改 case hire_year when 0 then update employees set salary = salary + 50 whereemployee_id = emp_id; when 1 thenupdate employees set salary = salary + 100 whereemployee_id = emp_id; when 2 thenupdate employees set salary = salary + 200 whereemployee_id = emp_id; when 3 thenupdate employees set salary = salary + 300 where employee_id = emp_id; when 4 then updateemployeesset salary = salary + 400 where employee_id = emp_id; else update employees setsalary = salary +500 where employee_id = emp_id; end case; end// delimiter; # 以107员工为例 # 更新前员工工资情况： selectsalary,employee_id,hire_datefrom 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 thenset 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员工为例 # 更新前员工工资情况： selectsalary,employee_id,hire_datefrom employees where employee_id = 108; # 更新工资： call update_salary_by_eid5(108); # 再次查询员工工资情况： selectsalary,employee_id,hire_datefrom 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 numint default 1; soberw:loop # 重新赋值 setnum = 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_saldouble 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 setsalary = 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 proceduretest_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_saldouble default 0; # 记录循环次数declare countint 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 setnum = 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 DEFAULT0; REPEAT  SET i = i + 1; UNTIL i >= 10 END REPEAT; SELECT i; END // DELIMITER ; calltest_repeat();

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

delimiter // create procedure update_salary_repeat(out num int) begin # 声明变量 # 记录保存平均薪资 declare avg_saldouble default 0; # 记录循环次数declare countint 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 setnum = count; end// delimiter ; call update_salary_repeat(@num); select @num; select avg(salary) from employees;

对比三种循环结构：

1、这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。 2、 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 <= 0then 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)  thenleave 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_saldouble default 0; # 保存累加人数 declare count int default 0; # 定义单个工资declare emp_salarydouble default 0; # 定义游标 declare cursor_sal cursor for select salary from employees order bysalary 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中流程控制和游标的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注行业资讯频道，感谢各位的阅读！