用户自定义函数-MySQL数据库应用

函数可以看做是一个“加工作坊”，这个“加工作坊”接收“调用者”传递过来的“原料”（实际上是函数的参数），然后将这些“原料”“加工处理”成“产品”（实际上是函数的返回值），再把“产品”返回给“调用者”。

存储函数也是过程式对象，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。然而，它们也有一些区别：

·存储函数不能拥有输出参数，因为存储函数本身就是输出参数。

·不能用CALL语句来调用存储函数。

·存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。

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

函数选项由以下一种或几种选项组合而成。

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：为函数添加功能说明等注释信息。

简化的用户自定义函数格式：

create function 函数名（变量名 数据类型）

returns 返回值数据类型

begin

函数体

return 表达式

end

1.顺序结构

【例6.1】创建一个存储函数，返回某个学生的姓名。

DELIMITER ？

CREATE FUNCTION NAME_OF_STU（XH CHAR（6））

RETURNS CHAR（8）

BEGIN

RETURN（SELECT 姓名 FROM XSB WHERE 学号=XH）；

END？

DELIMITER；

存储函数创建完后可使用SELECT关键字调用。例如，调用上例中的存储函数，可以使用以下语句：

SELECT NAME_OF_STU（'081102'）；

若要删除存储函数，可以使用“DROP FUNCTION”语句。例如：

DROP FUNCTION NAME_OF_STU；

上例中“DELIMITER ？”的作用是修改mysql中命令语句结束标识符，其示例如图6.8所示。

图6.8 修改mysql中命令语句结束标识符

【例6.2】创建一个用户函数fam（），求任意两整数之和。

（1）创建存储函数。

create function fam（a int，b int）

returns int

begin

declare c int；

set c= a+b；

return c；

end

?

（2）函数的调用。

select 函数名（实参）

select fam（2，7）?

执行结果如图6.9所示。

图6.9 函数调用结果

【例6.3】创建一个用户函数sea（），求任意三角形的面积，S=0.5*a*h。

（1）创建用户函数。

create function sea（a decimal（8，2），h decimal（8，2））

returns decimal（8，2）

begin

declare s decimal（8，2）；

set s=1.0/2*a*h；

return s；

end

?

（2）调用函数：

select sea（5.685，3.62）?

执行结果如图6.10所示。

图6.10 函数调用的参数传递

【例6.4】函数中利用系统函数解决问题。执行如下代码：

create function sayHello（user_name varchar（10））

returns varchar（20）

begin

return concat（'hello，'，user_name）；

end

调用函数：

select sayHello（'陶贵平'）；

【例6.5】建立一自定义函数，能随机产生三字姓名。

create function sqname（）

returns char（3）

begin

declare first_name char（16）default '赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨'；

declare mid_name char（8）default '大小多少山石土田'；

declare last_name char（5）default '甲乙丙丁戊'；

declare full_name char（3）；

set full_name = concat（substring（first_name，floor（rand（）*16+1），1），substring（mid_name，floor（rand（）*8+1），1），substring（last_name，floor（rand（）*5+1），1））；

return full_name；

end?

select sqname（）?

执行结果如图6.11所示。

图6.11 随机产生三字姓名

【例6.6】随机产生四位验证码，验证码由大小写字母和阿拉伯数字组成。

create function yzm（）

returns char（4）

begin

declare first_name char（62） default

'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'；

declare mid_name char（62） default

'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'；

declare third_name char（62） default

'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'；

declare last_name char（62） default

'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'；

declare full_name char（4）；

set full_name = concat（substring（first_name，floor（rand（）*62+1），1），substring（mid_name，floor（rand（）*62+1），1），substring（third_name，floor（rand（）*62+1），1），substring（last_name，floor（rand（）*62+1），1））；

return full_name；

end

select yzm（）?

执行结果如图6.12所示。

图6.12 随机产生四位验证码

2.if条件控制语句

条件控制语句分为两种：一种是if语句；另一种是case语句。

if语句根据条件表达式的值确定执行不同的语句块。if语句的用法格式如下：

if 条件表达式1 then 语句块1；

[elseif 条件表达式2 then语句块2] ...

[else语句块n]

end if；

说明：end if后必须以“；”结束。

扫行原理：

if 条件1满足 then

执行语句1

else if 条件2 满足 then

执行语句2

...

else

上面的条件都不满足，

执行语句n

end if；

if条件控制流程如图6.13所示。

图6.13 if条件控制流程

【例6.7】利用时间函数和分支结构解决问题。执行如下代码段：

create function ffunc（）

returns varchar（20）

begin

if hour（now（））>=11 then

return '晚'；

else

return '早'；

end if；

end

调用函数：

select ffunc（）；

执行结果如图6.14所示。

图6.14 if条件控制执行结果

【例6.8】利用时间函数和if结构嵌套解决问题。执行如下代码：

create function func1（）(www.xing528.com)

returns varchar（20）

begin

if hour（now（））>=17 then

return '晚'；

else if hour（now（））>=9 then

return '中'；

else

return '早'；

end if；

end if；

end

调用函数：

select func1（）

【例6.9】利用系统函数和if嵌套解决问题。调试如下代码：

CREATE FUNCTION cutString（s VARCHAR（255），n INT）

RETURNS varchar（255）

BEGIN

