MySQL 存储函数[特殊字符] VS 存储过程[特殊字符]

1、存储函数😸

一、存储函数概述

存储函数是MySQL中一种特殊的存储程序，具有以下特点：

• 返回单个值：必须通过RETURN语句返回明确的结果
• SQL表达式使用：可以直接在SQL语句中调用
• 输入参数：只接受输入参数(隐式IN)，不支持OUT/INOUT参数
• 确定性🤔🤔🤔：可声明为DETERMINISTIC（相同输入总是返回相同结果）

二、存储函数语法结构

DELIMITER //  -- 修改分隔符(可选)CREATE FUNCTION 函数名(参数1 数据类型,参数2 数据类型,...
)
RETURNS 返回值数据类型
[特性列表]
BEGIN-- 声明部分(DECLARE)-- 执行部分(SQL语句)-- 控制结构(IF/CASE/LOOP等)RETURN 返回值;  -- 必须包含
END //DELIMITER ;  -- 恢复分隔符

三、参数与返回值

1. 输入参数（仅支持IN）

CREATE FUNCTION fn_calculate_tax(salary DECIMAL(10,2),tax_rate DECIMAL(5,2)
RETURNS DECIMAL(10,2)
BEGINRETURN salary * tax_rate / 100;
END

2. 返回值类型

支持所有MySQL数据类型：

-- 返回字符串
CREATE FUNCTION fn_full_name(first_name VARCHAR(50),last_name VARCHAR(50))
RETURNS VARCHAR(100)
BEGINRETURN CONCAT(first_name, ' ', last_name);
END-- 返回日期
CREATE FUNCTION fn_next_monday()
RETURNS DATE
BEGINRETURN DATE_ADD(CURDATE(), INTERVAL (9 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY);
END

四、变量与流程控制

1. 变量声明与使用

CREATE FUNCTION fn_age_category(age INT)
RETURNS VARCHAR(20)
BEGINDECLARE category VARCHAR(20);IF age < 18 THENSET category = '未成年';ELSEIF age BETWEEN 18 AND 60 THENSET category = '成年';ELSESET category = '老年';END IF;RETURN category;
END

2. 复杂逻辑示例

CREATE FUNCTION fn_inventory_status(quantity INT,threshold INT)
RETURNS VARCHAR(30)
BEGINDECLARE status VARCHAR(30);CASE WHEN quantity <= 0 THENSET status = '缺货';WHEN quantity < threshold THENSET status = '库存不足';ELSESET status = '库存充足';END CASE;RETURN status;
END

五、确定性函数

CREATE FUNCTION fn_discount_price(original_price DECIMAL(10,2),discount DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGINRETURN original_price * (1 - discount/100);
END

六、存储函数调用

1. 在SQL中直接调用

-- 基本调用
SELECT fn_calculate_tax(5000, 15) AS tax_amount;-- 与表数据结合使用
SELECT employee_id,salary,fn_calculate_tax(salary, 20) AS tax
FROM employees;-- 在WHERE条件中使用
SELECT *
FROM products
WHERE fn_inventory_status(quantity, 10) = '库存不足';

2. 在存储过程中调用

CREATE PROCEDURE sp_apply_raise(IN emp_id INT)
BEGINUPDATE employeesSET salary = salary * fn_get_raise_factor(department)WHERE id = emp_id;
END

七、管理存储函数

1. 查看函数

-- 查看所有函数
SHOW FUNCTION STATUS;-- 查看函数定义
SHOW CREATE FUNCTION fn_name;

2. 修改函数

-- MySQL需要先删除再重建
DROP FUNCTION IF EXISTS fn_name;
CREATE FUNCTION fn_name() ...

3. 删除函数

DROP FUNCTION [IF EXISTS] fn_name;

八、最佳实践

1. 命名规范：建议使用fn_前缀区分
2. 单一职责：每个函数只完成一个明确功能
3. 性能考虑：避免在频繁调用的函数中使用复杂查询
4. 错误处理：考虑使用DECLARE HANDLER处理可能错误
5. 注释：添加函数目的、参数和返回值说明

---

🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳🐳

2、存储过程😸

一、存储过程概述

存储过程是预编译的SQL语句集合，存储在数据库中，具有以下特点：

• 预编译执行：首次创建时编译，后续调用直接执行，效率高
• 减少网络流量：客户端只需发送调用命令，而非多条SQL语句
• 增强安全性：可限制用户对表的直接访问，通过过程间接操作
• 代码复用：一次创建，多次调用，便于维护

二、存储过程语法结构

DELIMITER //  -- 修改分隔符(可选)CREATE PROCEDURE 过程名([IN|OUT|INOUT] 参数名 数据类型,...
)
[特性列表]
BEGIN-- 声明部分(DECLARE)-- 执行部分(SQL语句)-- 控制结构(IF/CASE/LOOP等)
END //DELIMITER ;  -- 恢复分隔符

三、参数类型详解

1. IN参数(默认)

CREATE PROCEDURE sp_raise_salary(IN emp_id INT,IN raise_amount DECIMAL(10,2)
)
BEGINUPDATE employees SET salary = salary + raise_amountWHERE id = emp_id;
END

2. OUT参数

CREATE PROCEDURE sp_get_employee_stats(OUT max_salary DECIMAL(10,2),OUT min_salary DECIMAL(10,2),OUT avg_salary DECIMAL(10,2)
)
BEGINSELECT MAX(salary), MIN(salary), AVG(salary)INTO max_salary, min_salary, avg_salaryFROM employees;
END

3. INOUT参数

CREATE PROCEDURE sp_increment_counter(INOUT counter INT,IN increment INT
)
BEGINSET counter = counter + increment;
END

四、变量与流程控制

1. 变量声明与使用

CREATE PROCEDURE sp_calculate_bonus(IN emp_id INT)
BEGINDECLARE base_salary DECIMAL(10,2);DECLARE years_service INT;DECLARE bonus DECIMAL(10,2);SELECT salary, DATEDIFF(NOW(), hire_date)/365 INTO base_salary, years_serviceFROM employees WHERE id = emp_id;SET bonus = base_salary * 0.1 * years_service;UPDATE employees SET bonus = bonus WHERE id = emp_id;
END

2. 条件控制(IF/CASE)

CREATE PROCEDURE sp_get_employee_level(IN emp_id INT, OUT level VARCHAR(20))
BEGINDECLARE emp_salary DECIMAL(10,2);SELECT salary INTO emp_salary FROM employees WHERE id = emp_id;IF emp_salary > 10000 THENSET level = '高级';ELSEIF emp_salary > 5000 THENSET level = '中级';ELSESET level = '初级';END IF;
END

3. 循环控制(WHILE/REPEAT/LOOP)

CREATE PROCEDURE sp_generate_test_data(IN num_rows INT)
BEGINDECLARE i INT DEFAULT 1;WHILE i <= num_rows DOINSERT INTO test_table VALUES(i, CONCAT('Item-', i));SET i = i + 1;END WHILE;
END

五、错误处理

CREATE PROCEDURE sp_transfer_funds(IN from_acc INT,IN to_acc INT,IN amount DECIMAL(10,2),OUT status VARCHAR(50)
)
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SET status = '转账失败';END;START TRANSACTION;UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;COMMIT;SET status = '转账成功';
END

六、存储过程调用

1. 基本调用

-- 无参数
CALL sp_update_all_salaries();-- IN参数
CALL sp_raise_salary(101, 500.00);-- OUT参数
SET @max = 0, @min = 0, @avg = 0;
CALL sp_get_employee_stats(@max, @min, @avg);
SELECT @max, @min, @avg;-- INOUT参数
SET @counter = 10;
CALL sp_increment_counter(@counter, 5);
SELECT @counter;  -- 输出15

2. 在应用程序中调用

// Java示例
try (Connection conn = DriverManager.getConnection(url, user, password);CallableStatement stmt = conn.prepareCall("{call sp_raise_salary(?, ?)}")) {stmt.setInt(1, 101);       // 设置第一个IN参数stmt.setBigDecimal(2, new BigDecimal("500.00"));  // 设置第二个IN参数stmt.execute();
}

七、管理存储过程

1. 查看存储过程

-- 查看所有存储过程
SHOW PROCEDURE STATUS;-- 查看特定存储过程代码
SHOW CREATE PROCEDURE sp_name;

2. 修改存储过程

-- MySQL中需要先删除再重建
DROP PROCEDURE IF EXISTS sp_name;
CREATE PROCEDURE sp_name() ...

3. 删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name;

八、最佳实践

1. 命名规范：使用统一前缀如sp_或proc_
2. 参数验证：在过程开始验证输入参数有效性
3. 注释：添加详细注释说明功能和参数
4. 错误处理：为关键操作添加错误处理
5. 避免长事务：长时间运行的过程应考虑分批次处理

3、存储函数😸 VS 存储过程😸

🔥 对比表

特性	存储函数(FUNCTION)	存储过程(PROCEDURE)
创建关键字	CREATE FUNCTION	CREATE PROCEDURE
参数模式	仅输入参数（隐式IN，不可写IN关键字）	必须显式声明IN/OUT/INOUT
返回值	必须用RETURNS声明类型且用RETURN返回值	无RETURNS，通过OUT参数或结果集返回数据
调用方式	SELECT func_name()	CALL proc_name()
能否执行DML	通常只查询（非严格限制）	可执行所有SQL（INSERT/UPDATE/DELETE等）

---

✅ 存储函数的正确语法

CREATE FUNCTION 函数名(参数1 数据类型,  -- 只能这样写，不能加IN/OUT！参数2 数据类型   -- 多个参数用逗号分隔
) 
RETURNS 返回值数据类型  -- 必须声明返回类型
[DETERMINISTIC|NOT DETERMINISTIC]  -- 可选特性
BEGIN-- 函数体RETURN 值;  -- 必须有RETURN语句
END;

示例：计算年龄总和

CREATE FUNCTION sum_ages(dept VARCHAR(50)) 
RETURNS INT
BEGINDECLARE total INT;SELECT SUM(age) INTO total FROM Employee WHERE department = dept;RETURN total;
END;

调用方式

-- 直接调用
SELECT sum_ages('Sales') AS department_total_age;-- 与其他SQL语句结合使用
SELECT department,sum_ages(department) AS total_age
FROM Employee
GROUP BY department;

---

✅ 存储过程的正确语法

CREATE PROCEDURE 过程名([IN] 参数1 数据类型,   -- IN可省略（默认就是IN）OUT 参数2 数据类型,    -- 必须显式声明OUTINOUT 参数3 数据类型  -- 双向参数
)
BEGIN-- 过程体-- 可执行任意SQL，通过OUT参数返回值
END;

示例：获取最老员工信息

CREATE PROCEDURE get_oldest_employee(OUT emp_name VARCHAR(100),OUT emp_age INT
)
BEGINSELECT name, age INTO emp_name, emp_ageFROM Employee ORDER BY age DESC LIMIT 1;
END; 

调用方式

-- 第一步：声明用户变量接收OUT参数
SET @oldest_name = '';
SET @oldest_age = 0;-- 第二步：调用存储过程
CALL get_oldest_employee(@oldest_name, @oldest_age);-- 第三步：查看结果
SELECT @oldest_name AS oldest_employee_name, @oldest_age AS oldest_employee_age;--or 显示结果
SELECT CONCAT('最年长员工是: ', @name, ', 年龄: ', @age) AS result;

---

❌ 常见错误示范

1. 在函数中使用OUT参数：

   -- 错误！函数不允许OUT参数
   CREATE FUNCTION bad_example(OUT x INT)...
   

2. 在过程中省略OUT声明：

   -- 错误！参数默认是IN，不会输出值
   CREATE PROCEDURE bad_example(result INT)...
   

3. 混淆调用方式：

   -- 错误！函数不能用CALL
   CALL my_function();-- 错误！过程不能用SELECT
   SELECT my_procedure();
   

---

🌟 记忆口诀

函数如数学公式：输入→计算→返回单值
过程如操作脚本：输入/输出→执行多动作→无返回

4、答疑🤔

👆MySQL 存储函数的确定性(DETERMINISTIC)

确定性函数是指：对于相同的输入参数，函数总是返回完全相同的结果，不受外部因素影响。数学函数就是典型的确定性函数，例如：

-- 确定性函数示例
CREATE FUNCTION fn_square(x INT) 
RETURNS INT
DETERMINISTIC
BEGINRETURN x * x;  -- 输入3永远返回9
END

非确定性函数则可能对相同的输入返回不同结果，例如：

-- 非确定性函数示例
CREATE FUNCTION fn_random_discount() 
RETURNS DECIMAL(3,2)
NOT DETERMINISTIC
BEGINRETURN RAND() * 0.5;  -- 每次调用返回不同随机值
END

---

🤔为什么需要声明确定性？

1. 查询优化。MySQL优化器会对确定性函数进行特殊处理：对于相同参数，可能缓存函数结果；在索引优化时有不同的处理策略。
2. 复制环境要求。在主从复制环境中，非确定性函数可能导致数据不一致。
3. 函数索引限制。只有确定性函数才能用于创建函数索引。

✅ 确定性场景

• 数学计算（加减乘除、幂运算等）
• 字符串处理（拼接、截取、格式化等）
• 日期计算（基于固定公式的计算）
• 业务规则计算（固定折扣率、税率等）

❌ 非确定性场景

• 依赖当前时间（NOW(), CURDATE()）
• 使用随机数（RAND()）
• 调用外部服务（获取实时汇率、天气等）
• 查询数据库（除非查询的表数据绝对不变）

😀特殊注意事项

1. 默认行为
   如果不声明，MySQL默认认为函数是NOT DETERMINISTIC

2. 错误声明的后果
   如果错误地将非确定性函数声明为确定性：
   -可能导致查询结果错误
   -在复制环境中造成数据不一致
   -函数索引返回不正确结果

3. 时间函数的特殊处理

-- 虽然包含CURDATE()，但可以声明为DETERMINISTIC
-- 因为同一SQL语句中多次调用返回相同值
CREATE FUNCTION fn_is_birthday_today(birth_date DATE)
RETURNS BOOLEAN
DETERMINISTIC
BEGINRETURN MONTH(birth_date) = MONTH(CURDATE()) AND DAY(birth_date) = DAY(CURDATE());
END