8 自定义函数
8.1 简介
自定义函数:用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
自定义函数的两个必要条件:(二者没有必然的联系)
(1)参数
(2)返回值
创建自定义函数:
CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body
RETURNS:返回类型
routine_body:函数体
函数体:
(1)函数体由合法的SQL语句构成;
(2)函数体可以是简单的SELECT或INSERT语句;
(3)函数体如果为符合结构则使用BEGIN…END语句;
(4)复合结构可以包含声明,循环,控制结构;
8.2 创建不带参数的自定义函数
(1)select now(),输出当前时间日期。
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-02-21 11:45:22 |
+---------------------+
1 row in set
(2)将其转换格式显示
mysql> select date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒') ;
+--------------------------------------------------+
| date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒') |
+--------------------------------------------------+
| 2016年02月21日 11点:45分:26秒 |
+--------------------------------------------------+
1 row in set
(3)如果每次都要像上面那样写就显得繁琐,这里编写一个函数:
mysql> CREATE FUNCTION f1()
-> RETURNS VARCHAR(30)
-> RETURN DATE_FORMAT(now(),'%Y年%m月%d日 %H点:%i分:%s秒');
Query OK, 0 rows affected
(4)select 自定义函数:
mysql> select f1();
+------------------------------+
| f1() |
+------------------------------+
| 2016年02月21日 12点:30分:27秒 |
+------------------------------+
1 row in set
8.3 创建带有参数的自定义函数
(1)创建示例
mysql> CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED)
-> RETURNS FLOAT(10,2) UNSIGNED
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected
(2)select 函数
mysql> select f2(10,15);
+-----------+
| f2(10,15) |
+-----------+
| 12.5 |
+-----------+
1 row in set
8.4 创建具有复合结构函数体的自定义函数
(1)更改结束符为//
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2016-02-21 12:46:07 |
+---------------------+
1 row in set
mysql> DELIMITER //
mysql> SELECT NOW()//
+---------------------+
| NOW() |
+---------------------+
| 2016-02-21 12:46:15 |
+---------------------+
1 row in set
(2)创建函数,向stu表中插入数据,并返回id
mysql> select * from stu//
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | jtzen9 | 100 |
| 2 | heiheihei | 60 |
| 3 | John | 70 |
| 4 | Nick | NULL |
| 5 | Mike | 90 |
+----+-----------+-------+
5 rows in set
mysql> CREATE FUNCTION addstu(username VARCHAR(20),userscore SMALLINT)
-> RETURNS INT UNSIGNED
-> BEGIN
-> INSERT stu(name,score) VALUES(username,userscore);
-> RETURN LAST_INSERT_ID();
-> END
-> //
Query OK, 0 rows affected
(3)select 函数,返回结果
mysql> DELIMITER ;
mysql> SELECT addstu('ZengJT',99);
+---------------------+
| addstu('ZengJT',99) |
+---------------------+
| 6 |
+---------------------+
1 row in set
mysql> select * from stu;
+----+-----------+-------+
| id | name | score |
+----+-----------+-------+
| 1 | jtzen9 | 100 |
| 2 | heiheihei | 60 |
| 3 | John | 70 |
| 4 | Nick | NULL |
| 5 | Mike | 90 |
| 6 | ZengJT | 99 |
+----+-----------+-------+
6 rows in set
8.5 删除函数
(1)语句:
DROP FUNCTION [IF EXISTS] function_name;
(2)示例:
mysql> DROP FUNCTION IF EXISTS addstu;
Query OK, 0 rows affected
mysql> SELECT addstu('ZengJT',99);
1305 - FUNCTION test.addstu does not exist
mysql>