7.3.2 自定义函数

学习目标：

1.理解函数应用场景

2.掌握函数的定义方法

3.能调用函数解决应用问题

学习导航：

一、问题导入：

1. 函数有何作用？与存储过程有何不同？

   提示：可以尝试用Navicat向导来创建函数，预览SQL代码。

2. 如果需要从一个字符串中提取数值数据，如何处理？

   例如：population = ‘14亿’，email=’337659334@qq.com’, address=’咸宁大道118号’。如何获取到数据14，337659334，118呢？

二、新知学习：

1.常用的字符函数

• (1)合并字符串函数CONCAT().
• 注意：当传入concat函数中的参数有一个值为NULL时，返回的结果值将为NULL。

 select concat('My','S','QL'); -- 合并后的字符串MySQL;

(2)获取字符串长度函数LENGTH()，按字节数来算的,并非字符个数。

 select length('canvas') 计算英文长度,length('星期以') 计算中文长度;

结果：

（3）转换数据类型CAST()

SELECT CAST('2023-12-10' AS date);   -- 日期数据 2023-12-10
select cast('12'  as  unsigned)+5;   -- 算术运算值 17

（4）查找数据在序列中出现的位置，没找到返回0.. FIND_IN_SET( )

SELECT FIND_IN_SET(1, '0,1,2,3,4,5,6,7,8,9');  -- 找到了，值2
SELECT FIND_IN_SET(-1, '0,1,2,3,4,5,6,7,8,9');  -- 没找到，值0

（5）截取指定位置和长度的子字符串。SUBSTR( )

select substr('One of the best database is MySQL',12,4); --   best
select substr('MySQL',10,4);    -- 无数据可取，空串

三、解决问题

（1）提取字符串中以数值数据开头的数据，如：“337659334@qq.com”中的QQ号337659334.

set @email = '337659334@qq.com';
select cast(@email as unsigned) qq ;  -- qq值 33759334

或者：

set @email = '337659334@qq.com';
select -(-@email) qq ;  -- qq值 33759334

（2）提取字符串中任意位置开始的数值数据。如：address = ‘咸宁大道118号’

DROP FUNCTION IF EXISTS ExtractNumber;
CREATE FUNCTION ExtractNumber(in_string VARCHAR(50)) 
RETURNS int
BEGIN
    DECLARE isNumber int;     -- 判断一个字符是否是数字，1表示是，0表示否
    DECLARE findNumber VARCHAR(50) DEFAULT '';  -- 数字字符拼接，初始值为空串
    DECLARE sChar VARCHAR(1);                  -- 遍历字符串，当前字符
    DECLARE pos INTEGER DEFAULT 1;            -- 循环控制，当前字符位置
    IF LENGTH(in_string) > 0 THEN
        WHILE(pos <= LENGTH(in_string)) DO
            SET sChar = SUBSTRING(in_string, pos, 1);  -- 取pos位置的一个字符
            SET isNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9');   -- find_in_set 搜索当前字符在序列中的位置，找不到是0
            IF isNumber > 0 THEN        -- isNumger大于0，是数字
                SET findNumber = CONCAT(findNumber, sChar);   -- 拼接成数字子串
            END IF;
            SET pos = pos + 1;
        END WHILE;
        RETURN CAST(findNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END;

调用函数：SELECT ExtractNumber(‘咸宁大道118号’) streetCode;

结果：

（3）练习：

访问meitao数据库，创建自定义函数fun_amount_byName，根据用户姓名，汇总用户的订单金额。

四、课堂小结：