mysql 自定义函数create function

方便后续查询，做以下记录；
自定义函数是一种与存储过程十分相似的过程式数据库对象，
它与存储过程一样，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。
自定义函数与存储过程之间存在几点区别：

1. 自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。
2. 自定义函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。
3. 可以直接对自定义函数进行调用而不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。
4. 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新。
5. 函数：有且仅有1 个返回，适合做处理数据后返回一个结果。

一、创建&使用存储函数

创建并使用自定义函数
使用 CREATE FUNCTION 语句创建自定义函数。
语法格式如下：

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
  RETURNS <类型>
  <函数主体>
1
2
3

-- 1、创建无参数函数(查询user表中最大的ID值)
-- create function getUserMaxId()
-- returns int(11) deterministic
-- RETURN (SELECT max(id) from user);

-- 2、使用getUserMaxId()函数
-- SELECT getUserMaxId();

-- 3、创建带参数函数
-- 需求：自定义nvl函数，参数1为null时返回参数2，参数1不为null正常返回参数1
-- CREATE FUNCTION nvl(str1 varchar(4000), str2 varchar(4000))
-- RETURNS VARCHAR(4000) DETERMINISTIC
-- return COALESCE(str1, str2);

-- 4、使用自定义nvl函数
-- set @str1 = "中国";
-- set @str2 = "默认值";
-- SELECT nvl(@str1, @str2);

-- 5、在create function 后添加if not exists可避免已经存在的函数重复添加，产生报错信息；
CREATE FUNCTION IF NOT EXISTS test.get_total(username VARCHAR(20))
RETURNS DECIMAL(10,2) deterministic
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(score * 10) INTO total FROM user WHERE username = username;
    RETURN total;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

存储函数返回table类型，使用mysql v5.7和v8.0都没有验证成功；
一直报以下错误，后续如有进展更新些处；

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE
BEGIN
    DECLARE result_table TABLE (
        id INT,
        name VARCHA' at line 2
1
2
3
4
5

参考文档：https://blog.51cto.com/u_16213348/8781024

二、查看存储函数

1、查看所有自定义函数

使用SHOW FUNCTION STATUS命令来查看所有自定义函数的信息：
SHOW FUNCTION STATUS;
1
2

2、使用WHERE子句过滤某个数据库中的自定义函数：

SHOW FUNCTION STATUS WHERE Db = 'database_name';
1

SHOW FUNCTION STATUS where Db="test"
1

3、使用LIKE子句过滤函数名包含某个关键字的自定义函数：

SHOW FUNCTION STATUS LIKE '%keyword%';
1

SHOW FUNCTION STATUS like "%nvl%"
1

三、修改存储函数

使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。
若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。
参考地址：https://deepinout.com/mysql/mysql-questions/t_how-can-we-alter-a-mysql-stored-function.html
修改存储函数失败，mysql版本5.7和8.0

-- 创建存储函数(成功)
-- CREATE FUNCTION test.get_total(username VARCHAR(20))
-- RETURNS DECIMAL(10,2) deterministic
-- BEGIN
--     DECLARE total DECIMAL(10,2);
--     SELECT SUM(score * 10) INTO total FROM user WHERE username = username;
--     RETURN total;
-- END;

-- 修改存储函数(失败)
ALTER FUNCTION get_total()
RETURNS DECIMAL(10,2) deterministic
BEGIN
    DECLARE total DECIMAL(10,2);
    SELECT SUM(score * 10) INTO total FROM user;
    RETURN total;
END;

/**
报错如下：
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()
RETURNS DECIMAL(10,2) deterministic
BEGIN
    DECLARE total DECIMAL(10,2)' at line 1
*/

-- 调用存储函数(成功)
-- SELECT test.get_total('mark');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

四、删除存储函数

语法格式如下：

DROP FUNCTION [ IF EXISTS ] <自定义函数名>
1

语法说明如下：
1、<自定义函数名>：指定要删除的自定义函数的名称。
2、IF EXISTS：指定关键字，用于防止因误删除不存在的自定义函数而引发错误。

drop function IF EXISTS 函数名;
1

五、查看存储函数结构

SHOW CREATE FUNCTION 函数名;

-- 查看创建nvl函数结构
SHOW CREATE FUNCTION nvl;
-- 返回: Create Function 字段内容如下：
CREATE DEFINER=`root`@`localhost` FUNCTION `nvl`(str1 varchar(4000), str2 varchar(4000)) RETURNS varchar(4000) CHARSET utf8mb4 DETERMINISTIC
return coalesce(str1, str2)
1
2
3
4
5