MySQL 之自定义函数

MySQL不仅内置了一些常用函数,同时也支持自定义函数,来满足开发者的需求

abstract.jpg

函数定义

MySQL的函数定义语法如下:

1
2
3
4
5
6
CREATE  
[DEFINER = { user | CURRENT_USER }]
FUNCTION functionName ( varName varType [, ... ] )
RETURNS returnVarType
[characteristic ...]
routine_body
  • functionName:函数名,同MySQL内置函数一样,大小写不敏感
  • varName: 形参名
  • varType: 形参类型,其与varName配对使用。形参数量不限( $\geq 0$)
  • returnVarType: 返回值类型。函数必须有且只能有一个返回值
  • characteristic:函数特性,下将详述
  • routine_body:函数体。函数体中必须含有 return 语句,当函数体为复合结构时,需要使用begin … end 语句

characteristic的可用参数值如下:

说明
language sql 指明函数体的语言类型, 目前仅支持sql
[not] deterministic deterministic 指明函数的结果是确定的,即相同的输入会得到相同的输出;not deterministic意为结果不确定。默认为 not deterministic
{ contains sql | no sql | reads sql data | modifies sql data } 指明函数体使用sql语句的限制。contains sql意为函数体包含sql语句,但不包含读写数据的sql语句;no sql意为函数体不包含sql语句;reads sql data意为函数体包含读数据sql语句;modifies sql data意为函数体包含写数据的sql语句。默认为contains sql
sql security { definer | invoker } 指明谁有权限执行该函数。definer意为只有定义者才能执行;invoker意为拥有权限的调用者可以执行。默认为definer
comment ‘message’ 函数的注释信息,指明函数的功能

示例如下:

  • 函数体为简单结构:
1
2
3
4
5
create    
function myfun_getAvg(num1 int, num2 int)   
comment '计算两个数的均值'
returns int   
return (num1+num2)/2;
  • 函数体为复合结构:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create
function myfun_getMax(num1 int, num2 int)
returns int
begin
declare res int;
if(num1 > num2) then
set res = num1;
elseif (num1 < num2) then
set res = num2;
else
set res = 0;
end if;
return res;
end;

变量

局部变量

定义在sql语句块中的变量,常见于存储过程和函数的 begin … end 中,语句块执行完后局部变量则结束生命周期

声明定义

函数中可以通过 declare 声明定义局部变量,其作用域为 begin … end 的函数体中。默认初值为nul,可以通过default指定该语句中所有定义变量的初值

1
declare var [, ...] varType [defualt initVal];

Note: declare声明定义语句,必须放在begin…end函数体中最前面的位置

赋值

  • 通过set给变量赋值
1
2
set var = expression [, var = expression, ...];
set var := expression [, var = expression, ...];

[Note]: sql下的 = 操作符是比较(判定是否相等)操作符,只有在set语句中可作为赋值操作符使用。故在其他语句中,赋值操作应该使用 := 操作符

  • 通过select into 给变量赋值

通过select语句将所查询出的字段数据依次赋值到 into 后的变量中。值得一提的是,当select查询结果为空时(即,无记录),则不对变量进行赋值操作;当select查询的结果不止一条时,MySQL将报错,函数执行失败

1
select filed1 [, ...] into var1 [, ...] from tableName where conditon

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create
function myfunTest(idx int)
returns int
comment '测试函数'
begin
declare res int; # 声明定义1个变量, 初值默认为 null
declare num1, num2 int default 27; # 声明定义多个变量,初值全部为27
declare data1, data2 int; # 声明定义多个变量,初值全部默认为 null
set num2 = 23, res = num1 + num2; # 使用set语句, = 操作符赋值
set data1 = 1, data2 = 1;
select num, price into data1, data2 from test2 where id = idx; # 使用 select into 语句
set res := res * (data1 + data2); # 使用set语句, := 操作符赋值
return (res);
end;

函数测试结果如下:

figure 1.jpeg

用户变量

定义在当前客户端的连接下的变量,其作用域在当前客户端连接下均有效,当当前客户端断开连接后则该变量结束生命周期。其对其他客户端连接不可见

赋值

