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;