2.3 自定义函数与存储过程

在MySQL数据库开发中，自定义函数（UDF）是扩展数据库功能的重要手段。
存储过程：是一组预编译的SQL语句和控制流程语句，被命名并存储在数据库中。存储过程可以用来封装复杂的数据库操作逻辑，并在需要时进行调用。
类似的操作还有：sql文件导入等。

自定义函数

数据库本身提供了一些常用的函数，如时间函数，字符串函数，窗口函数等，但是往往在工作过程中需要根据业务和日常处理数据的需求进行定制化函数的使用，这类函数往往包含一定的业务逻辑，编写较为复杂。因此为了减少在每次编写sql语句时，对复杂的融合函数进行重复编译和减少出错的风险，我们可以将这类具有复杂业务逻辑的sql进行自定义处理，在后续取数过程中直接对函数进行调用，从而大大提升数据提取速度和准确性。

基础语法结构

DELIMITER // #修改分隔符以支持函数体内的分号
CREATE FUNCTION 函数名(参数列表) #定义输入参数及其数据类型
RETURNS 返回类型 #指定函数返回值的类型
[特性声明] #DETERMINISTIC：相同输入总产生相同输出；READS SQL DATA：函数体包含SELECT查询；MODIFIES SQL DATA：函数体包含INSERT/UPDATE/DELETE
BEGIN
    函数体
    RETURN 值;
END //
DELIMITER ;

1.1 计算两个数值加和的函数

-- 计算两数之和
CREATE FUNCTION add_numbers(a INT, b INT) #函数名add_numbers
RETURNS INT #返回int类型结果
DETERMINISTIC #相同输入总产生相同输出
BEGIN
    RETURN a + b;
END //

1.2计算事件日期和当前日期，天数差值的自定义函数

-- 计算距离事件的天数
CREATE FUNCTION days_until_event(event_date DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN DATEDIFF(event_date, CURDATE());
END //

1.3 数据查询函数，从部门ID直接获取部门雇员数量，

-- 查询部门员工数
CREATE FUNCTION get_employee_count(dept_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE emp_count INT;
    SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dept_id;
    RETURN emp_count;
END //

1.4 如何调用这些自定义函数

SELECT add_numbers(5, 3); -- 返回8
SELECT greet_user('John'); -- 返回"Hello, John!"
SELECT days_until_event('2025-12-31');
SELECT get_employee_count(1);

2、存储过程

MySQL 存储过程（Stored Procedure）是数据库中预先编写并编译的 SQL 语句集合，用户通过名称和参数调用执行。它存储在数据库中，一次编译后永久有效，能显著提升数据操作效率，支持变量定义、SQL 命令及流程控制（如条件分支和循环）。

2.1、存储过程的定义与特点
定义：存储过程是一组为完成特定功能而封装的 SQL 语句，经编译后存储在数据库服务器端。用户通过 CALL 语句执行，并可传递参数。
特点：
优点：代码复用、减少网络流量（仅传输调用指令）、增强安全性（通过权限控制）、提升性能（编译后直接执行）。
缺点：调试复杂、数据库移植性差（不同数据库语法差异大）、维护成本较高。
分类：分为基本存储过程（仅顺序执行 SQL）和复杂存储过程（含控制语句如 IF、LOOP）。
2.2、创建存储过程

创建存储过程使用 CREATE PROCEDURE 语句，需注意参数模式和语法结构：

基本语法：

DELIMITER $$  -- 更改分隔符，避免与语句内的分号冲突
CREATE PROCEDURE 存储过程名([参数列表])
BEGIN
    -- SQL 语句或控制逻辑
END $$
DELIMITER ;   -- 恢复默认分隔符

参数列表格式：[IN|OUT|INOUT] 参数名数据类型。
参数模式：
IN：输入参数（默认），调用时传入值，存储过程内部使用。
OUT：输出参数，存储过程通过它返回值。
INOUT：兼具输入和输出功能。
示例：
创建带 IN 参数的存储过程（查询员工信息）：

CREATE PROCEDURE pro_findById(IN eid INT)
BEGIN
    SELECT * FROM employee WHERE id = eid;
END $$

创建带 OUT 参数的存储过程（根据女生名返回男生名）：

CREATE PROCEDURE mypro4(IN girlname VARCHAR(20), OUT mingzi VARCHAR(20))
BEGIN
    SELECT b.boyname INTO mingzi  -- 将结果赋值给OUT参数
    FROM beauty g JOIN boys b ON g.boyfriend_id = b.id
    WHERE g.name = girlname;
END $$

2.3、调用存储过程

使用 CALL 语句执行存储过程，并处理参数：

调用语法：CALL 存储过程名(参数列表);。
示例：
调用 IN 参数过程：

CALL pro_findById(4);  -- 传入员工ID

调用 OUT 参数过程（MySQL 8.0+）：

CALL mypro4('小昭', @bName);  -- @bName 接收返回值
SELECT @bName;               -- 查看输出结果

调用 INOUT 参数过程（参数翻倍示例）：

CREATE PROCEDURE double_value(INOUT a INT, INOUT b INT)
BEGIN
    SET a = a * 2;
    SET b = b * 2;
END $$
-- 调用
SET @x = 5, @y = 10;
CALL double_value(@x, @y);
SELECT @x, @y;  -- 结果: 10, 20

2.4、管理存储过程
查看存储过程：
列出所有存储过程：

SELECT * FROM information_schema.routines WHERE routine_schema = '数据库名';

查看定义详情：

SHOW CREATE PROCEDURE 存储过程名

删除存储过程：

DROP PROCEDURE IF EXISTS 存储过程名

2.5、适用场景
存储过程适用于复杂业务逻辑（如事务处理、报表生成），但需权衡可维护性。

2.3 自定义函数与存储过程