用户变量无需先行声明创建,直接赋值使用即可。赋值时,当前客户端下若无该用户变量,则会自动创建并完成赋值;查看一个不存在的用户变量时(e.g., select @foo)返回null。需要注意的是,用户变量的变量名必须以 @ 开头

  • 通过set 赋值
1
2
3
set @varName = val;     # 对名为 @varName 用户变量赋值
set @varName := val; # 对名为 @varName 用户变量赋值
select @varName; # 查看名为 @varName 用户变量的值
  • 通过 select 赋值

通过select语句将所查询出的字段数据赋值到变量中,需要注意的是,只能使用 := 操作符赋值

1
2
select @varName:=field [as field] [, ...] from tableName where condition;
select @varName:=Val;

示例

figure 2.jpeg

常用流程控制

if 语句

1
2
3
4
5
6
7
if condition then
statements
[ elseif condition then
statements ]
[ else
statements ]
end if;

示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create
function getLevelByScore(score int)
returns varchar(255)
comment '根据成绩获取评级'
begin
declare leavel varchar(255);
if (score >= 90) then
set leavel = '优秀';
elseif (score >= 80) then
set leavel = '中等';
elseif (score >= 60) then
set leavel = '及格';
else
set leavel = '不及格';
end if;
return leavel;
end;

测试结果如下:

figure 3.jpeg

while 语句

1
2
3
[label:] while condition do
statments
end while [label]

示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
create
function getSum(num int)
returns int
comment '计算0-num的和'
begin
declare i,sum int default 0;
while (i<=num) do
set sum = sum + i;
set i = i + 1;
end while;
return sum;
end;

测试结果如下:

figure 4.png

leave、iterate

levae 和 iterate 分别用于循环控制的跳出整个循环 和 跳过循环体的剩余部分并直接进行下一次循环,作用相当于C语言中的 break和 continue。区别在于C语言中的break、continue都是针对所在层循环的控制。而levae 和 iterate是针对label所标注的循环的控制,故其可以实现从最内层循环直接跳出

1
2
levae label;    # 跳出label所标注的循环结构
iterate label; # 跳过循环体的剩余部分,直接开始label所标注的下一次循环

示例1:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create
function testLeave()
returns varchar(255)
begin
declare i,j int default 0;
loop1: while (i<=5) do

set j = 0;
while (j<=i) do
if(j = 3) then
leave loop1;
end if;
set j = j + 1;
end while;

set i = i + 1;
end while loop1;

return concat('i: ', i, ' j:', j);
end;

测试结果如下:

figure 5.png

示例2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create
function testIterate()
returns varchar(255)
begin
declare i,j int default 0;
loop1: while (i<=5) do

set i = i + 1;

set j = 0;
while (j<=i) do
if(j = 3) then
iterate loop1;
end if;
set j = j + 1;
end while;

end while loop1;

return concat('i: ', i, ' j:', j);
end;

测试结果如下:

figure 6.png

函数相关的操作

查看函数状态

通过show status 命令查看函数的相关信息。可以在其后面使用 like 语句进行函数名匹配,其中functionName同样支持 % 进行模糊匹配

1
show function status [like functionName];

示例如下:

figure 7.jpeg

查看函数定义

通过show create 命令查看函数的定义内容

1
show create function functionName;

示例如下:

figure 8.jpeg

修改函数特性

通过alter function实现对函数特性characteristic的修改,注意,不是对函数定义内容的修改

1
alter function functionName [characteristic ...]

示例如下:

figure 9.jpeg

删除函数

通过drop function 删除函数。当指定函数不存在时,会报错,可以添加 if exists 避免出现报错

1
drop function [if exists] functionName

测试结果如下:

figure 10.jpeg

Note

在命令行中其默认将 ; 符号作为结束符来执行语句。所以如果我们在命令行中创建函数,需要使用 delimiter 命令重定义结束符
如下图所示,先通过 delimiter $$ 将结束符更改为 $$ (可随意更改,一般常用$$)再创建函数。函数创建完毕后,通过我们重定义后的结束符结束(即 $$ )。最后不要忘记,通过 delimiter ; 将结束符重新修改为 ;

figure 11.jpeg

测试结果如下:

figure 12.jpeg

0%