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

lewis 5年前 (2020-03-11) 阅读数 6 #技术

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

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

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

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

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

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

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

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

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

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

跳转语句：ITERATE 和 LEAVE 语句

准备工作：

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

createdatabasedbtest16;
usedbtest16;
createtableemployees
as
select*fromatguigudb.employees;
createtabledepartments
as
select*fromatguigudb.departments;
select*fromemployees;
select*fromdepartments;

分支结构

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

CASE

IF语句

IF 语句的语法结构是：

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

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

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

举例一：单判断

delimiter//
createproceduretest_if()
begin
		#声明局部变量
		declarestu_namevarchar(15);
		ifstu_nameisnull
							thenselect'stu_nameisnull';
		endif;
end//
delimiter;
calltest_if();

举例二：二选一

delimiter//
createproceduretest_if2()
begin
		declareemailvarchar(25);
		
		ifemailisnull
					thenselect'emailisnull';
		else
					select'emailisnotnull';
		endif;
end//
delimiter;
calltest_if2();

举例三：多选一

delimiter//
createproceduretest_if3()
begin
		declareageintdefault20;
		
		ifage>40
					thenselect'中老年';
		elseifage>18
					thenselect'青壮年';
		elseifage>10
					thenselect'青少年';
		else
					select'孩童';
		endif;
end//
delimiter;
calltest_if3();

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

delimiter//
createprocedureupdate_salary_by_eid1(inemp_idint)
begin
#声明变量
	declaresaldouble;#记录员工工资
	declarehiredatedate;#记录入职日期
	#查询赋值
	selectsalary,hire_dateintosal,hiredatefromemployees
	whereemployee_id=emp_id;
	#判断条件并修改
	ifsal<8000anddatediff(now(),hiredate)/365>5
				thenupdateemployeessetsalary=salary+500whereemployee_id=emp_id;
	endif;
end//
delimiter;
set@emp_id=104;
#更新前104号员工工资情况：
selectsalary,employee_idfromemployeeswhereemployee_id=@emp_id;
#更新工资：
callupdate_salary_by_eid1(@emp_id);
#再次查询104号员工工资情况：
selectsalary,employee_idfromemployeeswhereemployee_id=@emp_id;

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

delimiter//
createprocedureupdate_salary_by_eid2(inemp_idint)
begin
#声明变量
	declaresaldouble;#记录员工工资
	declarehiredatedate;#记录入职日期
	#查询赋值
	selectsalary,hire_dateintosal,hiredatefromemployees
	whereemployee_id=emp_id;
	#判断条件并修改
	ifsal<9000anddatediff(now(),hiredate)/365>5
				thenupdateemployeessetsalary=salary+500whereemployee_id=emp_id;
	else
				updateemployeessetsalary=salary+100whereemployee_id=emp_id;
	endif;
end//
delimiter;
#以103,104员工为例
#更新前员工工资情况：
selectsalary,employee_idfromemployeeswhereemployee_idin(103,104);
#更新工资：
callupdate_salary_by_eid2(103);
callupdate_salary_by_eid2(104);
#再次查询员工工资情况：
selectsalary,employee_idfromemployeeswhereemployee_idin(103,104);

场景举例三:声明存储过程“update_salary_by_eid3”，定义IN参数emp_id，输入员工编号。判断该员工薪资如果低于9000元，就更新薪资为9000元；薪资如果大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01；其他的涨薪100元。

delimiter//
createprocedureupdate_salary_by_eid3(inemp_idint)
begin
#声明变量
	declaresaldouble;#记录员工工资
	declareemp_commission_pctdouble;#记录奖金比例
	#查询赋值
	selectsalary,commission_pctintosal,emp_commission_pctfromemployees
	whereemployee_id=emp_id;
	#判断条件并修改
	ifsal<9000
			thenupdateemployeessetsalary=9000whereemployee_id=emp_id;
	elseifsal<10000andemp_commission_pctisnull
	thenupdateemployeessetcommission_pct=0.01whereemployee_id=emp_id;
	else
			updateemployeessetsalary=salary+100whereemployee_id=emp_id;
	endif;
end//
delimiter;
#以102,103,104员工为例
#更新前员工工资情况：
selectsalary,employee_id,commission_pctfromemployeeswhereemployee_idin(102,103,104);
#更新工资：
callupdate_salary_by_eid3(102);
callupdate_salary_by_eid3(103);
callupdate_salary_by_eid3(104);
#再次查询员工工资情况：
selectsalary,employee_id,commission_pctfromemployeeswhereemployee_idin(102,103,104);

CASE语句

CASE 语句的语法结构1：

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

CASE 语句的语法结构2：

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

举例一：case ... when ... then ...

delimiter//
createproceduretest_case()
begin
		declarevarintdefault2;
		
		casevar
			when1thenselect'var=1';
					when2thenselect'var=2';
					when3thenselect'var=3';
					elseselect'other';
		endcase;
end//
delimiter;
calltest_case();

举例二：case when ... then ...

delimiter//
createproceduretest_case2()
begin
		declarevar1intdefault10;
		
		casewhenvar1>=100thenselect'三位数';
					whenvar1>=10thenselect'两位数';
					elseselect'个位数';
		endcase;
end//
delimiter;
calltest_case2();

场景举例一：-- 声明存储过程“update_salary_by_eid4”，定义IN参数emp_id，输入员工编号。 -- 判断该员工薪资如果低于9000元，就更新薪资为9000元； -- 薪资大于等于9000元且低于10000的，但是奖金比例为NULL的，就更新奖金比例为0.01； -- 其他的涨薪100元。

delimiter//
createprocedureupdate_salary_by_eid4(inemp_idint)
begin
#声明变量
	declaresaldouble;#记录员工工资
	declareemp_commission_pctdouble;#记录奖金比例
	#查询赋值
	selectsalary,commission_pctintosal,emp_commission_pctfromemployees
	whereemployee_id=emp_id;
	#判断条件并修改
	case
	whensal<9000
			thenupdateemployeessetsalary=9000whereemployee_id=emp_id;
	whensal<10000andemp_commission_pctisnull
	thenupdateemployeessetcommission_pct=0.01whereemployee_id=emp_id;
	else
			updateemployeessetsalary=salary+100whereemployee_id=emp_id;
	endcase;
end//
delimiter;
#以103,104,105员工为例
#更新前员工工资情况：
selectsalary,employee_id,commission_pctfromemployeeswhereemployee_idin(103,104,105);
#更新工资：
callupdate_salary_by_eid3(103);
callupdate_salary_by_eid3(104);
callupdate_salary_by_eid3(105);
#再次查询员工工资情况：
selectsalary,employee_id,commission_pctfromemployeeswhereemployee_idin(103,104,105);

场景举例二：-- 声明存储过程update_salary_by_eid5，定义IN参数emp_id，输入员工编号。 -- 判断该员工的入职年限，如果是0年，薪资涨50； -- 如果是1年，薪资涨100； -- 如果是2年，薪资涨200； -- 如果是3年，薪资涨300； -- 如果是4年，薪资涨400； -- 其他的涨薪500。

delimiter//
createprocedureupdate_salary_by_eid5(inemp_idint)
begin
#声明变量
	declaresaldouble;#记录员工工资
	declarehire_yeardouble;#记录入职日期
	#查询赋值
	selectsalary,round(datediff(now(),hire_date)/365)intosal,hire_yearfromemployees
	whereemployee_id=emp_id;
	#判断条件并修改
	casehire_year
			when0thenupdateemployeessetsalary=salary+50whereemployee_id=emp_id;
			when1thenupdateemployeessetsalary=salary+100whereemployee_id=emp_id;
			when2thenupdateemployeessetsalary=salary+200whereemployee_id=emp_id;
			when3thenupdateemployeessetsalary=salary+300whereemployee_id=emp_id;
			when4thenupdateemployeessetsalary=salary+400whereemployee_id=emp_id;
			elseupdateemployeessetsalary=salary+500whereemployee_id=emp_id;
	endcase;
