2018-05-03-7章-自定义函数

1 MySQL 自定义函数简介

• 自定义函数

  用户自定义函数(user-defined function ,UDF)是一种对MySQL扩展的途径

  其用法与内置函数相同

• 自定义函数的两个必要条件

  (1) 参数

  (2) 返回值

• 函数可以返回任意类型的值,同样可以接收这些类型的参数

关于函数体

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

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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-06-26 15:39:45 |
+---------------------+
1 row in set (0.01 sec)

mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
+--------------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒')       |
+--------------------------------------------------------+
| 2018年06月26日 15点:41分:02秒                          |
+--------------------------------------------------------+
1 row in set (0.07 sec)

--封装一下DATE_FORMAT

mysql> CREATE FUNCTION f1() RETURNS VARCHAR(30)
    -> RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
Query OK, 0 rows affected (0.01 sec)

--调用自定义的f1函数;

mysql> SELECT f1();
+-------------------------------------+
| f1()                                |
+-------------------------------------+
| 2018年06月26日 15点:53分:21秒       |
+-------------------------------------+
1 row in set (0.01 sec)

3 MySQL 创建带有参数的自定义函数

mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED
    -> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT f2();
ERROR 1318 (42000): Incorrect number of arguments for FUNCTION test.f2; expected 2, got 0
mysql> SELECT f2(10,15);
+-----------+
| f2(10,15) |
+-----------+
|     12.50 |
+-----------+
1 row in set (0.02 sec)

4 MySQL 创建具有符合结构函数体的自定义函数

mysql> DELIMITER //

mysql> CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> RETURN
    -> INSERT test(username) VALUES(username);
    -> LAST_INSERT_ID();
    -> //
ERROR 1064 (42000): 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 'test(username) VALUES(username);
LAST_INSERT_ID()' at line 4

--两条sql语句后成了复合结构，需要加BEGIN...END

mysql> CREATE FUNCTION adduser(username VARCHAR(20))
    -> RETURNS INT UNSIGNED
    -> BEGIN
    -> INSERT test(username) VALUES(username);                                                                             
    -> RETURN LAST_INSERT_ID();
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT adduser('Jack');
+-----------------+
| adduser('Jack') |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * from test;
+----------+------+
| username | u_id |
+----------+------+
| John     |    1 |
| tom      |    2 |
| Rose     |    3 |
| Jack     |    4 |
+----------+------+
4 rows in set (0.00 sec)

4.1 删除函数

• DROP FUNCTION [IF EXISTS] function_name