一、栗子

1.1 封装一个查询时间的函数

Mysql中自带的查询时间的函数

mysql>SELECT NOW();
+———————+
| NOW() |
+———————+
| 2016-12-02 16:02:48 |
+———————+
1 row in set (0.00 sec)

这里，我们创建一个函数，强行用中文显示

CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

mysql> SELECT f1();
+——————————-+
| f1() |
+——————————-+
| 2016年12月02日 16点:19分:09秒 |
+——————————-+
1 row in set (0.00 sec)

解释：
CREATE FUNCTION f1() 表明创建了一个名为f1的函数。

RETURNS VARCHAR(30) 表明函数有 一个返回值，且返回值的类型是VARCHAR(30)

RETURN DATE_FORMAT(NOW(),’%Y年%m月%d日 %H点:%i分:%s秒’); 表明函数的返回体的内容

1.2创建一个求两个数的平局数的函数

CREATE FUNCTION f2(num1 INT,num2 INT)
RETURNS FLOAT(10,2)
RETURN (num1+num2)/2;

mysql> SELECT f2(10,5);
+———-+
| f2(10,5) |
+———-+
| 7.50 |
+———-+
1 row in set (0.09 sec)

解释：
CREATE FUNCTION f2(num1 INT,num2 INT) 表明创建了一个名为f2的函数，且该函数需要接收两个INT类型的参数。

RETURNS FLOAT(10,2) 表明函数有 一个返回值，且返回值的类型是FLOAT(10,2)

RETURN (num1+num2)/2; 表明函数的返回体的内容

1.3创建一个具有复合结构函数体的自定义函数

DELIMITER //
CREATE FUNCTION ADD_USER(p_id INT,username VARCHAR(10))
RETURNS INT UNSIGNED
BEGIN
INSERT user(p_id,username) VALUES(p_id,username);
RETURN LAST_INSERT_ID();
END
//

mysql> SELECT ADD_USER(1,”hehe”)//
+——————–+
| ADD_USER(1,”hehe”) |
+——————–+
| 1 |
+——————–+
1 row in set (0.09 sec)

解释：
DELIMITER // 表示将MySQL的结束符由 ; 改变为 //

CREATE FUNCTION ADD_USER(p_id INT,username VARCHAR(10)) 表示创建了一个名为ADD_USER的函数，并且该函数接收两参数。

RETURNS INT UNSIGNED 表明函数有 一个返回值，且返回值的类型是INT UNSIGNED 。

BEGIN 表明函数体为复合结构，用于定义开始的地方。

INSERT user(p_id,username) VALUES(p_id,username); 表明一条插入语句。

RETURN LAST_INSERT_ID(); 表明函数返回的结果是LAST_INSERT_ID()函数，该函数是MySQL自带的系统函数，用于返回最新插入数据的ID值。

END 表明函数体为复合结构，用于定义结尾的地方。

1.4写一个SQL查询从Employee表中获取第n高的薪水

表结构如下：

+—-+——–+
| Id | Salary |
+—-+——–+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+—-+——–+

假如，我们写n=2，返回结果 200.

DELIMITER //
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
        select distinct Salary from Employee order by Salary desc limit M, 1
    );
END
//

mysql> SELECT getNthHighestSalary(4)//
+————————+
| getNthHighestSalary(3) |
+————————+
| 100 |
+————————+
1 row in set (0.00 sec)

mysql> SELECT getNthHighestSalary(5)//
+————————+
| getNthHighestSalary(5) |
+————————+
| NULL |
+————————+
1 row in set (0.00 sec)

二、概念

2.1 修改MySQL中的结束符

将MySQL的结束符由 ; 改变为 //

DELIMITER //

改变之后，如果还想再用查询语句，需要这样

SELECT * FROM Employee //

2.2 自定义函数

创建自定义函数

CREATE FUNCTION function_name
RETURNS
{STRING|INTEGER|REAL|DECIMAL}
routine_body

自定义函数有两个必要条件：
1. 参数
2. 返回值

另外：函数可以返回任意类型的值，同样可以接收这些类型的参数。

2.3 函数体

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

四、Reference

1. 慕课网教程 http://www.imooc/video/2798