(八)自定义函数


1、自定义函数

用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同。

函数的两个必要条件:

  1. 参数
  2. 返回值

但实际上参数与返回值之间并没有什么必然的联系,函数可以没有参数,但都必然有返回值。例如函数VERSION()并不需要参数,但是有返回值;而函数POWER()虽然有底数和指数这两个参数,但只有一个返回值。
  函数可以返回任意类型的值,同样可以接收这些类型的参数,对于MySQL而言,理论上的参数数量上限为1024个,这对日常的各项开发来说是完全足够的。

创建自定义函数
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body

COMMENT ' STRING ':
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }

可以理解为:
 创建函数 
 创建者,省略即为当前登录到MySQL的用户
 函数名
 特性
 函数体

关于特性:

  • CONTAINS SQL:包含SQL语句,但不包含读或写数据的语句;
  • NO SQL:不包含SQL语句;
  • READS SQL DATA:包含读数据的语句;
  • MODIFIES SQL DATA:包含写数据的语句;
  • SQL SECURITY { DEFINER | INVOKER }:指明谁有权限来执行

关于函数体:

  1. 函数体由合法的SQL语句构成;
  2. 函数体可以是简单的SELECT语句或INSERT语句;
  3. 函数体如果为复合结构,则使用BEGIN……END语句包含;
  4. 复合结构可以包含声明、循环、控制结构。

2、创建不带参数的自定义函数

以函数NOW()为例,如果需要修改显示时间的格式,每次都需要输入更多的代码会比较繁琐:

此时就可以创建自定义函数解决这一问题:


之后每次使用时只需要调用函数f1()即可。


2、创建带有参数的自定义函数

以求平均数为例,创建自定义函数f2():

之后调用函数f2()并填入参数,便可求得结果:


3、创建具有复合结构函数体的自定义函数

创建数据表“test”并插入两条记录用于演示:

通过自定义函数实现插入记录并返回该条记录的“id”值:

此时系统报错,虽然输入函数体并没有结束,但因为该条SQL语句结束而输入“;”,结果系统认为是整个语句输入完毕而执行了,因此导致错误。针对这一问题可以使用DELIMITER暂时修改分隔符予以解决:

之后就可以继续完成函数体了:

但此时系统再次报错,原因是有两条需要执行的语句,分别是插入用户名及返回最后插入的id值,因此就需要使用之前提到的“BEGIN……END”包含来构成完整的聚合体:


  现在就可以正常使用该自定义函数:


最后可以改回默认的分隔符“:”,并删除自定义函数:

语法结构
DROP FUNCTION [IF EXISTS] function_name


4、自定义函数的SQL语句汇总:

  • 创建自定义函数
    CREATE FUNCTION function_name
    RETURNS
    {STRING | INTEGER | REAL | DECIMAL}
    routine_body

  • 删除自定义函数
    DROP FUNCTION [IF EXISTS] function_name

  • 修改分隔符
    DELIMITER

版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

推荐阅读更多精彩内容