26.MySQL之函数

如果想看更多有关于MySQL数据库的文章，请跳转到MySQL自学目录

前面讲述了一些系统函数的用法，比如聚集函数。这节的主要内容是自定义函数

自定义函数

自定义函数的语法格式

create function 函数名(参数1,参数2,…) returns 返回值的数据类型
[函数选项]
begin
函数体;
return 语句;
end;

说明：
- 自定义函数是数据库的对象，因此，创建自定义函数时，需要在指定的数据库下进行定义。
- 函数必须指定返回值的数据类型，且必须与return 语句中的返回值的数据类型相近（长度可以不同）。
- 括号可以为空。

1. 函数选项是由以下一种或几种选项组合而成：
   language sql
   [not] deterministic
   { contains sql | no sql | read sql data | modifies sql data}
   sql security {definer | invoker}
   comment ‘注释’
   选项说明：

   • language sql：默认选项，用于说明函数体使用SQL语言编写。
   • deterministic（确定性）：如果函数总是对同样输入参数产生同样的结果就认为是“确定的”，否则就是“不确定的”。默认为 not deterministic。
   • contains sql：表示函数体布博阿寒读或写数据的语句（如set命令）。
   • no sql：表示函数体不包含sql语句。
   • read sql data：表示函数体中包含select查询语句，但不包含更新语句。
   • modifies sql data：表示函数包含更新语句。默认是contains sql。
   • sql security：用于指定函数的执行许可。
   • definer：表示该函数只能由创建者调用。
   • invoker：表示该函数可以被其他数据库用户调用，默认值是definer。
   • comment：为函数添加注释功能。

2. 函数的创建与调用
   举个栗子

mysql> delimiter $  # 重置命令结束标记
mysql> # 创建一个test1函数
mysql> create function test1() returns int
    -> no sql
    -> begin
    ->  declare a int;
    ->  set a = 5;
    ->  return a;
    -> end$
Query OK, 0 rows affected (2.10 sec)

mysql> # 调用函数test1
mysql> select @t := test1() $
+---------------+
| @t := test1() |
+---------------+
|             5 |
+---------------+
1 row in set (0.17 sec)

1. 函数的维护

  函数的维护包括查看函数的定义、修改函数的定义、以及删除函数的定义等内容。

• 查看函数的定义：show function status;
• 查看指定函数的详细信息：show create function 函数名;
• 函数定义的修改。先将函数删除，在创建相同名称的函数即可。
• 函数定义的删除：drop function 函数名;

mysql> # 查看函数的定义
mysql> show function status\G;
*************************** 1. row ***************************
                  Db: test2
                Name: test1
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2018-07-20 19:29:36
             Created: 2018-07-20 19:29:36
       Security_type: DEFINER
             Comment:
character_set_client: gbk
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> # 查看函数的详细信息
mysql> show create function test1\G$
*************************** 1. row ***************************
            Function: test1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `test1`() RETURNS int(11)
    NO SQL
begin
        declare a int;
 set a = 5;
 return a;
end
character_set_client: gbk
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> drop function test1;
Query OK, 0 rows affected (0.01 sec)

1. 条件控制语句
   4.1 if语句
   语法格式：
   if 条件表达式1 then 语句块1;
   [else if 条件表达式2 then 语句块2]…
   [else 语句块n]
   end if;
   举个栗子

mysql> # if用法
mysql> create function iftest(a int,b int) returns int
    -> no sql
    -> begin
    ->  declare temp int;
    ->  if (a > b) then set temp = a;
    ->  elseif (a < b) then set temp = b;
    ->  else set temp = a;
    ->  end if;
    ->  return temp;
    -> end$
Query OK, 0 rows affected (0.13 sec)

mysql> select @max := iftest(5,3);
    -> $
+---------------------+
| @max := iftest(5,3) |
+---------------------+
|                   5 |
+---------------------+
1 row in set (0.11 sec)

4.2 case语句
语法格式：
case 表达式
when value1 then 语句块1;
when value1 then 语句块2;
when value1 then 语句块3;
…
else 语句块n ;
end case;

mysql> #case用法
mysql> create function casetest(week int) returns char(20)
    -> no sql
    -> begin
    ->  declare temp char(20);
    ->  case week
    ->   when 0 then set temp = '星期一';
    ->   when 1 then set temp = '星期二';
    ->   when 2 then set temp = '星期三';
    ->   when 3 then set temp = '星期四';
    ->   when 4 then set temp = '星期五';
    ->   else set temp = '休息日';
    ->  end case;
    ->  return temp;
    -> end$
Query OK, 0 rows affected (0.01 sec)

mysql> select @day := casetest(7)$
+----------------------+
| @day := casetest(7) |
+----------------------+
| 休息日               |
+----------------------+
1 row in set (0.01 sec)

4.3 where语句
语法格式：
[循环标签:] while 条件表达式 do
循环体;
end while [循环标签];

mysql> # where语句
mysql> create function wheretest(n int) returns int
    -> no sql
    -> begin
    ->  declare sum int default 0;
    ->  declare start int default 0;
    ->  while start <= n do
    ->   set sum = sum + start;
    ->   set start = start + 1;
    ->  end while;
    ->  return sum;
    -> end$
Query OK, 0 rows affected (2.09 sec)

mysql> select @sum := wheretest(100) $
+------------------------+
| @sum := wheretest(100) |
+------------------------+
|                   5050 |
+------------------------+
1 row in set (0.00 sec)