MySQL学习笔记（十四）自定义函数

1. 用户自定义函数（User-Defined Function）是一种对MySQL扩展的途径，其用法与内置的函数相同。

2. UDF有两个必要条件：

(1) 参数：可以有多个，也可以没有

(2) 返回值：函数只能有一个返回值

3. 函数可以返回任意类型的值，同样也可以接收这些类型的整数。

4. 创建UDF

CREATE FUNCTION function_name RETURNS {STRING | INTEGER | REAL | DECIMAL} routine_body

其中，RETURNS指代返回类型，routine_body指代函数体。

在函数体中，有以下特性：

(1) 函数体由合法的SQL语句构成

(2) 函数体可以是简单的SELECT或INSERT语句

(3) 函数体如果为复合结构，则使用BEGIN ... END语句

(4) 复合结构可以包含声明、循环及控制结构

5. 创建不带参数的UDF

//创建一个函数，执行将当前日期时间按指定格式修改的功能

CREATE FUNCTION f1()

RETURNS VARCHAR(40) 

RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %h时%i分%s秒');

//调用函数查看结果

SELECT f1();

6. 创建带有参数的UDF

//创建一个函数，执行取平均的功能

CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)

RETURNS FLOAT(10,2) UNSIGNED

RETURN (num1 + num2) /2;

//调用该函数

SELECT f2(10,15);

7. 创建具有复合结构的UDF

//创建数据表tb1

CREATE TABLE tb1(id TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,

                                  username VARCHAR(30));

//向表中插入数据

INSERT INTO tb1(username) VALUES('John'),('Rose');

//修改语句输入的结束方式，默认为分号时书写复合结构是将会默认语句结束，从而无法书写多个语句

DELIMITER //

//创建函数

CREATE FUNCTION adduser(uname VARCHAR(20))

RETURNS INT UNSIGNED

BEGIN

INSERT INTO tb1(username) VALUES(uname);

RETURN LAST_INSERT_ID();

END

//将默认结束标志改回原来的以；结束

DELIMITER ;

//调用函数，查看执行结果

SELECT adduser('Jack');

SELECT * FROM tb1;

8. 删除函数

DROP FUNCTION [IF EXISTS] function_name;