一、理解函数与存储过程

1、相同点

• 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。

2、区别

•  函数必须有返回值，而存储过程没有。

•  存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN。

• 对自定义函数进行调用不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。

3、使用场景

• 并发量少的情况下，很少使用存储过程。并发量高的情况下，为了提高效率，用存储过程比较多。
• 函数经常和存储过程搭配使用

二、自定义函数

在使用 MySQL 的过程中，MySQL 自带的函数可能完成不了我们的业务需求，这时候就需要自定义函数。自定义函数是一种与存储过程十分相似的过程式数据库对象。它与存储过程一样，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。

1、语法格式

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
  RETURNS <类型>
  <函数主体>

语法说明如下：

• <函数名>：指定自定义函数的名称。注意，自定义函数不能与存储过程具有相同的名称。
• <参数><类型>：用于指定自定义函数的参数。这里的参数只有名称和类型，不能指定关键字 IN、OUT 和 INOUT。
• RETURNS<类型>：用于声明自定义函数返回值的数据类型。其中，<类型>用于指定返回值的数据类型。
• <函数主体>：自定义函数的主体部分，也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用，包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外，自定义函数体还必须包含一个 RETURN<值> 语句，其中<值>用于指定自定义函数的返回值。在 RETURN VALUE 语句中包含 SELECT 语句时，SELECT 语句的返回结果只能是一行且只能有一列值。

2、示例

（1）无参自定义函数

-- 自定义无参数函数
DELIMITER $$ #使用DELIMITER是因为MySQL中默认是使用分号来结束一个命令的，函数体中一条命令写完时会用分号来结束，而MySQL会误以为函数体已经定义完成，因为需要定义一个新的标识符来标识一个命令的结束，因为就可以使用DELIMITER。
DROP FUNCTION IF EXISTS func_get $$
CREATE FUNCTION func_get()
    RETURNS VARCHAR(255)
    BEGIN
        RETURN 'hello world!';
    END $$
DELIMITER ;

-- 调用自定义函数
SELECT func_get();

（2）有参自定义函数

-- 自定义有参数函数
DELIMITER $$
DROP FUNCTION IF EXISTS func_add $$
CREATE FUNCTION func_add
(
    a INT,
    b INT
)
RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;#c就是局部变量
    SET c=a+b;
    RETURN c;
END $$
DELIMITER ;

-- 调用函数
SELECT func_add(1,2); 

3、自定义函数相关语法及变量

（1）变量声明

变量声明的语法如下：

DECLARE var_name[,...] type [DEFAULT value] 

要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。使用语序使用 set 和 select into语句为变量赋值。例如：

DECLARE c INT DEFAULT 0;#c就是局部变量

（2）IF条件语句

示例一：

-- 自定义有参数函数
DELIMITER $$
DROP FUNCTION IF EXISTS func_get $$
CREATE FUNCTION func_get
(
    cj INT
)
RETURNS VARCHAR(255)
BEGIN
    DECLARE text VARCHAR(255) DEFAULT '';    
    IF cj>90 THEN
        SET text='优秀';
    ELSEIF cj>60 THEN
        SET text='良好';
    ELSE SET text='不及格';
    END IF;
    RETURN text;
END $$
DELIMITER ;

-- 调用函数
SELECT func_get(70); 

示例二：if 里面的多条件 and or

-- 自定义有参数函数
DELIMITER $$
DROP FUNCTION IF EXISTS func_get $$
CREATE FUNCTION func_get
(
    num INT
)
RETURNS VARCHAR(255)
BEGIN
    DECLARE text VARCHAR(255) DEFAULT '';    
    IF num>10 AND num<20 THEN
        SET text='大于10小于20';
    ELSEIF num>20 OR num<10 THEN
        SET text='小于10或者大于20';    
    END IF;
    RETURN text;
END $$
DELIMITER ;

-- 调用函数
SELECT func_get(70); 

（3）CASE语句

-- 自定义有参数函数
DELIMITER $$
DROP FUNCTION IF EXISTS func_get $$
CREATE FUNCTION func_get
(
    cj INT
)
RETURNS VARCHAR(255)
BEGIN
    DECLARE text VARCHAR(255) DEFAULT '';    
    CASE 
            WHEN cj>10 AND cj<20 THEN
                    SET text='大于10小于20';
            WHEN cj>20 OR cj<10 THEN
                    SET text='小于10或者大于20'; 
            ELSE SET text='其他';
        END CASE;
    RETURN text;
END $$
DELIMITER ;

-- 调用函数
SELECT func_get(5); 

（4）循环语句

/*创建自定义函数,求指定数之间的和*/
 
delimiter $$ /*修改临时语句结束符*/
DROP FUNCTION IF EXISTS func_sum;
 
CREATE FUNCTION func_sum(num int) RETURNS INT
 
BEGIN
        /*定义条件变量，注意用@符号定义的变量是全局的，也就是说在外面也可以访问*/
        SET @flg = 1;    /*循环结束变量开关*/
        SET @res = 0;    /*用来保存最终结果*/
 
        WHILE @flg <= num DO    /*当条件满足时进入循环*/
 
        SET @res = @res + @flg; /*注意：mysql里没有+= -=这种概练*/
 
        SET @flg := @flg + 1;        /*只能用这种方式赋值*/
        /* 【备注】 := 符号的说明 在mysql中 := 才是真正意义上的赋值操作，把右边的值赋给左边的变量
            = 则只在一种情况下作为赋值用，
            就是在SET语句里面，如: SET id = 50 除此之外 = 则是作为比较操作符使用的
            当然我这里完全可以用 = 来进行赋值，但人家Mysq已经专门设计了一个专门的赋值符号 :=
            所以我只要是赋值操作都是使用 := 来完成，这样可避免出错
        */
        END WHILE;    /*结束循环*/
 
    RETURN @res;    /*返回结果*/
 
END 
/*语句结束*/
$$
/*将临时语句结束符改回成 ; */
delimiter ;
 
/*函数查看语句*/
SELECT func_sum(100);

4、修改自定义函数

修改命令规范：

ALTER FUNCTION func_name [characteristic ...]

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

5、删除自定义函数

DROP FUNCTION [IF EXISTS] <自定义函数名>

三、常用系统函数汇总

可以参考：MySQL函数大全