end//
delimiter;
#以107员工为例
#更新前员工工资情况：
selectsalary,employee_id,hire_datefromemployeeswhereemployee_id=107;
#更新工资：
callupdate_salary_by_eid5(107);
#再次查询员工工资情况：
selectsalary,employee_id,hire_datefromemployeeswhereemployee_id=107;

#针对场景二，此种写法略显不足，重复的书写相同的更新语句，
#其实观察下来也就金额不同，可以有改进改进如下：
delimiter//
createprocedureupdate_salary_by_eid6(inemp_idint)
begin
#声明变量
	declaresaldouble;#记录员工工资
	declarehire_yeardouble;#记录入职日期
	declareadd_saldouble;#保存更新的金额
	#查询赋值
	selectsalary,round(datediff(now(),hire_date)/365)intosal,hire_yearfromemployees
	whereemployee_id=emp_id;
	#判断条件并修改
	casehire_year
			when0thensetadd_sal=50;
			when1thensetadd_sal=100;
			when2thensetadd_sal=200;
			when3thensetadd_sal=300;
			when4thensetadd_sal=400;
			elsesetadd_sal=500;
	endcase;
	#根据当前add_sal值修改
	updateemployeessetsalary=salary+add_salwhereemployee_id=emp_id;
end//
delimiter;
#以108员工为例
#更新前员工工资情况：
selectsalary,employee_id,hire_datefromemployeeswhereemployee_id=108;
#更新工资：
callupdate_salary_by_eid5(108);
#再次查询员工工资情况：
selectsalary,employee_id,hire_datefromemployeeswhereemployee_id=108;

循环结构

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

LOOP

WHILE

REPEAT

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

1.初始化条件

2.循环条件

3.循环体

4.迭代条件

LOOP语句

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

LOOP语句的基本格式如下：

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

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

举例一：

delimiter//
createproceduretest_loop()
begin
		#声明变量
		declarenumintdefault1;
		
		soberw:loop
				#重新赋值
				setnum=num+1;
				ifnum>=10
						thenleavesoberw;
				endif;
		endloopsoberw;
		
		#查看num
		selectnum;
end//
delimiter;
calltest_loop();

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

delimiter//
createprocedureupdate_salary_loop(outnumint)
begin
#声明变量
		#记录保存平均薪资
		declareavg_saldoubledefault0;
		#记录循环次数
		declarecountintdefault0;
		#获取当前平均薪资
		selectavg(salary)intoavg_salfromemployees;
		
		soberw:loop
				#结束条件
				ifavg_sal>=12000
								thenleavesoberw;
						endif;
				#更新工资
				updateemployeessetsalary=salary*1.1;
				#保证当前平均薪资为最新
				selectavg(salary)intoavg_salfromemployees;
				#记录次数
				setcount=count+1;
		endloopsoberw;
		#返回num
		setnum=count;
end//
delimiter;
callupdate_salary_loop(@num);
select@num;
selectavg(salary)fromemployees;

WHILE语句

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

[while_label:]WHILE循环条件DO
	循环体
ENDWHILE[while_label];

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

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

delimiter//
createproceduretest_while()
begin
#初始化条件
		declareiintdefault1;
		#循环条件
		whilei<10do
				#循环体略
				#迭代条件
				seti=i+1;
		endwhile;
		selecti;
end//
delimiter;
calltest_while();

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

delimiter//
createprocedureupdate_salary_while(outnumint)
begin
#声明变量
		#记录保存平均薪资
		declareavg_saldoubledefault0;
		#记录循环次数
		declarecountintdefault0;
		#获取当前平均薪资初始化条件
		selectavg(salary)intoavg_salfromemployees;
		
		#循环条件
		soberw:whileavg_sal>5000do
				#循环体		
				#更新工资
				updateemployeessetsalary=salary*0.9;
				#记录次数
				setcount=count+1;
				
				#迭代条件
				#保证当前平均薪资为最新
				selectavg(salary)intoavg_salfromemployees;
				
		endwhilesoberw;
		#返回num
		setnum=count;
end//
delimiter;
callupdate_salary_while(@num);
select@num;
selectavg(salary)fromemployees;

REPEAT语句

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

REPEAT语句的基本格式如下：

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

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

举例一：

DELIMITER//
CREATEPROCEDUREtest_repeat()
BEGIN	
	DECLAREiINTDEFAULT0;
	
	REPEAT
		SETi=i+1;
	UNTILi>=10
	ENDREPEAT;
	
	SELECTi;
END//
DELIMITER;
calltest_repeat();

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

delimiter//
createprocedureupdate_salary_repeat(outnumint)
begin
		#声明变量
		#记录保存平均薪资
		declareavg_saldoubledefault0;
		#记录循环次数
		declarecountintdefault0;
		#获取当前平均薪资初始化条件
		selectavg(salary)intoavg_salfromemployees;
		
		#循环条件
		soberw:repeat
				#循环体		
				#更新工资
				updateemployeessetsalary=salary*1.15;
				#记录次数
				setcount=count+1;
				
				#迭代条件
				#保证当前平均薪资为最新
				selectavg(salary)intoavg_salfromemployees;
				
		untilavg_sal>=13000
		endrepeatsoberw;
		#返回num
		setnum=count;
end//
delimiter;
callupdate_salary_repeat(@num);
select@num;
selectavg(salary)fromemployees;

对比三种循环结构：

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//
createprocedureleave_begin(innumint)
soberw:begin
		ifnum<=0
				thenleavesoberw;
		elseifnum=1
				thenselectavg(salary)fromemployees;
		elseifnum=2
				thenselectmin(salary)fromemployees;
		elseifnum>2
				thenselectmax(salary)fromemployees;
		endif;
		selectcount(1)fromemployees;
end//
delimiter;
callleave_begin(2);
callleave_begin(-1);

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

delimiter//
createprocedureleave_while(outnumint)
begin
		declareavg_saldouble;
		declarecountintdefault0;
		
		selectavg(salary)intoavg_salfromemployees;
		soberw:whiletruedo
		
				if(avg_sal<=10000)
						thenleavesoberw;
				endif;
				
				updateemployeessetsalary=salary*0.9;
				selectavg(salary)intoavg_salfromemployees;
				setcount=count+1;
				
		endwhilesoberw;
		
		setnum=count;
end//
delimiter;
callleave_while(@num);
select@num;
selectavg(salary)fromemployees;

ITERATE语句

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

语句基本格式如下：

ITERATElabel

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

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

delimiter//
createproceduretest_iterate()
begin
		declarenumintdefault0;
		soberw:loop
				setnum=num+1;
				ifnum<10
				theniteratesoberw;
			endif;
				ifnum>15
					thenleavesoberw;
			endif;
		endloopsoberw;
		
		selectnum;
end//
delimiter;
calltest_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//
createprocedureget_count_by_limit_total_salary(inlimit_total_salarydouble,outtotal_countint)
begin
#保存薪资和
		declaresum_saldoubledefault0;
		#保存累加人数
		declarecountintdefault0;
		#定义单个工资
		declareemp_salarydoubledefault0;
		#定义游标
		declarecursor_salcursorforselectsalaryfromemployeesorderbysalarydesc;
		#打开游标
		opencursor_sal;
		#使用游标
		whilesum_sal<limit_total_salarydo
		fetchcursor_salintoemp_salary;
			setsum_sal=sum_sal+emp_salary;
			setcount=count+1;
		endwhile;
		#关闭游标
		closecursor_sal;
		#给total_count赋值
		settotal_count=count;
end//
delimiter;
set@limit_total_salary=200000;
callget_count_by_limit_total_salary(@limit_total_salary,@total_count);
select@total_count;

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

版权声明

本文仅代表作者观点，不代表博信信息网立场。

上一篇：数据库中partitionby和groupby语句的使用有何区别下一篇：MySQL读写分离基本原理详解