MySQL学习笔记—自定义函数

共计 5055 个字符，预计需要花费 13 分钟才能阅读完成。

MySQL 学习笔记—自定义函数

---

注释语法：

MySQL 服务器支持 3 种注释风格：

• 从‘#’字符从行尾。

• 从‘–’序列到行尾。请注意‘–’(双破折号)注释风格要求第 2 个破折号后面至少跟一个空格符(例如空格、tab、换行符等等)。该语法与标准 SQL 注释语法稍有不同。

• 从 /序列到后面的/ 序列。结束序列不一定在同一行中，因此该语法允许注释跨越多行。

下面的例子显示了 3 种风格的注释：

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;

---

自定义用户变量:

• 可以先在用户变量中保存值然后在以后引用它；这样可以将值从一个语句传递到另一个语句。用户变量与连接有关。也就是说，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将自动释放。

• 用户变量的形式为 @var_name，其中变量名 var_name 可以由当前字符集的文字数字字符、‘.’、‘_’和‘$’组成。默认字符集是 cp1252 (Latin1)。可以用 mysqld 的–default-character-set 选项更改字符集。用户变量名对大小写不敏感。
  设置用户变量的一个途径是执行 SET 语句：

SET @var_name = expr [, @var_name = expr] ...

对于 SET，可以使用 = 或:= 作为分配符。分配给每个变量的 expr 可以为整数、实数、字符串或者 NULL 值。

也可以用语句 select 代替 SET 来为用户变量分配一个值。在这种情况下，分配符必须为:= 而不能用 =，因为在非 SET 语句中 = 被视为一个比较 操作符：

mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

---

自定义函数基本方式

• 1. 自定义函数
  用户自定义函数 (user-defined function, UDF) 是一种对 MySQL 扩展的途径, 其用法与内置函数相同。
  自定义函数的两个必要条件:

  • a. 参数
  • b. 返回值

  函数可以返回任意类型的值, 同样可以接受这些类型的参数；
  函数的参数与返回值之间，没有必然的联系。

• 2. 创建自定义函数
  CREATE FUNCTION function_name ([func_parameter[,…]])
  RETURNS {STRING|INTEGER|REAL|DECIMAL|…}
  routine_body – 函数体

  默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为 db_name.function_name。
  如果子程序名和内建的 SQL 函数名一样，定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。当你随后调用子程序的时候也要插入。
  RETURNS 字句只能对 FUNCTION 做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句

• 3. 关于函数体

  • 函数体由合法的 SQL 语法构成;
  • 函数体可以是简单的 SELECT 或 INSERT 语句;
  • 函数体如果为复合结构则使用 BEGIN…END 语句；
  • 复合结构可以包括声明，循环，控制结构。

---

例子

• 创建一个不带参数的自定义函数：
  该函数将系统的时间按照设定的格式返回

CREATE FUNCTION myTime()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y 年 %m 月 %d 日 %H 点 %i 分 %s 秒');

• 创建一个带有参数的自定义函数：
  该函数可计算两个传入参数的平均值

CREATE FUNCTION avg(num1 SMALLINT UNSIGNED ,num2 SMALLINT UNSIGNED)
RETURNS FLOAT(3,2) 
RETURN (num1+num2)/2;

---

BEGIN … END 复合语句

BEGIN … END 复合语句格式：

[begin_label:] BEGIN
    [statement_list]
END [end_label]

存储子程序可以使用 BEGIN … END 复合语句来包含多个语句。statement_list 代表一个或多个语句的列表。statement_list 之内每个语句都必须用分号（;）来结尾。

复合语句可以被标记。除非 begin_label 存在, 否则 end_label 不能被给出, 并且如果二者都存在, 他们必须是同样的。

使用多重语句需要客户端能发送包含语句定界符; 的查询字符串。这个符号在命令行客户端被用 delimiter 命令来处理。改变查询结尾定界符“;”（比如改变为“//”）使得; 可被用在子程序体中。
例如在 mysql 中执行以下命令：

delimiter //

则以后命令结尾的符号就变成“//”而不是“；”

