MySQL中的函数
1. 函数
-
函数与存储过程一样,也是一组预先编译好的SQL语句的集合,理解为批处理语句。将实现某个功能的一段代码封装起来,只暴露名字,隐藏具体实现过程。
-
函数与存储过程的区别
- 存储过程:可以有0个返回值,也可以有多个返回值,适合做批量插入、批量更新
- 函数:有且仅有1个返回值,适合做处理数据后返回一个结果
-
下面例子中可能使用的数据表
-
employees 员工表
-
departments 部门表
-
jobs 工种表
-
locations 地点表
员工表分别通过部门id、工种id与部门表、工种表关联,部门表通过地点id与地点表关联。
-
1.1. 函数的创建与调用
-
创建语法
create function 函数名(参数列表) returns 返回类型 begin 函数体 end
注意:
-
参数列表包含两部分:参数名 参数类型
-
函数体:肯定会有return语句,如果没有会报错;如果return语句没有放在函数体最后也不报错,但不建议。
-
函数体中仅有一句话,则可以省略begin end
-
使用delimiter语句设置结束标记
-
-
调用语法
select 函数名(参数列表);
1.2. 示例
-
无参数有返回
#创建函数,返回员工个数 delimiter $ #设置结束标记为$ create function myf1() returns int begin declare c int default 0; #定义局部变量 select count(*) into c #赋值 from employees; return c; end $ #调用函数 select myf1()$
-
有参数有返回
#根据员工名,返回工资 delimiter $ create function myf2(name varchar(20)) returns double begin set @sal=0; #定义用户变量 select salary into @sal from employees where last_name=name; return @sal; end $ #调用 select myf2('kochhar')$
1.3. 函数的查看与删除
-
查看函数的语法
show create function 函数名;
-
删除函数的语法
drop function 函数名;
2. 流程控制结构
- 顺序结构:程序从上往下依次执行
- 分支结构:程序从两条或多条路径中选择一条去执行
- 循环结构:程序在满足一定条件的基础上,重复执行一段代码
2.1. 分支结构
-
if函数(可以实现简单的双分支)
if(表达式1,表达式2,表达式3)
如果表达式1成立,则返回表达式2的值,否则返回表达式3的值。可以应用在任何地方。
-
case结构
-
情况1:一般用于实现等值判断
case 变量|表达式|字段 when 要判断的值 then 返回值1或语句1; when 要判断的值 then 返回值2或语句2; ... else 返回值n或语句n; #此句可省略 end case;
-
情况2:一般用于实现区间判断
case when 要判断的条件1 then 返回值1或语句1; when 要判断的条件2 then 返回值2或语句2; ... else 返回值n或语句n; #此句可省略 end case;
-
可以作为表达式,嵌套在其他语句中使用(此时只能返回值,且不加分号,end后也不加case和分号),可以放在任何地方,begin end中或begin end外面;也可以作为独立的语句去使用(此时返回语句且加分号,end后需加case和分号),只能放在begin end中。
-
如果when中的值或条件成立,则执行对应的then后面的语句,并且结束case;如果都不满足,则执行else中的语句或值;若else省略,并且所有when条件都不满足,则返回null。
-
示例
#创建存储过程,根据传入的成绩,显示等级,如成绩90-100为A,80-90为B,60-80为C,否则为D delimiter $ create procedure test_case(in score int) begin case when score>90 and score<=100 then select 'A'; when score>80 then select 'B'; when score>60 then select 'C'; else select 'D'; end case; end $ #调用存储过程 call test_case(75)$
-
-
if结构(可以实现多重分支)
-
语法
if 条件1 then 语句1; elseif 条件2 then 语句2; ... else 语句n; #此句可省略 end if;
应用在begin end中
-
示例
#创建函数,根据传入的成绩,返回等级,如成绩90-100为A,80-90为B,60-80为C,否则为D create function test_if(score int) returns varchar(20) begin declare grade varchar(20); if score>90 and score<=100 then select 'A' into grade; elseif score>80 then select 'B' into grade; elseif score>60 then select 'C' into grade; else select 'D' into grade; end if; return grade; end $ #调用函数 select test_if(75)$
-
2.2. 循环结构
- 分类
- while
- loop
- repeat
- 循环控制
- iterate:类似于continue,继续,结束本次循环,继续下一次
- leave:类似于break,跳出,结束当前所在循环
- 放在begin end中
2.2.1. while循环
-
语法
【标签:】while 循环条件 do 循环体; end while 【标签】;
中括号【】中内容是可选的,若要加循环控制语句,需要加标签,给循环起名字。
-
示例
#批量插入,根据次数插入到admin表中多条记录,admin表包含username和password两个字段 create procedure test_while1(in insertcount int) begin declare i int default 1; while i<=insertcount do insert into admin(username,password) values(concat('rose',i),'666'); set i=i+1; end while; end $ #调用 call test_while1(100)$
#添加leave语句。批量插入,根据次数插入到admin表中多条记录,如果次数>20则停止 create procedure test_while2(in insertcount int) begin declare i int default 1; a:while i<=insertcount do insert into admin(username,password) values(concat('rose',i),'666'); if i>=20 then leave a; end if; set i=i+1; end while a; end $ #调用 call test_while2(100)$
#添加iterate语句。批量插入,根据次数插入到admin表中多条记录,只插入偶数次 create procedure test_while3(in insertcount int) begin declare i int default 0; a:while i<insertcount do set i=i+1; if mod(i,2)!=0 then iterate a; end if; insert into admin(username,password) values(concat('rose',i),'666'); end while a; end $ #调用 call test_while3(100)$
2.2.2. loop循环
-
语法
【标签:】loop 循环体; end loop 【标签】;
可以用来模拟简单的死循环,若要结束循环,必须搭配leave使用。
2.2.3. repeat循环
-
语法
【标签:】repeat 循环体; until 结束循环的条件 end repeat 【标签】;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战