MySQL 中函数分为两类:系统函数和自定义函数。
【1】系统函数
系统定义好的函数,直接调用即可。任何函数都有返回值,而存储过程无返回值(可以用inout或out型参数带值出来)。
点击查看常用函数(字符串函数)
(http://blog.csdn.net/j080624/article/details/71078067)。
字符串长度示例如下:
length表示字节长度,与charset有关;
char_length表示字符长度,不区分中英,与charset无关;
select LENGTH("中国"),length("zhongguo"),CHAR_LENGTH("中国"),CHAR_LENGTH("zhongguo");
【2】自定义函数
- 函数,是有返回值;
- 存储过程无返回值;
- 函数与存储过程都是属于数据库;
- 触发器属于表;
- java中,声明变量[数据类型 变量名] ; MySQL中,声明变量[变量名 数据类型]
【创建语法】
create function 函数名([形参列表]) returns 数据类型 begin -- 函数体; --返回值:return 类型(指定的数据类型); end
示例如下:
CREATE FUNCTION fc1(v_age int(11)) returns int(11) BEGIN DECLARE p_count int(11); set p_count := 0; SELECT COUNT(*) into p_count from p_user where age >v_age; RETURN p_count; END
【函数参数】
和所有程序语言中函数的参数定义方式相似。定义时的参数叫形参,调用时的参数叫实参(MySQL中函数的实参可以是具体指也可以是变量)。
形参必须指定数据类型:参数名,数据类型。
Function 函数名(形参名字,数据类型) returns 数据类型。
【调用函数】
select fc1(10);
【查看函数】
show function status [like pattern];
【查看函数创建语句】
show create function function_name;
示例如下:
show create function fc1;
【删除函数】
DROP FUNCTION function_name;
示例如下:
DROP FUNCTION fc1
【函数与变量】
函数(存储过程)中,可以使用自定义变量(用户变量)。
示例如下:
create function addLoop(v_int int) returns int begin set @i := 1; set @res := 0; while @i <= v_int do set @res := @res + @i; set @i := @i +1; end while; return @res; end
调用如下:
select addLoop(10),@i,@res; -- 第一次执行时,@i @res 为null;需要先执行函数,再查看变量值。
说明如下:存储过程或函数中,对用户变量(在函数内部定义)的操作是滞后的。是在存储过程或者函数调用结束后,才会重新将内部修改的值赋值给外部传入的用户变量。
如果测试环境下,用户变量在外部定义传入函数,则第一次同函数一起查询时,显示其值。函数内部定义的用户变量为null。
set @a:=11; select addLoop2(@a),@a,@res; select @a,@res;
再次执行查看用户变量操作时,显示其正确值:
这里需要注意用户变量的几个关键词:
会话级别:即当前会话有效,关闭链接失效;与系统变量相对应。
全局变量:在函数外部可以查看;与局部变量对应。
故,谨慎使用用户变量,可以在测试时候使用用户变量监控。
那么什么是局部变量呢?look down…
【函数作用域】
MySQL中的作用域与js中作用域完全一样。
全局变量可以在任何地方使用,局部变量只能在函数内部使用。
全局变量:用户变量,set关键字定义赋值,@符号形式。
局部变量:declare声明,无@符号形式,同样可以使用set关键字赋值。
- 所有的局部变量必须在函数体开始之前声明。
- 无论局部还是全局变量,赋值形式都相同,大体分为两类
-- 第一种 set variable := 具体值或者表达式; -- 第二种 select expr into variable from ...; or select expr from ... into variable;
这里:=
说明一下,在MySQL中,无 ==
比较符号,故=
既可用于赋值亦可用于比较。在存储过程或者函数中,为了显示分辨赋值和比较,可使用:=
作为赋值明确符号。
当然,你同样可以使用=
用于赋值和比较,MySQL会自动鉴别。
全局变量和局部变量示例如下:
create function addLoop2(v_int int) returns int begin declare i int default 1; set @res := 0; while i <=v_int do set @res := @res + i; set i = i +1; end while; return @res; end
测试如下 :
select addLoop2(10),@res; 不可在函数外部查看declare声明的局部变量。
【函数中迭代循环】
循环在函数中是常见结构,但有时候难免遇到continue情况。MySQL中并没有continue,break关键字。
这是,就需要用到 iterate,leave实现continue,break功能。
示例如下:
create function addLoop2(v_int int) returns int begin declare i int default 1; set @res := 0; mywhile:while i <=v_int do -- 当i 为5的倍数时,跳出该次循环继续下一个循环 IF i % 5 = 0 THEN set i := i+1; ITERATE mywhile; END IF; set @res := @res + i; set i := i +1; end while; return @res; end
测试结果如下图所示:
上面示例演示的为continue功能,下面演示leave功能:
DROP FUNCTION IF EXISTS addLoop2 ; create function addLoop2(v_int int) returns int begin declare i int default 1; set @res := 0; mywhile:while i <=v_int do IF i = 10 THEN LEAVE mywhile; END IF; set @res := @res + i; set i := i +1; end while [mywhile]; return @res; end
测试结果如下图所示: