自定义函数
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自定义函数
发布评论