MySQL自定义函数（CREATE FUNCTION）

原文链接：http://c.biancheng.net/view/2590.html
案例：本文主要描述MySQL自定义函数
一、创建并使用自定义函数
（1）语法格式如下：

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
  RETURNS <类型>
  <函数主体>

（2)语法说明如下：

1）<函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。
2）<参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。
3）RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。
4）<函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。
5）在 RETURN VALUE 语句中包含 SELECT 语句时，SELECT 语句的返回结果只能是一行且只能有一列值。
6）若要查看数据库中存在哪些自定义函数，可以使用 SHOW FUNCTION STATUS 语句；若要查看数据库中某个具体的自定义函数，可以使用 SHOW CREATE FUNCTION<函数名> 语句，其中<函数名>用于指定该自定义函数的名称。

(3)实例
【实例 1】创建存储函数，名称为 StuNameById，该函数返回 SELECT 语句的查询结果，数值类型为字符串类型，输入的 SQL 语句和执行结果如下所示。

mysql> CREATE FUNCTION StuNameById()
    -> RETURNS VARCHAR(45)
    -> RETURN
    -> (SELECT name FROM tb_students_info
    -> WHERE id=1);
Query OK， 0 rows affected (0.09 sec)

注意：当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为反斜杠是 MySQL 的转义字符。

成功创建自定义函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 调用用户自定义的函数，语法格式为：

SELECT <自定义函数名> ([<参数> [,...]])

【实例 2】调用自定义函数 StuNameById，查看函数的运行结果，如下所示。

mysql> SELECT StuNameById();
+---------------+
| StuNameById() |
+---------------+
| Dany          |
+---------------+
1 row in set (0.24 sec)

【实例 3】定义函数n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null

CREATE FUNCTION getNthHighestSalary(N INT) //参数这里不能加default
RETURNS INT //注意这里是 returns  有加s
BEGIN
  RETURN (
      # Write your MySQL query statement below.

select
 case when t2.num<1 then null else t2.Salary end
from(
    select
        count(1) num,
        Salary
    from (
        select 
            Salary,
            dense_rank() over(order by Salary desc) drp
        from Employee) t1
    where drp=N) t2
);
END

二、修改自定义函数
可以使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。
三、删除自定义函数
自定义函数被创建后，一直保存在数据库服务器上以供使用，直至被删除。删除自定义函数的方法与删除存储过程的方法基本一样，可以使用 DROP FUNCTION 语句来实现。
（1）语法格式如下：

DROP FUNCTION [ IF EXISTS ] <自定义函数名>

（2）语法说明如下。

1）<自定义函数名>：指定要删除的自定义函数的名称。
2）IF EXISTS：指定关键字，用于防止因误删除不存在的自定义函数而引发错误。

(3)实例
【实例 1】删除自定义函数 StuNameById，查看函数的运行结果，如下所示。

mysql> DROP FUNCTION StuNameById;
Query OK, 0 rows affected (0.09 sec)
mysql> SELECT StuNameById();
ERROR 1305 (42000): FUNCTION test_db.StuNameById does not exist