MySQL 存储函数

 一、存储函数 vs. 存储过程

 特性  存储函数 (FUNCTION)  存储过程 (PROCEDURE)

返回值  必须通过 `RETURN` 语句返回一个值  可以通过 `OUT`/`INOUT` 参数返回零个或多个值，但没有直接返回值

核心用途  计算并返回一个结果  执行复杂的业务逻辑操作（如增删改、事务管理等）

调用方式  在 SQL 语句中直接调用（如 `SELECT`）  使用 `CALL` 语句调用

参数类型  参数默认为 `IN`（输入参数），不能有 `OUT`/`INOUT` 参数  参数可以是 `IN`, `OUT`, `INOUT`

二、创建存储函数 (CREATE FUNCTION)

基本语法：

CREATE FUNCTION function_name ([parameter_list])
RETURNS data_type
[characteristic ...]
routine_body

`function_name`: 存储函数的名称。名称应唯一，推荐使用 `func_` 前缀。

`parameter_list`: 参数列表。格式为：`[IN] param_name param_type`。因为参数只能是输入的，所以 `IN` 关键字通常可以省略。

       `param_name`: 参数名。

       `param_type`: 参数的数据类型（如 `INT`, `VARCHAR(255)`）。

`RETURNS data_type`: 必须声明函数返回值的数据类型。

`characteristic`: 函数的特性（可选），与存储过程相同。常用选项：

       `DETERMINISTIC`：表示函数是“确定性”的，即相同的输入总是产生相同的输出（如数学函数）。如果函数使用了`NOW()`等非确定性函数，则不应使用此选项。

       `NOT DETERMINISTIC`：表示函数是“非确定性”的（默认）。

       `COMMENT 'string'`：函数的注释说明。

`routine_body`: 函数体，由 `BEGIN ... END` 包裹的有效 SQL 语句集合。其中必须包含至少一个 `RETURN value` 语句。

三、创建示例与步骤

创建函数时，通常需要临时修改分隔符，以避免函数体中的 `;` 被误认为是整个 SQL 语句的结束。

1.  选择数据库:

    USE test;

2.  修改分隔符 (通常改为 `//` 或 `$$`):

    DELIMITER //

3.  创建函数:

    CREATE FUNCTION func_student(std_id INT)

    RETURNS VARCHAR(20)

    COMMENT '根据学生ID查询姓名'

    BEGIN

         声明一个变量来存储结果

        DECLARE student_name VARCHAR(20);

         查询并将结果赋值给变量

        SELECT name INTO student_name

        FROM tb_student

        WHERE id = std_id;

         返回结果

        RETURN(student_name);

    END //

4.  恢复分隔符:

    DELIMITER ;

示例 2：计算两个数的和（更简单的例子）

DELIMITER //
CREATE FUNCTION func_add(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a + b;
END //
DELIMITER ;

四、调用存储函数

存储函数可以在任何允许使用表达式的地方调用，最常用于 `SELECT` 语句：

 调用示例1的函数

SELECT func_student(1);

 调用示例2的函数

SELECT func_add(5, 3); -- 返回 8

 在查询中使用

SELECT id, name, func_add(score, 10) AS new_score FROM tb_student;

五、管理存储函数

1.  查看函数

       查看所有函数状态（模糊匹配）:

        SHOW FUNCTION STATUS LIKE 'func_%';

       查看某个函数的定义：

        SHOW CREATE FUNCTION func_student;

       从信息模式中查询：

        SELECT * FROM information_schema.ROUTINES

        WHERE ROUTINE_TYPE = 'FUNCTION' AND ROUTINE_NAME = 'func_student';

2.  修改函数

    使用 `ALTER FUNCTION` 语句主要用来修改注释、特性等元数据，不能修改函数体或参数。要修改这些，必须先 `DROP` 再 `CREATE`。

    ALTER FUNCTION func_student COMMENT 'This is a new comment';

3.  删除函数

    DROP FUNCTION IF EXISTS func_student;

    `IF EXISTS` 是可选的，用于防止因函数不存在而报错。

六、重要注意事项

1.  权限：创建存储函数需要 `CREATE ROUTINE` 权限。

2.  确定性：如果函数被标记为 `DETERMINISTIC` 但它实际上不是，可能会导致优化器做出错误的决策，结果不可预测。

3.  RETURN 类型：`RETURN` 语句返回的值必须与 `RETURNS` 子句声明的类型兼容，否则会被强制转换。

4.  函数体限制：存储函数中不能使用执行显式或隐式提交（`COMMIT`）或回滚（`ROLLBACK`）的 SQL 语句。它们通常也不允许修改数据库数据（即不允许有 `INSERT`, `UPDATE`, `DELETE` 等语句），主要用于计算。这与存储过程不同。