MySQL之FUNCTION函数

而我们今天介绍的函数（FUNCTION）是需要我们用户自己定义的，除了MySQL内置的函数外，我们想把处理某个问题的过程自己封装起来，也可以直接调用我们自定义的函数完成像MySQL内置函数的效果那样。今天要介绍的函数（FUNCTION）就是帮我们解决这个问题的利器。

函数

函数其实就是一种'函数'，只不过在这个函数里可以执行MySQL的语句而已。它可以像MySQL内置函数一样把处理某个问题的过程封装起来，之后我们直接调用函数就可以去解决这个问题了。MySQL中定义函数的语句如下：

CREATE FUNCTION 函数名称([参数列表])RETURNS 返回值类型BEGIN    函数体内容END

DELIMITER $CREATE FUNCTION avg_price(s VARCHAR(100))RETURNS DOUBLEBEGIN  RETURN (SELECT AVG(prod_price) FROM products WHERE prod_name REGEXP s);END $DELIMITER ;

我们定义了一个名叫avg_price的函数，它接收一个VARCHAR(100)类型的参数，声明的返回值类型是DOUBLE，需要注意的是，我们在RETURN语句后边写了一个SELECT语句，表明这个函数的返回结果就是根据这个查询语句产生的，也就是返回了某类商品的平均价格。

函数定义好了，下面我们来看看如何调用。我们自定义的函数和系统内置函数的使用方式是一样的，都是在函数名后加小括号()表示函数调用，调用有参数的函数时可以把参数写到小括号里边。函数调用可以放到查询列表或者作为搜索条件，或者和别的操作数一起组成更复杂的表达式，我们现在来调用一下刚刚写好的这个名为avg_price的函数吧：

先来看下products表中的数据：

SELECT avg_price('anvil');SELECT avg_price('JetPack');SELECT avg_price('TNT');

由此观之，通过调用函数的方式而不是直接写查询语句的方式来获取某类商品的平均价格看起来就简洁多了。

如果我们想查看我们已经定义了多少个函数，可以使用下边这个语句：

SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]

如果我们想查看某个函数的具体是怎么定义的，可以使用这个语句：

SHOW CREATE FUNCTION 函数名

虽然展示出很多内容，但是我们只要聚焦于名叫Create Function的那部分信息，该部分信息展示了这个函数的定义语句是什么样的。

如果想删除某个函数，使用这个语句：

DROP FUNCTION 函数名

到现在为止我们只是勾勒出一个函数的大致轮廓，下边我们来详细说一下MySQL定义函数体时支持的一些语句。

函数体的定义

函数中的函数体使用变量前必须先声明这个变量，声明方式如下：

DECLARE 变量名1, 变量名2, ... 数据类型 [DEFAULT 默认值];

这些在函数体内声明的变量只在该函数体内有用，当函数执行完成后，就不能访问到这些变量了，所以这些变量也被称为局部变量。我们可以在一条语句中声明多个相同数据类型的变量。

DELIMITER $$CREATE FUNCTION var_demo()RETURNS INTBEGIN  DECLARE c INT;  SET c = 5;  RETURN c;END $$DELIMITER ;

我们定义了一个名叫var_demo而且不需要参数的函数，然后在函数体中声明了一个名称为c的INT类型的局部变量，之后我们调用SET语句为这个局部变量赋值了整数5，并且把局部变量c当作函数结果返回。我们调用一下这个函数：

DELIMITER $$CREATE FUNCTION var_default_demo()RETURNS INTBEGIN  DECLARE c INT default 1;  RETURN c;END $$DELIMITER ;

在新创建的这个var_default_demo函数中，我们声明了一个局部变量c，并且指定了它的默认值为1，然后看一下该函数的调用结果：

DELIMITER $CREATE FUNCTION avg_price(s VARCHAR(100))RETURNS DOUBLEBEGIN  DECLARE a DOUBLE;  SET a = (SELECT AVG(prod_price) FROM products WHERE prod_name REGEXP s);  RETURN a;END $DELIMITER ;

我们先把一个查询语句的结果赋值给了局部变量a，然后再返回了这个变量。

除了局部变量外，也可以在函数体中使用自定义变量，比方说这样：

DELIMITER $$CREATE FUNCTION user_defined_var_demo()RETURNS INTBEGIN  SET @abc = 10;  RETURN @abc;END $$DELIMITER ;

我们定义了一个名叫user_defined_var_demo的函数，函数体内直接使用了自定义变量abc，我们来调用一下这个函数：

在定义函数的时候，可以指定多个参数，每个参数都要指定对应的数据类型，就像这样：

参数名 数据类型

比如我们上边编写的这个avg_price函数：

这个函数只需要一个类型为VARCHAR(100)参数，我们这里给这个参数起的名称是s，不过这个参数名不要和函数体语句中的其他变量名、列名啥的冲突，比如上边的例子中如果把变量名s改为为prod_name，它就与下边用到WHERE子句中的列名冲突了。

另外，函数参数不可以指定默认值，我们在调用函数的时候，必须显式的指定所有的参数，并且参数类型也一定要匹配，比方说我们在调用函数avg_price时，必须指定我们要查询的商品名，不然会报错的：

像其他的编程语言一样，在函数的函数体里也可以使用判断的语句，语法格式如下：

IF 表达式 THEN    处理语句列表[ELSEIF 表达式 THEN    处理语句列表]... # 这里可以有多个ELSEIF语句[ELSE    处理语句列表]END IF;

