MySQL存储过程之函数

MySQL存储过程

为了便于MySQL代码维护，以及提高MySQL代码的重用性，MySQL开发人员经常将频繁使用的业务逻辑封装成存储程序 MySQL的存储程序分为四类：函数、触发器、存储过程以及事件。

MySQL编程基础知识

MySQL程序设计结构是在SQL标准的基础上增加了一些程序设计语言的元素，其中包括常量、变量、运算符、表达式、流程控制以及函数等内容。

常量

按照MySQL的数据类型进行划分，可以将常量划分为字符串常量、数值常量、十六进制常量、日期时间常量、二进制常量以及NULL。

• 字符串常量

字符串常量是指用单引号或双引号括起来的字符序列。

select 'I\'m a teacher' as col1, "you 're a student" as col2;

由于大多编程语言（例如Java、C等）使用双引号表示字符串，为了便于区分，在MySQL数据库中推荐使用单引号表示字符串。

• 数值常量

  数值常量可以分为整数常量（例如2013）和小数常量（例如5.26、101.5E5）

• 日期时间常量

  日期时间常量是一个符合特殊格式的字符串。例如’14:30:24’是一个时间常量，'2008-05-12 14:28:24’是一个日期时间常量。

• 布尔值

  布尔值只包含两个可能的值：true和false。
  说明：使用select语句显示布尔值true或者false时，会将其转换为字符串“0”或者字符串“1”。
  select true ,false;

• 二进制常量

二进制常量由数字“0”和“1”组成。二进制常量的表示方法：前缀为“b”，后面紧跟一个“二进制”字符串。例如下面的select语句输出三个字符。其中b‘111101’表示“等号”，b‘1’表示“笑脸”，b‘11’表示“心”。
select b'111101',b'1', b'11';

• 十六进制常量

  十六进制常量由数字“0”到“9”及字母“a”到“f”或“A”到“F”组成（字母不区分大小写）。十六进制常量有两种表示方法。

  • 第一种表示方法：前缀为大写字母“X”或小写字母“x”，后面紧跟一个“十六进制”字符串。
    例如：select X'41', x'4D7953514C';
    其中X’41’表示大写字母A。x’4D7953514C’表示字符串MySQL。

  • 第二种表示方法：前缀为“0x”，后面紧跟一个“十六进制数”（不用引号）。
    例如：select 0x41, 0x4D7953514C ;
    其中0x41表示大写字母A。0x4D7953514C表示字符串MySQL。

  如果需要将一个字符串或数字转换为十六进制格式的字符串，可以用hex()函数实现。
  例如：select hex('MySQL');
  hex()函数将“MySQL”字符串转换为十六进制数4D7953514C

• NULL值

  NULL值可适用于各种字段类型，它通常用来表示“值不确定”、“没有值”等意义，NULL值参与算术运算、比较运算以及逻辑运算时，结果依然为NULL。

变量

变量分为系统变量（以@@开头）以及用户自定义变量。
用户自定义变量分为用户会话变量（以@开头）以及局部变量（不以@开头） 。

用户会话变量

MySQL客户机1定义了会话变量，会话期间，该会话变量一直有效；
MySQL客户机2不能访问MySQL客户机1定义的会话变量；
MySQL客户机1关闭或者MySQL客户机1与服务器断开连接后，MySQL客户机1定义的所有会话变量将自动释放，以便节省MySQL服务器的内存空间。

（1）用户会话变量的定义与赋值

用户会话变量的定义与赋值有两种方法：使用set命令或者使用select语句。

• 方法一：使用set命令定义用户会话变量，并为其赋值，语法格式如下：
  set @user_variable1＝expression1 [,@user_variable2= expression2 , …]

  例：set @user_name='zs';，set @user_name=b'11',@age=18;

  说明：用户会话变量的数据类型是根据赋值运算符“=”右边表达式的计算结果自动分配的。也就是说，等号右边的值决定了用户会话变量的数据类型。

• 方法二：使用select语句定义用户会话变量，并为其赋值，语法格式有两种。

  • 第一种语法格式：
    select @user_variable1:＝expression1 [,user_variable2:= expression2 , …]

    例：select @user_name := 'longge';

  • 第二种语法格式：
    select expression1 into @user_variable1, expression2 into @user_variable2,…

    例：select 88 into @age;

  第一种与第二种语法格式的区别在于：第一种语法格式中的select语句会产生结果集，第二种语法格式中的select语句，仅仅用于会话变量的定义及赋值（但不会产生结果集）。

（2）用户会话变量与SQL语句

自定义函数在使用select语句时，该select语句不能产生结果集，否则将产生编译错误。

set @student_count =(select count(*) from student);
select @student_count;(不产生结果集)

select @student_count:=count(*) from student; （产生结果集）