IF（ISNULL（s））THEN RETURN ''；

ELSE IF CHAR_LENGTH（s）<n THEN RETURN s；

ELSE IF CHAR_LENGTH（S）=n THEN RETURN '相等'；

ELSE RETURN CONCAT（LEFT（s，n），'sgq123'）；

END IF；

END IF；

END IF；

END

调用函数：

SELECT cutString（'abcdefghijklmnopqrstuvwxyz'，5）；

执行结果如图6.15所示。

图6.15 if嵌套函数

3．case多分支语句

case语句用于实现比if语句分支更为复杂的条件判断。case语句的语法格式如下：

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

case 表达式

when value1 then 语句块1；

when value2 then 语句块2；

…

else 语句块n；

end case；

执行流程如图6.16所示。

图6.16 case多分支语句执行流程

【例6.10】利用多分支case语句与函数解决问题。执行如下代码：

CREATE FUNCTION grade_sw（score INT）RETURNS VARCHAR（30）

BEGIN

DECLARE consult INT；

DECLARE grade VARCHAR（30）；

IF（score >= 0）THEN

SET consult = score div 10；

CASE consult

WHEN 10 THEN

SET grade = 'A'；

WHEN 9 THEN

SET grade = 'A'；

WHEN 8 THEN

SET grade ='B'；

WHEN 7 THEN

SET grade = 'C'；

WHEN 6 THEN

SET grade ='D'；

WHEN 5 THEN

SET grade = 'E'；

WHEN 4 THEN

SET grade = 'E'；

WHEN 3 THEN

SET grade = 'E'；

WHEN 2 THEN

SET grade = 'E'；

WHEN 1 THEN

SET grade = 'E'；

WHEN 0 THEN

SET grade = 'E'；

ELSE

SET grade = 'Score is error!'；

END CASE；

ELSE SET grade = 'Score is error!'；

END IF；

return grade；

END

4.循环语句

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

（1）while语句。

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

[循环标签：]while 条件表达式 do

循环体；

end while [循环标签]；

说明：end while后必须以“；”结束，执行流程如图6.17所示。

图6.17 while语句执行流程

（2）leave语句。

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

leave 循环标签；

说明：leave 循环标签后必须以“；”结束。

（3）iterate语句。

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

iterate 循环标签；

说明：iterate循环标签后必须以“；”结束。

（4）repeat语句。

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

[循环标签：]repeat

循环体；

until 条件表达式

end repeat [循环标签]；

说明：end repeat后必须以“；”结束。

（5）loop语句。

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

[循环标签：] loop

循环体；

if 条件表达式 then

leave [循环标签]；

end if；

end loop；

说明：end loop后必须以“；”结束。

【例6.11】自定义函数中使用循环语句。用循环结构编写函数gsdl，求1+2+3+…+99+100的和。

end

?

select gsdl（100）?

调用结果如图6.18所示。

图6.18 用自定义函数求1+2+3+....+99+100的和

6.4.2 函数的维护

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

1．查看函数的定义

（1）查看当前数据库中所有的自定义函数信息，可以使用MySQL命令“show function status；”。如果自定义函数较多，使用MySQL命令“show function status like 模式；”可以进行模糊查询。

（2）查看指定数据库（例如choose数据库）中的所有自定义函数名，可以使用下面的SQL语句：

select name from mysql.proc where db = 'choose' and type = 'function'；

（3）使用MySQL命令“show create function 函数名；”可以查看指定函数名的详细信息。例如，查看get_name_fn（）函数的详细信息，可以使用“show create function get_name_fn（）”；

（4）函数的信息都保存在information_schema数据库中的routines表中，可以使用select语句检索routines表，查询函数的相关信息，如下：

select * from information_schema.routines where routine_name='get_name_fn'\G

2．函数定义的修改

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

修改存函数是指修改已经定义好的函数。MySQL中通过ALTER FUNCTION语句来修改存储函数，其语法形式如下：

ALTER FUNCTION sp_name [characteristic ...]

characteristic：

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

其中，sp_name参数表示存储过程或函数的名称；

characteristic参数指定存储函数的特性。

CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句；

NO SQL表示子程序中不包含SQL语句；

READS SQL DATA表示子程序中包含读数据的语句；

MODIFIES SQL DATA表示子程序中包含写数据的语句。

SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行。

DEFINER表示只有定义者自己才能够执行；

INVOKER表示调用者可以执行。

COMMENT 'string'是注释信息。

说明：① 修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。但是，这两个语句的结构是一样的，语句中的所有参数都是一样的。而且，它们与创建存储过程或函数的语句中的参数也是基本一样的。

② 修改存储过程和函数，只能修改它们的权限，目前MYSQL还不提供对已存在的存储过程和函数代码的修改。如果要修改，只能通过先DROP掉，然后重新建立新的存储过程和函数来实现。

下面修改存储函数name_from_employee的定义，将读写权限改为READS SQL DATA，并加上注释信息'FIND NAME'。代码执行如下：

3．自定义函数的删除

删除函数语法格式如下：

drop function if exists function_name；

使用MySQL命令“drop function函数名”删除自定义函数。例如，删除get_name_fn（）函数可以使用“drop function get_name_fn；”。若要删除存储函数，可以使用DROP FUNCTION语句，例如：

DROP FUNCTION NAME_OF_STU；