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