select count(*) from student into @student_count;（不产生结果集）

（3）局部变量

declare命令专门用于定义局部变量及对应的数据类型。

局部变量必须定义在存储程序中（例如函数、触发器、存储过程以及事件中），并且局部变量的作用范围仅仅局限于存储程序中，脱离存储程序，局部变量没有丝毫意义。

局部变量主要用于下面三种场合：

• 场合一：局部变量定义在存储程序的begin-end语句块之间。此时局部变量首先必须使用declare命令定义，并且必须指定局部变量的数据类型。只有定义局部变量后，才可以使用set命令或者select语句为其赋值。

• 场合二：局部变量作为存储过程或者函数的参数使用，此时虽然不需要使用declare命令定义，但需要指定参数的数据类型。

• 场合三：局部变量也可以用在SQL语句中。数据检索时，如果select语句的结果集是单个值，可以将select语句的返回结果赋予局部变量，局部变量也可以直接嵌入到select、insert、update以及delete语句的条件表达式中。

运算符与表达式

根据运算符功能的不同，可将MySQL的运算符分为算术运算符、比较运算符、逻辑运算符以及位操作运算符。

（1）算术运算符

算术运算符用于两个操作数之间执行算术运算。常用的算术运算符有：+（加）、-（减）*（乘）、/（除）、%（求余）以及div（求商）等6种运算符。
set @num=15;
select @num+2,@num-2,@num*2,@num/2,@num%3,@num div 3;

（2）比较运算符

比较运算符（关系运算符）用于比较操作数之间的大小关系，其运算结果要么为true、要么为false、要么为NULL（不确定）。

（3）逻辑运算符

逻辑运算符（又称布尔运算符）对布尔值进行操作，其运算结果要么为true、要么为false、要么为NULL（不确定）。

（4）位运算符

位运算符对二进制数据进行操作（如果不是二进制类型的数，将进行类型自动转换），其运算结果为二进制数。使用select语句显示二进制数时，会将其自动转换为十进制数显示。

begin-end语句块

[开始标签:] begin
[局部]变量的声明;
错误触发条件的声明;
游标的声明;
错误处理程序的声明;
业务逻辑代码;
end[结束标签];

重置命令结束标记

delimiter $$  #将结束命令改为$$
select * from student where student_name like '张_'$$
delimiter ;  #将结束命令改为;
select * from student where student_name like '张_';

定义函数

创建自定义函数的语法格式

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

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

language sql：默认选项，用于说明函数体使用SQL语言编写。
deterministic（确定性）：当函数返回不确定值时，该选项是为了防止“复制”时的不一致性。如果函数总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“不确定”的。
例如函数返回系统当前的时间，返回值是不确定的。如果既没有给定deterministic也没有给定not deterministic，默认的就是not deterministic。
contains sql：表示函数体中不包含读或写数据的语句（例如set命令等）。
no sql：表示函数体中不包含SQL语句。
reads sql data：表示函数体中包含select查询语句，但不包含更新语句。
modifies sql data：表示函数体包含更新语句。如果上述选项没有明确指定，默认是contains sql。
sql security：用于指定函数的执行许可。
definer：表示该函数只能由创建者调用。
invoker：表示该函数可以被其他数据库用户调用。默认值是definer。
comment：为函数添加功能说明等注释信息。

函数的创建与调用

1、创建row_no_fn()函数，该函数实现功能是为查询结果集添加行号。

delimiter $$
create function row_no_fn2() returns int
no sql
begin
	set @row_no=@row_no+1;
    	return @row_no;
end;
$$
delimiter ;

set @row_no=0;   /*函数中创建的会话变量*/
select row_no_fn() 行号,student_no,student_name from student;

2、自定义函数操作数据库中的数据

delimiter $$
create function get_name_fn2(student_no1 int ) returns char(20)
reads sql data
begin
	declare student_name1 char(20);
    select student_name  from student where student_no =student_no1 into student_name1;
    return student_name1;
end;
$$
delimiter ;

定义函数后，执行函数。

select get_name_fn('2012001');

函数的维护

（1）查看函数的定义

• 查看当前数据库中所有的自定义函数信息，可以使用MySQL命令:
  show function status;
  如果自定义函数较多，使用MySQL命令show function status like 模式;可以进行模糊查询。
  show function status like "%fn%" ;
• 查看指定数据库（例如choose数据库）中的所有自定义函数名，可以使用下面的SQL语句
  select name from mysql.proc where db = 'choose' and type = 'function' ;
• 使用MySQL命令show create function 函数名;可以查看指定函数名的详细信息。
  例如查看get_name_fn()函数的详细信息，可以使用show create function get_name_fn;
