35 SQL 函数

优质

小牛编辑

122浏览

2023-12-01

1. 定义

慕课解释：函数可以把经常使用的代码封装起来，以便需要的时候直接调用。

本小节，我们将一起学习 SQL 函数。

2. 前言

我们在学习编程语言时候，也会遇到函数，在 SQL 中也是如此，如果你熟悉 Python 或者其它语言，那么一定使用过print这个内置函数。SQL 为了给开发者提供便利，也提供了一系例的内置函数，它们大致可分为算术函数、字符串函数、日期函数、转换函数和聚合函数五大类。

SQL 除了提供好用的内置函数外，还可以通过 Create 指令来新建一个自定义函数。在这个小节中，我们会简单的介绍自定义函数的创建和使用，而内置函数我们将在后面的几个小节中详细介绍。

本小节测试数据如下，请先在数据库中执行：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

3. 语法

SQL 函数是一组 SQL 语句的封装，因此它的创建是颇为复杂的，大致的步骤可概括为如下几步：

通过CREATE FUNCTION [function_name]来声明一个函数。如：CREATE FUNCTION getUsername；
在函数名中通过变量声明函数参数，如getUsername(uid int)，并通过returns指定返回值类型，如 returns varchar，表示函数返回字符串类型；
通过BEGIN开始函数体，并通过END来结束函数体；
若在函数体内使用了变量，需通过DECLARE来声明变量，如 DECLARE uname varchar(20)；
在函数体内使用 SQL 语句得到结果，并通过RETURN指定返回值。

我们通过上述步骤新建一个 getUsername 函数，该函数通过用户 id 获取用户名。

CREATE FUNCTION getUsername(uid int) RETURNS varchar(20)
BEGIN
	DECLARE uname varchar(20);
	SELECT username FROM imooc_user WHERE id = uid INTO uname;
	RETURN uname;
END;

在 getUsername 函数体中，若要给 uname 这样的局部变量赋值，需要使用INTO关键字。函数创建完毕后，通过 Select 来调用，如SELECT getUsername(1)。

结果如下：

# SELECT getUsername(1);
+----------------+
| getUsername(1) |
+----------------+
| pedro          |
+----------------+

4. 实践

4.1 例1 获得最大年龄

请书写 SQL 语句，新建一个getOldestAge函数，该函数获得imooc_user中用户的最大年龄。

分析：

按照上面步骤新建函数 getOldestAge，且该函数无参，但返回类型为 int ，最大年龄我们可以先使用 Order By 对其排序，然后通过Limit 取第一位用户，则可得到最大的年龄。

语句：

整理可得语句如下：

CREATE FUNCTION getOldestAge() RETURNS int
BEGIN
	RETURN (SELECT age FROM imooc_user ORDER BY age DESC LIMIT 1);
END;

创建该函数后，使用 Select 来调用：

SELECT getOldestAge();

结果如下：

+----------------+
| getOldestAge() |
+----------------+
| 23             |
+----------------+

5. 小结

SQL 自定义函数是十分复杂的一环，难学且使用少，我们不推荐你在自定义函数上死磕。在这个小节里，我们没有介绍 PostgreSQL 自定义函数的使用，因为在 PostgreSQL 中，自定义函数更加复杂。在市面上，我们很少看到书籍和资料会花大量篇幅在自定义函数上，但是如果你感兴趣，我们可以给你推荐几个链接。

Postgresql之自定义函数

MySQL 自定函数

在查询的时候，尤其是在 Where 的条件中慎用函数，它会导致查询不走索引，从而拉低查询的速度。
自定义函数用的非常少，因为它的移植性很差，取得的效果也不明显。