一、栗子
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 函数体
- 函数体可以由合法的SQL语句构成;
- 函数体可以是简单的SELECT或INSERT语句;
- 函数体如果为复合结构则使用BEGIN…END语句;
- 复合结构可以包含声明、循环、控制结构。