MySQL自定义函数

admin管理员组

文章数量:1794759

MySQL自定义函数

自定义函数 1 自定义函数和存储过程的区别

存储过程实现的功能要复杂些，而函数的针对性更强； 存储过程可以返回多个值；函数只能有一个返回值； 存储过程一般独立的来执行；而函数可以作为其它sql语句的组成部分来出现(作用等同内置函数)。 自定义函数实用较少，存储过程实用较多

1.1 函数定义

MySQL的函数定义语法如下：

CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION functionName ( varName varType [, ... ] ) RETURNS returnVarType [characteristic ...] routine_body

functionName：函数名，同MySQL内置函数一样，大小写不敏感 varName: 形参名 varType: 形参类型，其与varName配对使用。形参数量不限( ≥ 0 \\geq 0 ≥0) returnVarType: 返回值类型。函数必须有且只能有一个返回值 characteristic：函数特性，下将详述 routine_body：函数体。函数体中必须含有 return 语句，当函数体为复合结构时，需要使用begin … end 语句

1.2 变量定义

变量分为局部变量与用户变量

1.2.1 局部变量

定义在sql语句块中的变量，常见于存储过程和函数的 begin … end 中，语句块执行完后局部变量则结束生命周期 声明定义 函数中可以通过 declare 声明定义局部变量，其作用域为 begin … end 的函数体中。默认初值为null，可以通过default指定该语句中所有定义变量的初值

declare var [, …] varType [defualt initVal]; 注意: declare声明定义语句，必须放在begin…end函数体中最前面的位置

赋值 通过set给变量赋值

set var = expression [, var = expression, ...]; set var := expression [, var = expression, ...];

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

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

示例：

DROP FUNCTION if exists `myfunTest` $$ create function `myfunTest`(idx int) returns int 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 num = idx; # 使用 select into 语句 set res := res * (data1 + data2); # 使用set语句, := 操作符赋值 return (res); end$$ DELIMITER ; 1.2.2 用户变量

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

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

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

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

select @varName:=field [as field] [, ...] from tableName where condition; select @varName:=Val; 2 常用流程控制 2.1 if 语句 if condition then statements [ elseif condition then statements ] [ else statements ] end if;

示例如下:

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; 2.2 CASE 条件语句 CASE WHEN cj >= 60 THEN SET pf = '及格'; ELSE SET pf = '不及格'; 2.2 while 语句 [label:] while condition do statments end while [label]

示例如下:

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;

测试结果如下:

2.3 leave、iterate

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

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

示例1:

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;

测试结果如下:

示例2:

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;

测试结果如下:

2.4 loop

loop为死循环，需要手动退出循环，我们可以使用 leave来退出循环

delimiter // create procedure s_loop() begin # 声明计数器 declare i int default 1; # 开始循环 num: loop # 查询计数器记录的值 select i; # 判断大于等于停止计数 if i >= 10 then leave num; end if; # 计数器自增1 set i = i + 1; # 结束循环 end loop num; end // call s_loop();

repeat

2.5 别名

语法：

[别名:] LOOP 循环语句 END LOOP [别名]

注意：别名和别名控制的是同一个标签。

3 函数相关的操作 3.1 查看函数状态

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

show function status [like functionName];

示例如下:

3.2 查看函数定义

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

show create function functionName;

示例如下:

DELIMITER $$ DROP FUNCTION if exists `myfunTest` $$ create function `myfunTest`(idx int) returns int 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 num = idx; # 使用 select into 语句 set res := res * (data1 + data2); # 使用set语句, := 操作符赋值 return (res); end$$ DELIMITER ; show create function myfunTest CREATE DEFINER=`dev`@`%` FUNCTION `myfunTest`(idx int) RETURNS int(11) 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 num = idx; # 使用 select into 语句 set res := res * (data1 + data2); # 使用set语句, := 操作符赋值 return (res); end 3.3 修改函数特性

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

alter function functionName [characteristic ...] 3.4删除函数

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

drop function [if exists] functionName

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

4 应用样例 4.1 替换函数replace DELIMITER $$ DROP FUNCTION IF EXISTS replace_test $$ CREATE FUNCTION rank_test(col_name VARCHAR (50) CHARSET utf8) RETURNS VARCHAR (50) BEGIN RETURN replace(replace(replace(col_name,'省',''),'市',''),'县',''); END $$ DELIMITER ; select city_name,rank_test(city_name) from DIM_CITY; 4.2身份证号校验 DELIMITER $$ DROP FUNCTION IF EXISTS `is_id_card`$$ CREATE FUNCTION `is_id_card` (number VARCHAR (20) CHARSET utf8) RETURNS TINYINT (1) BEGIN DECLARE flag BOOL DEFAULT FALSE ; IF ( LENGTH(number) = 18 and number REGEXP '[1-9]{1}[0-9]{17}|[1-9]{1}[0-9]{16}X' AND substr(number,7,4) between '1900' and year(now()) AND SUBSTR(number,11,2) BETWEEN '01' AND '12' AND SUBSTR(number,13,2) BETWEEN '01' AND DAY(LAST_DAY(CONCAT(SUBSTR(number,7,4),'-',SUBSTR(number,11,2),'-01'))) OR ( LENGTH(number) = 15 AND number REGEXP '[1-9]{1}[0-9]{14}' AND substr(number,7,2) between '00' and '99' AND SUBSTR(number,9,2) BETWEEN '01' AND '12' AND SUBSTR(number,11,2) BETWEEN '01' AND DAY(LAST_DAY(CONCAT('19',SUBSTR(number,7,2),'-',SUBSTR(number,9,2),'-01'))) ) ) THEN SET flag = TRUE ; END IF ; RETURN flag ; END $$ DELIMITER ; select is_id_card('410725199709119870'); DROP FUNCTION IF EXISTS `is_id_card`; 补充知识 MySQL正则表达式 模式模式匹配什么

^	匹配字符串开头
$	匹配字符串结尾
.	匹配任意单个字符
[…]	匹配方括号间列出的任意字符
[^…]	匹配方括号间未列出的任意字符
p1|p2|p3	交替匹配任意 p1 或 p2 或 p3
*	匹配前面的元素的零次或多次
+	匹配前面的元素的一次或多次
{n}	匹配前面的元素 n 次
{m,n}	匹配前面的元素 m 至 n 次

本文标签： 自定义函数mySQL