• 函数的信息都保存在information_schema数据库中的routines表中，可以使用select语句检索routines表，查询函数的相关信息。
  select * from information_schema.routines where routine_name='get_name_fn'\G

（2）修改函数的定义

由于函数保存的仅仅是函数体，而函数体实际上是一些MySQL表达式，因此函数自身不保存任何用户数据。当函数的函数体需要更改时，可以使用drop function语句暂时将函数的定义删除，然后使用create function语句重新创建相同名字的函数即可。这种方法对于存储过程、视图、触发器的修改同样适用。
例如：drop function get_choose_number_fn;

（3）删除函数的定义

使用MySQL命令drop function函数名删除自定义函数。例如删除get_name_fn()函数可以使用
drop function get_name_fn;

条件控制语句

（1）if语句

if 条件表达式1 then 语句块1;
[elseif 条件表达式2  then语句块2] ...
[else语句块n]
end if;

delimiter $$
create function get_name_fn_role(no int,role char(20)) returns char(20)
	reads sql data
    begin
		declare name char(20);
        if('student'=role) then
			select student_name into name from student where student_no=no;
		elseif('teacher'=role) then 
			select teacher_name into name from teacher where teacher_no =no;
		else set name='输入有误';
        end if;
        return name;
	end;
$$
delimiter ;

（2）case语句

MySQL中的case语句与C语言、Java语言等高级程序设计语言不同，在高级程序设计语言中，每个case的分支需使用“break”跳出，而MySQL无需使用“break”语句。

case 表达式
when value1 then  语句块1;
when value2 then  语句块2;
…
else 语句块n;
end case;

delimiter $$
create function get_week_fn(week_no int ) returns char (20)
no sql
begin
	declare week char(20);
    case week_no
		when 0 then set week ='星期一';
        when 1 then set week ='星期二';
        when 2 then set week ='星期三';
        when 3 then set week ='星期四';
        when 4 then set week ='星期五';
        else set week ='今天休息';
    end case ;
    return week;
end
$$ 
delimiter ;

循环语句

MySQL提供了三种循环语句，分别是while、repeat以及loop。除此以外，MySQL还提供了iterate语句以及leave语句用于循环的内部控制。

（1）while语句

当条件表达式的值为true时，反复执行循环体，直到条件表达式的值为false，while语句的语法格式如下。

[循环标签:]while 条件表达式 do
循环体;
end while [循环标签];

delimiter $$
create function get_sum_fn(n int) returns int
no sql
begin
	declare sum int default 0;
    	declare start int default 0;
    	while start<n do
		set start=start+1;
        	set sum =sum+start;
	end while;
    return sum;
end;
$$
delimiter ;

（2）leave语句

leave语句用于跳出当前的循环语句（例如while语句），语法格式如下。

leave 循环标签;

delimiter $$
create function get_sum1_fn(n int) returns int
no sql
begin
	declare sum char(20) default 0;
    declare start int default 0;
    add_num:while true do
		set start=start+1;
        set sum =sum+start;
        if(start=n) then
			leave add_num;
        end if;
	end while add_num;
    return sum;
end;
$$
delimiter ;

（3）iterate语句

iterate语句用于跳出本次循环，继而进行下次循环。iterate语句的语法格式如下:

iterate 循环标签;

delimiter $$
create function get_sum2_fn(n int) returns int
no sql
begin
	declare sum char(20) default 0;
    declare start int default 0;
    add_num :while true do 
		set start = start +1;
		if (start = n) then
				leave add_num;
		end if;
        if(start%2=0) then
			set sum = sum+start;
		else
			iterate add_num;
		end if;			
	end while add_num;
       return sum;
end;
$$
delimiter ;

（4）repeat语句

当条件表达式的值为false时，反复执行循环，直到条件表达式的值为true，repeat语句的语法格式如下:

[循环标签:]repeat 
循环体;
until 条件表达式
end repeat [循环标签];

delimiter $$
create function get_sum3_fn(n int) returns int
	no sql
    begin
    declare sum char(20) default 0;
    declare start int default 0;
    repeat 
		set start = start+1;
        set sum = sum +start;
        until start = n
	end repeat;
    return sum;
end;
$$
delimiter ;

（5） loop语句

由于loop循环语句本身没有停止循环的语句，因此loop通常使用leave语句跳出loop循环，loop的语法格式如下：

[循环标签:] loop
循环体;
if 条件表达式 then 
leave [循环标签]; 
end if;
end loop;

delimiter $$
create function get_sum4_fn(n int) returns int
no sql
begin
	declare sum char(20) default 0;
    declare start int default 0;
    add_sum :loop
		set start = start +1;
        set sum = sum +start;
        if (start = n) then 
			leave add_sum;
		end if;
	end loop;
	return sum;
end;
$$
delimiter ;