小编给大家分享一下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
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 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)则必须添加名称。 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 <= 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中流程控制和游标的示例分析”有了一定的了解,如果想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!