sql之自定义函数（4）_sql自定义函数

知识点：

1. 自定义函数定义
2. 创建、调用、删除自定义函数
3. 自定义函数的应用
4. 深入自定义函数
   1. 复合结构
   2. 局部变量的创建、赋值；全局变量的创建
   3. 流程控制语句：条件语句（if、case）、控制循序条件语句（leave跳出循环、iterate跳出当前循环）、循环语句（loop、repeat、while）
       

一.定义

用户自定义函数（user-defined function，UDF）是一种对MySQL扩展的途经，其用法与内置函数相同。 

 

二.创建、修改、删除自定义函数

1.创建自定义函数：无参自定义函数、有参自定义函数

创建UDF:
 
　　CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name type,...])
 
　　RETURNS {STRING|INTEGER|REAL}
 
　　runtime_body
 
简单来说就是:
 
　　CREATE FUNCTION 函数名称(参数列表)
 
　　RETURNS 返回值类型
 
　　函数体
解析：
returns后面的为返回值类型，return后面的为返回值。

自定义函数有两个必要条件： 

1. 参数 
2. 返回值：return后面的为返回值

函数可以返回任意类型的值，同样可以接收这些类型的参数。
函数的参数和返回值之间没有必然的内在的联系。

关于函数体

• 函数体由合法的sql语句构成；
• 函数体可以是简单的SELECT或INSERT语句；
• 函数体如果为复合结构（多个语句要执行）则使用BEGIN…END语句；
• 复合结构可以包含声明，循环，控制结构； 

注意：

1.如果在创建函数的时候遇到you *might* want to use the less safe log_bin_trust_function_creators variable

需要执行 ：SET GLOBAL log_bin_trust_function_creators = 1; 

2.在输入要执行的sql语句后，后面的分号为当前默认的分隔符，导致函数无法再继续输入。因此需要修改默认的分隔符。

需要执行：delimiter

$$#修改默认的结束符";"为"$$ ",以后的SQL语句都要以"$$"作为结尾

 

例1：创建无参自定义函数：返回当前时间的，年月日 时分秒。

第一种：
delimiter$$ #将默认的结束符";"修改为"$$"
 
create function f1() 
returns datetime
begin
return now();
end$$
 
delimiter;  #改回；
 
第二种：
delimiter$$
 
create function f1() 
returns datetime
return now()$$
 
delimiter;

例2：创建有参自定义函数：求两个数的平均值。

DELIMITER$$
 
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1+num2)/2$$
 
DELIMITER;

例3：创建具有复合结构函数体的自定义函数：创建一个函数向表中插入数据，并返回所插入数据的id。

DELIMITER$$
 
CREATE FUNCTION f4() 
RETURNS INT(10) UNSIGNED
BEGIN
INSERT INTO goods(goods_name,goods_num) VALUES('圆珠笔',10);
RETURN LAST_INSERT_ID();
END$$
 
DELIMITER;

2.调用自定义函数语法：(和使用内置函数一样，使用它）

SELECT function_name(parameter_value,...)；

3.删除自定义函数语法:

drop function [IF EXISTS] function_name；

三.自定义函数的应用

四.深入使用自定义函数

1.复合结构

参考例3

2.局部变量和全局变量

自定义函数中定义局部变量语法:

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];
 
简单来说就是:
 
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]；

这些变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义

示例:

 

DELIMITER $$
CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED) 
RETURNS SMALLINT
BEGIN
DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;
SET  a = x, b = y;
RETURN a+b;
END$$

 

上边的代码只是把两个数相加,当然,没有必要这么写,只是说明局部变量的用法,还是要说明下:这些局部变量的作用范围是在BEGIN...END程序中

变量赋值语法:

SET parameter_name = value[,parameter_name = value...]
 
SELECT INTO parameter_name
 
例: 
 
...在某个UDF中...
DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
RETURN x;
END$$

