📚 SQL 自定义函数与存储过程知识点整理

一、概述

自定义函数和存储过程的作用：

• 补充系统函数的不足
• 封装业务逻辑
• 提高代码复用性和可维护性

核心区别：

• 自定义函数：返回单个值，用于简单计算和数据转换
• 存储过程：返回完整结果集，用于复杂业务逻辑

---

二、自定义函数（User-Defined Function）

基础语法

CREATE FUNCTION 函数名(参数1 类型1, 参数2 类型2...)
RETURNS 返回类型
BEGIN
  -- 函数体：编辑您的逻辑
  DECLARE 变量 类型;
  -- 其他SQL语句或逻辑
  SET 变量名 = ...;
  RETURN 某个值;  -- 返回一个结果
END;

语法要素说明

要素	说明
CREATE FUNCTION	创建函数命令
函数名	自定义函数的名称
参数	输入参数，可以是多个
RETURNS	返回值类型
DECLARE	声明函数内的局部变量
SET	为变量赋值
RETURN	返回函数结果

创建自定义函数示例

1. 简单的加法函数

-- 创建自定义函数：价格加10块钱
CREATE FUNCTION add_10(price FLOAT)
RETURNS FLOAT
BEGIN
  -- 函数体：编辑您的逻辑
  DECLARE new_price FLOAT;
  -- 其他SQL语句或逻辑
  SET new_price = price + 10;  -- 赋值过程
  RETURN new_price;             -- 返回一个结果
END;

2. 计算数据差异的函数

-- 创建自定义函数：计算两个数据之间的差异
CREATE FUNCTION calc_diff(value1 FLOAT, value2 FLOAT)
RETURNS FLOAT
BEGIN
  DECLARE diff FLOAT;
  SET diff = ABS(value1 - value2);
  RETURN diff;
END;

启用自定义函数

-- 创建自定义函数开关（某些MySQL版本需要启用）
SET GLOBAL log_bin_trust_function_creators = TRUE;

调用自定义函数

-- ✅ 正确调用方式：在SELECT中调用
SELECT add_10(10);              -- 结果：20

-- 在查询中使用
SELECT *, add_10(price) AS 加价后价格
FROM books;

-- ❌ 错误调用方式（这些都不能工作）
add_10;                          -- 不能直接调用
add_10(10);                      -- 不能直接调用
SELECT add_10;                   -- 不能这样调用

查看自定义函数

-- 查看当前数据库的所有自定义函数
SHOW FUNCTION STATUS WHERE Db = "exercise";

-- 查看指定函数的创建代码
SHOW CREATE FUNCTION add_10;

-- 删除自定义函数
DROP FUNCTION add_10;

条件分支在自定义函数中的应用

-- 创建打折函数：按作者进行打折
CREATE FUNCTION get_author_discount(author VARCHAR(20), price FLOAT)
RETURNS FLOAT
BEGIN
  DECLARE discount FLOAT;
  SET discount = price;
  
  IF author = "老舍" THEN
    SET discount = price * 0.8;
  ELSEIF author = "矛盾" THEN
    SET discount = price * 0.7;
  ELSE
    SET discount = price;
  END IF;
  
  RETURN discount;
END;

-- 调用
SELECT *, get_author_discount(author, price) AS 打折后价格
FROM books;

复杂例子：叠加打折

-- 创建函数：先按作者打折，再按出版社打折
CREATE FUNCTION get_discount_price(author VARCHAR(20), press VARCHAR(50), price FLOAT)
RETURNS FLOAT
BEGIN
  DECLARE discount_price FLOAT;
  
  -- 先按照作者打折
  IF author = "老舍" THEN
    SET discount_price = price * 0.8;
  ELSEIF author = "矛盾" THEN
    SET discount_price = price * 0.7;
  ELSE
    SET discount_price = price;
  END IF;
  
  -- 再按照出版社打折
  IF press = "机械工业出版社" THEN
    SET discount_price = discount_price * 0.8;
  ELSEIF press = "人民教育出版社" THEN
    SET discount_price = discount_price * 0.5;
  END IF;
  
  RETURN discount_price;
END;

-- 调用
SELECT *, get_discount_price(author, press, price) AS 最终价格
FROM books;

自定义函数的特点

✅ 优点：

• 简化复杂的计算逻辑
• 提高代码复用性
• 使查询更清晰易读

❌ 局限：

• 只能返回一个值
• 无法返回完整的查询结果集
• 业务特性强，通用性小

---

三、存储过程（Stored Procedure）

基础语法

CREATE PROCEDURE 存储过程名(参数1 类型1, 参数2 类型2...)
BEGIN
  -- 过程体（一个或多个SQL查询过程）
  过程体逻辑;
END;

存储过程的特点

• 可以包含多个SQL语句
• 返回完整的查询结果集
• 可以执行复杂的业务逻辑

实际应用示例

场景：根据老师名称找到其所有学员

分析过程：

第一步：根据老师名称找到老师ID
第二步：根据老师ID找到所教课程的课程ID
第三步：根据课程ID找到所有参加考试的学员ID

创建存储过程

-- 创建存储过程：获取指定老师的所有学员ID
CREATE PROCEDURE get_stu(teacher_name VARCHAR(20))
BEGIN
  SELECT s_id FROM score 
  WHERE c_id = (
    SELECT c_id FROM course  
    WHERE t_id = (
      SELECT t_id FROM teacher WHERE t_name = teacher_name
    )
  );
