1 了解自定义函数

1.1 是什么

自定义函数是一种与存储过程十分相似的过程式数据库对象。它与存储过程一样，都是由SQL语句和过程式语句组成的代码片段，并且可以被应用程序和其他SQL语句调用。

1.2 自定义函数与存储过程的区别

自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。

自定义函数中必须有返回值，存储过程没有返回值。

可以直接对自定义函数进行调用而不需要使用call语句，而对存储过程的调用需要使用call语句。

2 创建自定义函数

2.1 语法

1 create function 函数名([参数])
2 returns 类型
3 函数主体

2.2 说明

1）函数名

指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。

2）参数

用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字in、out和inout。

3）类型

用于声明自定义函数返回值的数据类型。

4）函数主体

自定义函数的主体部分，也称函数体。所有在存储过程中使用的SQL语句在自定义函数中同样适用，包括前面所介绍的局部变量、set语句、流程控制语句、游标等。

除此之外，自定义函数体还必须包含返回值，用于指定自定义函数的返回值。在返回值中包含select语句时，select语句的返回结果只能是一行且只能有一列值。

2.3 实例

创建不带参数的自定义函数：

1 create function showTopGrade()
2 returns int(10)
3 return (select max(grade) from score);

创建带有参数的自定义函数：

1 create function getStuGrade(stu varchar(45))
2 returns int(10)
3 return (select max(grade) from score where student = stu);

创建带有多个语句的自定义函数：

1 delimiter $$
2 create function randNum()
3 returns int(5)
4 begin
5 declare i int default 0;
6 set i = floor(rand() * 10);
7 return i;
8 end $$
9 delimiter ;

3 使用自定义函数

3.1 语法

1 select 自定义函数名称([参数]);

3.2 实例

1 mysql> select showTopGrade();
2 +----------------+
3 | showTopGrade() |
4 +----------------+
5 | 95 |
6 +----------------+
7 1 row in set (0.00 sec)
8 
9 mysql>

4 查看自定义函数

4.1 实例

查看所有自定义函数：

 1 mysql> mysql> show function status;
 2 +------+--------------+----------+----------------+---------------+
 3 | Db | Name | Type | Definer | Security_type |
 4 +------+--------------+----------+----------------+---------------+
 5 | demo | getGrade | FUNCTION | root@localhost | DEFINER |
 6 | demo | getStuGrade | FUNCTION | root@localhost | DEFINER |
 7 | demo | showTopGrade | FUNCTION | root@localhost | DEFINER |
 8 +------+--------------+----------+----------------+---------------+
 9 3 rows in set (0.00 sec)
10 
11 mysql>

查看自定义函数的创建语句：

 1 mysql> mysql> show create function getGrade;
 2 +----------+----------------------------------------------------------------+
 3 | Function | Create Function |
 4 +----------+----------------------------------------------------------------+
 5 | getGrade | CREATE DEFINER=`root`@`localhost` FUNCTION `getGrade`() |
 6 | | RETURNS int(10) return (select grade from score where id = 12) |
 7 +----------+----------------------------------------------------------------+
 8 1 row in set (0.00 sec)
 9 
10 mysql>

5 修改自定义函数

可以通过先删除自定义函数，然后重新创建自定义函数的方法实现修改的操作。

6 删除自定义函数

6.1 语法

1 drop function 自定义函数名称

6.2 实例

1 mysql> drop function getGrade;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 mysql>

002MySQL学习010操作自定义函数