MySQL函数与查询结果应用

函数（function）同样用于代码的封装，与存储过程不同的是，在存储过程中，数据的输入和输出都是由参数完成的，而函数应直接返回数据，即通过函数的调用直接获取结果。查询结果可能是一个值（标量值）、一列数据或是一个虚拟的二维表，根据不同的数据结构可以有不同的使用方式。本文将讨论相关应用。

函数

创建函数时使用create function语句，格式如下：

create function [if not exists] <函数名> (<参数列表>) 
returns <返回值类型> [deterministic]
begin
    <语句>
end

其中：

• <函数名>为函数的名称，可根据习惯或约定的格式命名。
• <参数列表>中，多个参数使用逗号分隔，每个参数由参数名和数据类型两部分组成。
• <返回值类型>是指函数返回数据的类型。
• 指定deterministic关键字时，说明函数会返回固定类型的数据，这样便于数据的处理。不指定此关键字时，默认为not deterministic，即返回数据类型不确定。
• <语句>则是函数的处理代码，其中应使用return语句返回处理结果。

下面的代码创建了fn_factor()函数，其功能是将数据乘以指定的系数，并返回计算结果。

use cdb_demo;

delimiter //
create function if not exists fn_factor(n double,f double)
returns double deterministic
begin
	return n*f;
end;
//
delimiter ;

本例，fn_factor()函数中，参数n指定数据，参数f指定系数，在函数中，使用return语句返回n乘以f的值。

接下来，我们使用t2表中的数据为例，将f5，f6，f7字段数据乘以1.2，可以模拟将百分制转换为满分为120分的对应分数。

use cdb_demo;

select f1,f2,f3,f4,
f5,round(fn_factor(f5,1.2),1) as f5a,
f6,round(fn_factor(f6,1.2),1) as f6a,
f7,round(fn_factor(f7,1.2),1) as f7a
from t2;

代码执行结果如下图所示。

查询结果

函数中，更复杂的程序逻辑，如程序流程控制等，可以参考前一篇文章内容。

删除函数时可以使用drop function语句，应用格式如下：

drop function [if exists] <函数名>;

查询自定义函数的定义时可以使用show语句，应用格式如下。

show create function <函数名>;

查询结果应用

当查询结果只有一个值时，可以将其保存到变量中进一步使用，如下面的代码可以计算t2表中f5字段的极差（也称为全距，计算方法是最大值减最小值）。

use cdb_demo;

select min(f5) into @v_min from t2;
set @v_max = (select max(f5) from t2);
set @v_range = @v_max - @v_min;
select @v_range;

本例，将查询结果赋予变量时分别使用了两种方法，求最小值时使用into关键字赋值给@v_min变量；求最大值时使用set语句赋值给@v_max变量；最后使用最大值减最小值计算出极差。当然，对于这种简单的计算也可以直接使用一条语句完成，如下面的代码。

use cdb_demo;

select max(f5)-min(f5) from t2;

当查询结果为一列数据时，也可以作为一个数列使用，比如，作为in条件的值，如下面的代码。

use cdb_demo;

select * from t3a 
where f1 in(select f1 from t3 where recid<3);

当查询结果为一条或多条记录时，可以作为一个虚拟表或数据集使用，比如，可以将数据保存到一个新表中，或者添加到一个已存在的表中。接下来，我们将触发器相关的t201、t202、t203表数据统一合并到t299表中。

下面的代码，首先通过合并（union）将t202和t201表数据合并到t299 表。

use cdb_demo;

create table t299
as
select recid,f1,f2,f3,f_ts,'UPDATE' as f_opt from t202
union
select recid,f1,f2,f3,null,'INSERT' from t201;

合并（union）数据时，会使用第一个查询结果的字段名，本例，首先从t202表中查询所有数据，注意，t202表比t201表多了f_ts字段；此外，还添加了f_opt字段，在t202表的记录中使用'UPDATE'值，即update触发器保存的记录。合并的第二个查询是t201表，其中，recid、f1、f2、f3字段都与t202表对应，f_ts列则使用空值（null），f_opt字段则使用'INSERT'值，表示为insert触发器保存的记录。

下面的代码会将t203表的数据追加到t299表中。

use cdb_demo;

insert into t299(recid,f1,f2,f3,f_ts,f_opt)
select recid,f1,f2,f3,f_ts,'DELETE'  from t203;

本例使用了insert into...select语句，将查询结果添加到指定的表中，并将f_opt字段数据指定为'DELETE'值，表示delete触发器保存的记录。这里，select查询结果的字段名并不要求与insert into中指定的字段名相同，数据会按字段顺序一一对应。

完成操作后，t299表的数据如下图所示。

查询结果

此外，对于t201、t202、t203表的数据合并（union）操作也可以一步完成，如下面的代码。

use cdb_demo;

create table t298
as
select recid,f1,f2,f3,f_ts,'UPDATE' as f_opt from t202
union
select recid,f1,f2,f3,null,'INSERT' from t201
union
select recid,f1,f2,f3,f_ts,'DELETE' from t203;

本例会将三个表的数据合并到t298表中，其内容与t299表相同。