用户变量定义语法:(可以理解成全局变量)

SET @param_name = value
 
例：
SET @allParam = 100;
SELECT @allParam;

上述定义并显示@allParam用户变量,其作用域只为当前用户的客户端有效

3.自定义函数中流程控制语句语法:

存储过程和函数中可以使用流程控制来控制语句的执行。

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合语句，构造可以被嵌套。

1．IF语句

IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF 
 
解析：
其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

注意：MYSQL还有一个IF()函数，他不同于这里描述的IF语句

例题：

例1：根据输入的参数来判断age与18，20的关系来确定是不是成年人
DELIMITER$$
CREATE FUNCTION f1(age int(10))
RETURNS varchar(255)
BEGIN
IF age<18 THEN  return '未成年人';  
ELSEIF age=20 THEN return '20岁了';  
ELSE return '成年人';  
END IF;  #IF语句都需要使用END IF来结束。
END$$
DELIMITER;
 
->select f1(10);
结果为：未成年人
 
例2：根据输入的工资来涨工资
delimiter$$
create function f2(salary int(10))
returns int(10)
begin
declare aaa int(10) default 0; #同样可以使用局部变量和全局变量
if salary<=3000 then return salary+500; #工资不高于3000，涨500
elseif 3000<salary<=5000 then return salary+1000; #工资大于3000而不高于5000，涨1000
else return salary+2000;  #工资高于5000，涨2000
end if;
end$$
delimiter;

IF语句都需要使用END IF来结束。

IF函数：

语法：IF(value,t,f)函数
 
例：
select if（(age)<18,'未成年人','成年人'） from table_test;
解析：查询table_test表中的age列，如果age<18,则返回‘未成年人’，否认返回‘成年人’；

IFnull函数：

IFNULL(value1,value2)函数
 
例：select ifnull(salary,0) from table_test;
解析：查询table_test表中的salary列，如果为null,则返回0