其中处理语句列表中可以包含多条语句，每条语句以分号;结尾就好。我们举一个包含IF语句的函数的例子：

‍‍‍‍‍‍‍‍‍DELIMITER $$CREATE FUNCTION condition_demo(i INT)RETURNS VARCHAR(10)BEGIN  DECLARE result VARCHAR(10);  IF i = 1 THEN      SET result = '结果是1';  ELSEIF i = 2 THEN      SET result = '结果是2';  ELSEIF i = 3 THEN      SET result = '结果是3';  ELSE      SET result = '非法参数';  END IF;  RETURN result;END $$DELIMITER ;‍‍‍‍‍‍‍‍‍

在我们定义的函数condition_demo中，它接收一个INT类型的参数，这个函数的处理逻辑如下：

1、如果这个参数的值是1，就把result变量的值设置为'结果是1'。

2、否则如果这个这个参数的值是2，就把result变量的值设置为'结果是2'。

3、否则如果这个这个参数的值是3，就把result变量的值设置为'结果是3'。

4、否则就把result变量的值设置为'非法参数'。

SELECT condition_demo(1);SELECT condition_demo(2);SELECT condition_demo(5);

除了判断语句，MySQL还支持循环语句的编写，不过提供了3种形式的循环语句，我们一一道来：

• WHILE循环语句：

WHILE 表达式 DO    处理语句列表END WHILE;

这个语句的意思是：如果满足给定的表达式，则执行处理语句，否则退出循环。比如我们想定义一个计算从1到n这n个数的和（假设n大于0）的函数，可以这么写：

DELIMITER $$CREATE FUNCTION sum_all(n INT UNSIGNED)RETURNS INTBEGIN  DECLARE result INT DEFAULT 0;  DECLARE i INT DEFAULT 1;  WHILE i <= n DO      SET result = result + i;      SET i = i + 1;  END WHILE;  RETURN result;END $$DELIMITER ;

在函数sum_all中，我们接收一个INT UNSIGNED类型的参数，声明了两个INT类型的变量i和result。我们先测试一下这个函数：

分析一下这个结果是怎么产生的，初始的情况下result的值默认是0，i的值默认是1，给定的参数n的值是3。这个函数的运行过程就是：

1、先判断i <= n是否成立，也就是1 <= 3是否成立，显然成立，然后执行处理语句，将result的值设置为1（result + i = 0 + 1），i的值设置为2（i + 1 = 1 + 1）。

2、再判断i <= n是否成立，也就是2 <= 3是否成立，显然成立，然后执行处理语句，将result的值设置为3（result + i = 1 + 2），i的值设置为3（i + 1 = 2 + 1）。

3、再判断i <= n是否成立，也就是3 <= 3是否成立，显然成立，然后执行处理语句，将result的值设置为6（result + i = 3 + 3），i的值设置为4（i + 1 = 3 + 1）。

4、再判断i <= n是否成立，也就是4 <= 3是否成立，显然不成立，退出循环。

所以最后返回的result的值就是6，也就是1、2、3这三个数的和。

• REPEAT循环语句：

REPEAT    处理语句列表UNTIL 表达式 END REPEAT;

先执行处理语句，再判断表达式是否成立，如果成立则退出循环，否则继续执行处理语句。与WHILE循环语句不同的一点是：WHILE循环语句先判断表达式的值，再执行处理语句，REPEAT循环语句先执行处理语句，再判断表达式的值，所以至少执行一次处理语句，所以如果sum_all函数用REPEAT循环改写，可以写成这样：

DELIMITER $$CREATE FUNCTION sum_all(n INT UNSIGNED)RETURNS INTBEGIN  DECLARE result INT DEFAULT 0;  DECLARE i INT DEFAULT 1;  REPEAT      SET result = result + i;      SET i = i + 1;  UNTIL i > n END REPEAT;  RETURN result;END $$DELIMITER ;

• LOOP循环语句：

这只是另一种形式的循环语句：

LOOP    处理语句列表END LOOP;

不过这种循环语句有一点比较奇特，它没有判断循环终止的条件？那这个循环语句怎么停止下来呢？其实可以把循环终止的条件写到处理语句列表中然后使用RETURN语句直接让函数结束就可以达到停止循环的效果，比方说我们可以这样改写sum_all函数：

DELIMITER $$CREATE FUNCTION sum_all(n INT UNSIGNED)RETURNS INTBEGIN  DECLARE result INT DEFAULT 0;  DECLARE i INT DEFAULT 1;  LOOP      IF i > n THEN          RETURN result;      END IF;      SET result = result + i;      SET i = i + 1;  END LOOP;END $$DELIMITER ;

如果我们仅仅想结束循环，而不是使用RETURN语句直接将函数返回，那么可以使用LEAVE语句。不过使用LEAVE时，需要先在LOOP语句前边放置一个所谓的标记，比方说我们使用LEAVE语句再改写sum_all函数：

DELIMITER $$CREATE FUNCTION sum_all(n INT UNSIGNED)RETURNS INTBEGIN    DECLARE result INT DEFAULT 0;    DECLARE i INT DEFAULT 1;    flag:LOOP        IF i > n THEN            LEAVE flag;        END IF;        SET result = result + i;        SET i = i + 1;    END LOOP flag;    RETURN result;END $$DELIMITER ;

可以看到，我们在LOOP语句前加了一个flag:，相当于为这个循环打了一个名叫flag的标记，然后在对应的END LOOP语句后边也把这个标记名flag给写上了。在函数的函数体中使用LEAVE flag语句来结束flag这个标记所代表的循环。

小结

end