以下例子创建了一个函数，用于向 tb1 表中插入数据（数据包括 username，age），并返回 age 最大的那一条数据：

• 先执行：

delimiter //

• 再输入函数：

create function addUser(username varchar(20),age tinyint(3) unsigned)
returns tinyint(3) unsigned
begin
insert tb1(username,age) values(username,age);
return (select max(age) from tb1);
end //

• 改回结尾定界符：

delimiter ;

• 执行函数：

select addUser('chenjs',20);

---

删除函数的语句

删除函数的语句

drop function function_name;

此处只需写上函数名即可，函数的参数可以不用写出来。
例如删除上面创建的函数 addUser(username varchar(20),age tinyint(3) unsigned)，可以直接用以下语句删除：

drop function addUser;

---

流控制构造

---

IF 语句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

IF 实现了一个基本的条件构造。如果 search_condition 求值为真，相应的 SQL 语句列表被执行。如果没有 search_condition 匹配，在 ELSE 子句里的语句列表被执行。statement_list 可以包括一个或多个语句。
请注意，也有一个 IF() 函数，它不同于这里描述的 IF 语句：
IF(expr1,expr2,expr3)
如果 expr1 是 TRUE (expr1 不等于 0 且 expr1 不等于 NULL)，则 IF()的返回值为 expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
例如：

返回 2，并将 2 赋给变量 @m。

---

CASE 语句

CASE 语句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

或:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

存储程序的 CASE 语句实现一个复杂的条件构造。如果 search_condition 求值为真，相应的 SQL 被执行。如果没有搜索条件匹配，在 ELSE 子句里的语句被执行。

• 例子
  该例子根据传入一个表示函数名称的字符串与一个待处理的数字，对出入的数字执行不同的操作（在此之前已将结尾定界符改为”//”）

create function caseTest(str varchar(5),num int)
returns int 
begin 
case str
when 'power' then set @result=power(num,2);
when 'ceil' then set @result=ceil(num);
when 'floor' then set @result=floor(num);
when 'round' then set @result=round(num);
else set @result=0;
end case;
return (select @result);
end //

测试截图如下：

---

LOOP 语句

LOOP 语句格式

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP 允许某特定语句或语句群的重复执行，实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出，退出通常伴随着一个 LEAVE 语句。

LOOP 语句可以被标注。除非 begin_label 存在，否则 end_label 不能被给出，并且如果两者都出现，它们必须是同样的。

• LEAVE 语句
  格式

LEAVE label

这个语句被用来退出任何被标注的流程控制构造。它和 BEGIN … END 或循环一起被使用。

• ITERATE 语句
  格式

ITERATE label

ITERATE 只可以出现在 LOOP, REPEAT, 和 WHILE 语句内。ITERATE 意思为：“再次循环。”

例如，其创建方式使用的是 create procedure 而不是 create function，这是因为 procedure 不需要 returns 与 return 字段，而 function 必须有 returns 与 return 字段：

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; 
    END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

---

REPEAT 语句

REPEAT 语句格式

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT 语句内的语句或语句群被重复，直至 search_condition 为真。

REPEAT 语句可以被标注。除非 begin_label 也存在，end_label 才能被用，如果两者都存在，它们必须是一样的。

使用 repeat 来实现上面的程序，程序如下：

create function doRepeat(p1 int)
returns int
begin
 repeat set p1 = p1 + 1;
 until p1>10 
 end repeat;
return p1;
end

测试截图:

---

WHILE 语句

WHILE 语句格式

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILE 语句内的语句或语句群被重复，直至 search_condition 为真。

WHILE 语句可以被标注。除非 begin_label 也存在，end_label 才能被用，如果两者都存在，它们必须是一样的。

例子：

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END

• DECLARE 仅被用在 BEGIN … END 复合语句里，并且必须在复合语句的开头，在任何其它语句之前。用于声明一个局部变量，该变量在函数外不可访问，如果想要访问必须将数值返回，此时应该用 create function 而不可以用 create procedure，因为只有 create function 才可以有返回值，函数更改如下：

CREATE function dowhile()
RETURNS int
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
RETURN v1;
END

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-10/136277.htm