END;

调用存储过程

-- 使用 CALL 关键词调用存储过程
CALL get_stu("张三");

-- 结果：返回张三老师所教课程的所有学员ID

查看存储过程

-- 查看当前数据库的所有存储过程
SHOW PROCEDURE STATUS WHERE Db = "exercise";

-- 查看指定存储过程的创建代码
SHOW CREATE PROCEDURE get_stu;

-- 删除存储过程
DROP PROCEDURE get_stu;

更复杂的存储过程示例

-- 创建存储过程：获取指定学生的所有成绩信息
CREATE PROCEDURE get_student_scores(student_id INT)
BEGIN
  SELECT s.s_id, s.s_name, c.c_name, sc.s_score
  FROM student s
  LEFT JOIN score sc ON s.s_id = sc.s_id
  LEFT JOIN course c ON sc.c_id = c.c_id
  WHERE s.s_id = student_id
  ORDER BY c.c_name;
END;

-- 调用
CALL get_student_scores(1);

---

四、自定义函数 vs 存储过程对比

详细对比表

对比维度	自定义函数	存储过程
目的	简单计算和数据转换	封装复杂业务逻辑
业务性	与业务相关，通用性小	封装整个业务流程
返回结果	仅返回一个值	返回完整查询结果集
返回方式	RETURN 返回单一值	通过SELECT返回结果集
调用方式	SELECT 函数名()	CALL 存储过程名()
复杂度	简单逻辑	复杂多步骤逻辑
查询中使用	可以嵌入SELECT	不能嵌入SELECT
参数修改	参数不可修改	参数可带OUT修饰符

使用场景对比

自定义函数适用于：

-- 1. 简单的数据转换
SELECT ADD_10(price) FROM books;

-- 2. 常规的计算
SELECT CALC_DIFF(value1, value2) FROM table;

-- 3. 条件判断结果
SELECT GET_DISCOUNT(author, price) FROM books;

存储过程适用于：

-- 1. 多步骤业务流程
CALL get_stu("张三");

-- 2. 涉及多个表的操作
CALL calculate_student_average(student_id);

-- 3. 需要返回完整结果集
CALL get_student_scores(1);

---

五、自定义函数内部的常用语句

SET 赋值语句

-- 为变量赋值
SET new_price = price + 10;
SET discount = price * 0.8;

IF 条件语句

-- IF-THEN-ELSEIF-ELSE 结构
IF 条件1 THEN
  操作1;
ELSEIF 条件2 THEN
  操作2;
ELSE
  操作3;
END IF;

-- 例子
IF author = "老舍" THEN
  SET discount = price * 0.8;
ELSEIF author = "矛盾" THEN
  SET discount = price * 0.7;
ELSE
  SET discount = price;
END IF;

CASE 条件语句

-- CASE-WHEN 结构
CASE 
  WHEN 条件1 THEN 操作1;
  WHEN 条件2 THEN 操作2;
  ELSE 操作3;
END CASE;

-- 例子
CASE
  WHEN price > 100 THEN SET discount = 0.5;
  WHEN price > 50 THEN SET discount = 0.7;
  ELSE SET discount = 0.9;
END CASE;

DECLARE 变量声明

-- 声明并初始化变量
DECLARE new_price FLOAT;
DECLARE discount FLOAT DEFAULT 1.0;
DECLARE count_num INT;

---

六、核心要点总结

✅ 自定义函数：

CREATE FUNCTION 函数名(参数 类型)
RETURNS 返回类型
BEGIN
  DECLARE 变量 类型;
  SET 变量 = ...;
  RETURN 返回值;
END;

✅ 调用自定义函数：

SELECT 函数名(参数1, 参数2, ...) FROM 表;

✅ 存储过程：

CREATE PROCEDURE 存储过程名(参数 类型)
BEGIN
  SELECT ... FROM ...;
END;

✅ 调用存储过程：

CALL 存储过程名(参数值);

✅ 查看和删除：

-- 查看所有自定义函数/存储过程
SHOW FUNCTION STATUS WHERE Db = "数据库名";
SHOW PROCEDURE STATUS WHERE Db = "数据库名";

-- 查看特定对象的代码
SHOW CREATE FUNCTION/PROCEDURE 名称;

-- 删除
DROP FUNCTION 函数名;
DROP PROCEDURE 存储过程名;

✅ 自定义函数常用语句：

• DECLARE：声明变量
• SET：赋值
• IF-THEN-ELSE：条件分支
• CASE-WHEN：多条件判断
• RETURN：返回结果

✅ 使用场景：

• 自定义函数：简单计算、数据转换、条件判断
• 存储过程：复杂业务流程、多步骤操作、返回结果集

---

七、学习路径和练习

建议练习：

1. 创建简单的自定义函数（加减乘除）
2. 创建条件判断的自定义函数（打折、等级）
3. 创建叠加打折的复杂函数
4. 创建查询多步骤的存储过程
5. 创建涉及多个表的业务存储过程

难点总结：

• 自定义函数只能返回一个值
• 存储过程可以返回多行多列结果
• 调用方式不同（SELECT vs CALL）
• 函数适合简单逻辑，存储过程适合复杂流程