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)