MySQL分析与整理 — 自定义函数

文章中所有操作均是在 MySQL 5.7 版本下进行的

MySQL 数据库除了自带的内置函数之外，如果这些内置函数并不是满足我们的需求，可以自定义函数，其用法和内置函数相同。自定义函数是有要求的，它有两个必要的要求：参数（可以没有参数）和返回值（必须有返回值，可以返回任何数据类型的值）。

1 使用自定义函数

1.1 创建自定义函数

-- 创建语法
delimiter $
create function function_name(parameter_name type,[parameter_name2 type, ...])
returns return_type
begin
	fun_body
end
$
/*
function_name：函数名，自定义的
parameter_name type：参数名和参数类型
return_type：返回值的类型
fun_body：函数执行的sql语句体，可能包含变量声明，循环和控制语句
fun_body中最后必须有一个“return”命令用来返回相应的返回值类型的数据

注：这里是基于控制台讲解的，可视化数据库操作工具自行处理
delimiter $ 是临时改变MySQL数据库的命令结束符
end后面的$就是用来结束整个函数定义的
因为MySQL数据库的命令结束符为英文分号
fun_body可能会是多个sql语句都是用英文分号结束的
不这么设置MySQL数据库会认为英文分号就结束了，造成函数定义操作
不用了，记得改回来，用“delimiter ;”命令恢复为英文分号
*/

1.2 查看自定义函数

-- 创建的自定义函数可以用下面语句来查看
select name from mysql.proc where db = '需要查看哪个数据库' and type = 'FUNCTION';
/*
show function status;是查看所有的函数
*/
-- 查看函数的创建代码
show create function 自定义函数名;

1.3 调用自定义函数

调用自定义函数和调用内置函数基本是一样的。

select 自定义函数名(parameter_value,...);

1.4 删除自定义函数

drop function 自定义函数名;

2 自定义函数应用示例

2.1 无参数的自定义函数

delimiter $
create function hello()
returns varchar(20)
begin
	return "Hello World!";
end
delimiter ; -- 换回默认结束符为英文分号
$
/*
如果只是返回一个简单的数据，并没有复杂的sql语句体，可以不用begin和end包裹
例如：
create function hello()
returns varchar(20)
return "Hello World!";
*/

2.2 有参数的自定义函数

/*
根据输入值范围输出成绩是否优良差
小于60 不及格
60-69 需要努力
70-79 成绩中等
80-89 成绩优秀
90-100 成绩良好
*/
delimiter $
create function get_grade(mark int)
returns varchar(20)
begin
	declare grade varchar(20);
	case floor(mark/10) 
		when 5 then
			set grade = '不及格';
		when 6 then
			set grade = '继续努力';
		when 7 then
			set grade = '成绩良好';
		else
			set grade = '成绩优秀';
	end case;
	return grade;
end
$
delimiter ; -- 换回默认结束符为英文分号
-- 调用
select get_grade(57);
+---------------+
| get_grade(57) |
+---------------+
| 不及格        |
+---------------+

结语

大部分关系型数据库都支持自定义函数，而且用法基本都是大同小异。所以当你熟悉一个数据库的自定义函数时候，再去看另外一个数据库就会觉得没那么难了。所以作者在写这种普及知识和应用的文章，很少提及一些原理或者理论性质的描述。当然示例比较简单，平时的自定义函数还得需要根据需求进行复杂的设计。