2．CASE语句(有两种格式）

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下：

CASE case_value 
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE 
 
解析：
其中，case_value参数表示条件判断的变量；
when_value参数表示变量的取值；
statement_list参数表示不同when_value值的执行语句。
 
 
例：
delimiter$$
create function f1(damage int(10))
returns varchar(255)
begin
case damage
when 100 then return 'adc';
when 90 then return 'ad';
when 65 then return '坦克';
else return 'ap';
end case;  #CASE语句都要使用END CASE结束
end$$
delimiter;
 

CASE语句还有另一种形式。该形式的语法如下：

CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE 
 
解析：
其中，search_condition参数表示条件判断语句；
statement_list参数表示不同条件的执行语句。
 
例：
delimiter$$
create function f1(damage int(10))
returns varchar(255)
begin
case 
when damage=100 then return 'adc';
when damage=90 then return 'ad';
when damage=65 then return '坦克';
else return 'ap';
end case;   #CASE语句都要使用END CASE结束
end$$
delimiter;

下面是一个CASE语句的示例。代码如下：

CASE age 
WHEN 20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ; 

代码也可以是下面的形式：

CASE 
WHEN age=20 THEN SET @count1=@count1+1; 
ELSE SET @count2=@count2+1; 
END CASE ; 

本示例中，如果age值为20，count1的值加1；否则count2的值加1。CASE语句都要使用END CASE结束。

注意：这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句

并且用END CASE替代END来终止！！

case表达式：

第一种：
SELECT id,CASE 
WHEN damage<=60 THEN 'ap'
WHEN damage<80 THEN 'ad'
WHEN damage>80 THEN'adc'
ELSE 0
END
FROM test3;
 
第二种：
SELECT id,CASE damage 
WHEN 70 THEN 'ap'
WHEN 85 THEN 'ad'
WHEN 100 THEN'adc'
ELSE 0
END
FROM test3;

 3．LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下：

[begin_label:] LOOP 
statement_list 
END LOOP [end_label] 
 
解析：
其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；
 
statement_list参数表示需要循环执行的语句。

下面是一个LOOP语句的示例：从1加到无穷，因为没有条件结束循环。代码如下：

DELIMITER$$
CREATE FUNCTION f3() 
RETURNS INT(10)
BEGIN
    DECLARE x INT(10);
    declare y int(10);
    SET X=0;
    set y=1;
    -- 开始循环
    LOOP
    SET x=x+y;
    set y=y+1;
    END LOOP ;  -- 结束循环
    RETURN X;
END$$
DELIMITER;
 
结果：
会一直执行，因为没有结束循环的条件

该示例循环执行1到无穷的操作。因为没有跳出循环的语句，这个循环成了一个死循环。

LOOP循环都以END LOOP结束。

4．LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下：

LEAVE label 
解析：
其中，label参数表示循环的标志。

例1：从1加到99，代码如下

DELIMITER$$
CREATE FUNCTION f5() 
RETURNS INT(10)
BEGIN
    DECLARE X INT(10);
    DECLARE Y INT(10);
    SET X=0;
    SET Y=1;
    
    -- 开始循环
    myloop:LOOP
    SET X=X+Y;
    SET Y=Y+1; 
 
    IF Y=100 THEN LEAVE myloop;
    END IF;  --记得结束if语句
    END LOOP myloop;  -- 结束循环
    RETURN X;
END$$
DELIMITER;

5．iterate 语句

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下：

ITERATE label 
 
其中，label参数表示循环的标志。

例子：从1加到99，但是不加2，代码如下

DELIMITER$$
CREATE FUNCTION f5() 
RETURNS INT(10)
BEGIN
    DECLARE X INT(10);
    DECLARE Y INT(10);
    SET X=0;
    SET Y=1;
    
    -- 开始循环
    myloop:LOOP
    SET X=X+Y;
    SET Y=Y+1; 
    IF Y=2 THEN ITERATE myloop;
    END IF;   --如果是if语句，记得结束if语句
    IF Y=100 THEN LEAVE myloop;
    END IF;   --如果是if语句，记得结束if语句
    END LOOP myloop;  -- 结束循环
    RETURN X;
END$$
DELIMITER;

说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

使用这两个语句时一定要区分清楚。

6．repeat 语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label] 
 
其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

下面是一个REPEAT语句的示例。代码如下：

delimiter$$
create function f6()
returns int(10)
begin
    declare x int(10);
    declare y int(10);
    set x=0;
    set y=1;
    --开始循环
    repeat
    set x=x+y;  --不要忘记加set
    set y=y+1;
    until y=101
    end repeat;    --结束循环
    return x;    --不要忘记return
end$$
delimiter;

该示例循环执行从1加到100，y值为101时结束循环。

REPEAT循环都用END REPEAT结束。

7．WHILE语句

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时，执行循环内的语句。

WHILE语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label] 
 
其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；
statement_list参数表示循环的执行语句。

下面是一个ITERATE语句的示例。代码如下：

例1：
DELIMITER$$
CREATE FUNCTION f11() 
RETURNS INT(10)
BEGIN
    DECLARE X INT(10);
    SET X=1;
    -- 开始循环
    WHILE X<100 
    DO 
    SET X=X+1; 
    END WHILE ;  -- 结束循环
    RETURN X;
END$$
DELIMITER;
 
 
例2：
DELIMITER$$
CREATE FUNCTION f7(a INT) 
RETURNS VARCHAR (255)
BEGIN
    DECLARE X INT;
    DECLARE Y INT;
    SET Y = a;
    -- 开始循环
    WHILE Y < 10 DO 
        SET Y = Y + 1;
    END WHILE; -- 结束循环
    RETURN Y;
END$$
DELIMITER;
 
该示例循环执行count加1的操作，count值小于100时执行循环。
如果count值等于100了，则跳出循环。WHILE循环需要使用END WHILE来结束。