7、自定义函数

7.1、自定义函数简介

用户自定义函数（user-defind function，UDF）是一种对 mysql 扩展的途径，其用法与内置函数相同。

两个必要条件：参数、返回值

  1. 参数与返回值之间没有必然的联系，有可能有两个参数，只有一个返回值（如power（）幂运算）。也有可能没有参数，有返回值（如version（）版本信息）。
  2. 函数可以返回任意类型的值，也可以接受这些类型的参数，可以没有参数但一定会有返回值。

如何自定义函数：

/*创建自定义函数语法*/
CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL（实数） | DECIMAL（小数）}            /*返回类型*/
routine_body   /*函数体*/

关于函数体

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

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

SET NAMES gbk;      /*设置编码方式，能完整显示中文，只改变当前数据库的显示编码方式，mysql服务器的默认编码方式不变*/

有些内置函数，每次需要的时候都要重新调用很是麻烦（如now（）函数）。而自定义函数可以简化一些步骤，减少代码量：

mysql> SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');    /*now（）函数每次使用都需要这样调用，很麻烦*/
+--------------------------------------------------+
| DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒') |
+--------------------------------------------------+
| 2018年04月04日 09点:15分:15秒                    |
+--------------------------------------------------+
1 row in set (0.01 sec)

/*将now（）函数定位为 f1（），下次直接调用 f1（）即可*/
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)

mysql> SELECT f1();            /*调用 f1（）*/
+-----------------------------+
| f1()                        |
+-----------------------------+
| 2018年04月04日 09:19分:53秒 |
+-----------------------------+
1 row in set (0.00 sec)

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

可以带有（0-1024个）参数：

/*删除自定义函数*/
DROP FUNCTION function_name
/*定义 f2（）函数，求平均值*/
mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
    -> RETURNS FLOAT(10,2) UNSIGNED   /*长度最长为10位，保留小数点后两位*/
    -> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT f2(10,15);  /*调用 f2（）*/
+-----------+
| f2(10,15) |
+-----------+
|     12.50 |
+-----------+
1 row in set (0.00 sec)

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

/*使用复合结构函数体的自定义函数给数据表插入两条记录*/
mysql> select * from tb_2;         /*数据表结构*/
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
+----+----------+
2 rows in set (0.00 sec)

mysql> DELIMITER //             /*使用它可以告诉python解释器，分号（；）不结束语句，两个斜杠结束（//）*/
mysql> CREATE FUNCTION adduser(username VARCHAR(20))   /*创建具有复合结构函数体的自定义函数*/
    -> RETURNS INT UNSIGNED          /*返回类型为整型*/
    -> BEGIN                  /*使用BEGIN...END语句*/
    -> INSERT tb_2(username) VALUES(username);   /*第一个username 为数据表中的字段，第二个为adduser函数的参数*/
    -> RETURN LAST_INSERT_ID();          /*返回插入记录的 id*/
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT adduser('Rose');
    -> //
+-----------------+
| adduser('Rose') |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.01 sec)

mysql> SELECT adduser('Bob');
    -> //
+----------------+
| adduser('Bob') |
+----------------+
|              4 |
+----------------+
1 row in set (0.01 sec)

mysql> DELIMITER ;             /*结束以两个斜杠为结束语句，以分号结束*/
mysql> SELECT * FROM tb_2;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | Rose     |
|  4 | Bob      |
+----+----------+
4 rows in set (0.00 sec)