MySQL自定义函数创建与使用总结
MySQL自定义函数和存储过程类似,也需要在数据库中创建并保存。它与存储过程一样,都是由SQL语句和控制语句组成的代码片段,可以被应用程序和其他SQL语句调用。
MySQL自定义函数与存储过程存在以下区别:
1、存储过程可以定义in(输入),out(输出)、inout(输入输出)三种类型的参数,自定义函数不能定义输出参数;
2、自定义函数中必须包含RETURN语句,用来返回一个值,存储过程则不需要RETURN语句;
3、调用存储过程时使用CALL语句,自定义函数可以像系统函数一样直接调用。
一、创建MySQL自定义函数的语法
创建MySQL自定义函数使用create function命令,该命令格式如下:
delimiter //
create function func_name([para1 type[,para2 type]...)
returns type [characteristic ...]
begin
function_body
end //
delimiter ;
说明:
1、[delimiter //]修改默认的命令结束符为[//],函数体以[//]结束,[delimiter ;]恢复默认的结束符为[;]。
2、func_name:指定自定义函数的名称。
3、para1 type:用于指定函数的参数。参数不能指定关键字in、out和inout。
4、returns type:用于指定自定义函数返回值的数据类型。
5、function_body:自定义函数的主体,也称函数体。在存储过程中使用的SQL语句在自定义函数中也同样可以使用。此外,函数体必须包含一个RETURN语句,用于指定函数的返回值。
6、characteristic参数:指定使用SQL语句的限制。包括四种选择:
(1)CONTAINS SQL(默认值):表示子程序包含SQL语句,但不包含读或者写数据的语句;
(2)NO SQL:表示子程序不包含SQL;
(3)READS SQL DATA:表示子程序包含读数据的语句,但是不包含写数据的语句;
(4)MODIFIES SQL DATA:表示子程序包含写数据的语句。
二、自定义函数的创建举例
首先创建以下三个表:student(学生)、course(课程)、score(成绩),并输入数据:
create table student(
s_id int primary key auto_increment comment '学生学号',
s_name char(20) comment '学生姓名',
age int comment '学生年龄',
phone char(20) comment '电话'
);
create table course(
c_id int primary key auto_increment comment '课程编号',
c_name char(20) comment '课程名称',
credits int comment '课程学分'
);
create table score(
s_id int comment '学生学号',
c_id int comment '课程编号',
score int comment '成绩',
primary key(s_id,c_id)
);
insert into student
values(1001,'张平',20,'15937352444'),(1002,'王刚',21,'13637355888'),
(1003,'张静静',19,'13403736655'),(1004,'王涛',20,'13037388899'),
(1005,'王鹏飞',19,'13237366999');
insert into course
values(1,'数据库',4),(2,'数据结构',4),(3,'管理学',3),(4,'英语',4),(5,'电子商务',3);
insert into score
values(1001,1,80),(1001,2,90),(1001,3,77),(1001,4,87),(1001,5,69),
(1002,1,87),(1002,2,67),(1002,3,78),(1002,4,98),(1002,5,78),
(1003,1,66),(1003,2,77),(1003,3,88),(1003,4,99),(1003,5,66),
(1005,1,81),(1005,2,83),(1005,3,62),(1005,4,68),(1005,5,72),
(1004,1,72),(1004,2,60),(1004,3,84),(1004,4,88),(1004,5,74);
1、创建一个自定义函数,实现两个数相乘,返回乘积。
delimiter //
drop function if exists func_product;
create function func_product(m decimal(10,4),n decimal(10,4))
returns decimal(20,8) no sql
begin
declare result decimal(20,8);
set result=m*n;
return result;
end //
delimiter ;
mysql> select func_product(1.2,5);
+----------------+
| product(1.2,5) |
+----------------+
| 6.00000000 |
+----------------+
1 row in set (0.00 sec)
2、创建一个自定义函数,输入学生的姓名和所学课程的课程编号,查询该课程的成绩。
delimiter //
drop function if exists func_query_score;
create function func_query_score(student_name char(20),course_name char(20))
returns int reads sql data
begin
return (select score from score
where s_id=(select s_id from student where s_name=student_name) and
c_id=(select c_id from course where c_name=course_name));
end //
delimiter ;
mysql> select func_query_score('张平','数据结构') as '成绩';
+--------+
| 成绩 |
+--------+
| 90 |
+--------+
1 row in set (0.01 sec)
三、自定义函数的调用
和系统函数的调用完全相同,格式如下:
select function_name(para1,para2,...);
mysql> select func_product(1.2,5);
mysql> select func_query_score('张平','数据结构') as '成绩';
四、查看数据库中包含的自定义函数名称及详细信息
--格式: show function status where db='dbname';
mysql> show function status where db='wanggx' and name like 'func%'\G
*************************** 1. row ***************************
Db: wanggx
Name: func_product
Type: FUNCTION
Definer: root@localhost
Modified: 2019-11-21 12:46:50
Created: 2019-11-21 12:46:50
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Db: wanggx
Name: func_query_score
Type: FUNCTION
Definer: root@localhost
Modified: 2019-11-21 12:47:01
Created: 2019-11-21 12:47:01
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
五、查看指定的自定义函数的详细信息
--格式: show create function func_name;
mysql> show create function func_query_score\G
*************************** 1. row ***************************
Function: func_query_score
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `func_query_score`(student_name char(20),course_name char(20)) RETURNS int(11)
READS SQL DATA
begin
return (select score from score
where s_id=(select s_id from student where s_name=student_name) and
c_id=(select c_id from course where c_name=course_name));
end
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
六、修改自定义函数
delimiter //
alter function func_name([para1 type[,para2 type]...)
returns type [characteristic ...]
begin
function_body
end //
delimiter ;
七、删除自定义函数
drop function func_name;