MySQL 存储过程与函数

MySQL 存储过程与函数

1. 存储过程概述

1.1 理解

• 含义：存储过程（Stored Procedure）是一组经过预先编译的SQL语句的封装，预先存储在MySQL服务器上。
• 执行过程：客户端调用存储过程，服务器直接执行预先存储的SQL语句，无需逐条传输。
• 核心优势：
  1. 简化操作，提高SQL语句的重用性，减少开发工作量；
  2. 减少网络传输量，客户端无需发送大量SQL语句；
  3. 降低SQL语句暴露的风险，提升数据安全性；
  4. 可设置权限控制，实现更细粒度的数据访问管理。
• 与视图的对比：
  • 视图是虚拟表，不直接操作底层数据；存储过程是程序化SQL，可直接操作底层表，实现复杂数据处理逻辑；
  • 存储过程无返回值，调用方式为CALL 存储过程名()。

1.2 分类

根据参数类型分为5类：

1. 无参数（无参数无返回）；
2. 仅带IN类型（有参数无返回）；
3. 仅带OUT类型（无参数有返回）；
4. 既带IN又带OUT（有参数有返回）；
5. 带INOUT（有参数有返回）。

• 注意：IN、OUT、INOUT都可以在一个存储过程中带多个。

---

2. 创建存储过程

2.1 语法分析

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型, ...)
[characteristics ...]
BEGIN-- 存储过程体（多条SQL语句，每条以分号结尾）
END;

关键说明

1. 参数类型：
   • IN：输入参数，调用时传入值；
   • OUT：输出参数，存储过程执行后向外返回值；
   • INOUT：既可以输入，也可以输出。
2. characteristics约束条件：

   LANGUAGE SQL
   | [NOT] DETERMINISTIC
   | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
   | SQL SECURITY { DEFINER | INVOKER }
   | COMMENT 'string'
   

   • LANGUAGE SQL：说明存储过程由SQL语句组成；
   • [NOT] DETERMINISTIC：是否为确定性结果（默认NOT DETERMINISTIC）；
   • CONTAINS SQL/NO SQL/READS SQL DATA/MODIFIES SQL DATA：指定存储过程对SQL语句的使用限制（默认CONTAINS SQL）；
   • SQL SECURITY：DEFINER（仅创建者可执行）或INVOKER（有访问权限的用户均可执行），默认DEFINER；
   • COMMENT 'string'：存储过程注释。
3. 存储过程体：
   • 多条SQL语句需放在BEGIN...END之间，单条语句可省略；
   • 变量声明需在BEGIN...END内的最前面，使用DECLARE关键字；
   • 变量赋值可使用SET或SELECT...INTO。
4. 修改结束标记：
   MySQL默认语句结束符为;，与存储过程体中的分号冲突，需用DELIMITER修改：

   DELIMITER $ -- 将结束符改为$
   CREATE PROCEDURE ...
   BEGIN-- SQL语句（以;结尾）
   END $ -- 以新结束符$结束存储过程
   DELIMITER ; -- 恢复默认结束符
   

2.2 代码举例

示例1：无参数存储过程

DELIMITER $
CREATE PROCEDURE select_all_data()
BEGINSELECT * FROM emps;
END $
DELIMITER ;

示例2：带OUT参数的存储过程

DELIMITER //
CREATE PROCEDURE avg_employee_salary(OUT avg_sal DOUBLE)
BEGINSELECT AVG(salary) INTO avg_sal FROM emps;
END //
DELIMITER ;

---

3. 调用存储过程

3.1 调用格式

-- 无参数
CALL 存储过程名();-- 带IN参数
CALL 存储过程名(实参);-- 带OUT/INOUT参数
SET @变量名;
CALL 存储过程名(@变量名);
SELECT @变量名; -- 查看返回值

3.2 代码举例

-- 调用示例1
CALL select_all_data();-- 调用示例2
SET @avg_sal;
CALL avg_employee_salary(@avg_sal);
SELECT @avg_sal;

3.3 如何调试

存储过程调试难度较高，可通过以下方式辅助排查：

1. 分步测试：将存储过程体中的SQL语句单独执行，验证逻辑正确性；
2. 增加SELECT输出：在存储过程中添加SELECT语句，输出中间变量值；
3. 使用第三方工具：部分DBMS提供存储过程调试功能（如MySQL Workbench）。

---

4. 存储函数的使用

4.1 语法分析

CREATE FUNCTION 函数名(参数名 参数类型, ...)
RETURNS 返回值类型
[characteristics ...]
BEGIN-- 函数体（必须包含RETURN语句）
END;

关键说明

1. 参数：函数参数默认为IN，无需显式指定；
2. RETURNS：强制指定返回值类型，函数体必须包含RETURN value语句；
3. characteristics：与存储过程的取值相同；
4. 调用方式：与MySQL内置函数一致，直接在SELECT语句中使用：SELECT 函数名(实参);。

4.2 代码举例

示例1：无参数存储函数

DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQL
BEGINRETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;-- 调用
SELECT email_by_name();

示例2：带IN参数的存储函数

-- 需先设置全局变量（解决权限问题）
SET GLOBAL log_bin_trust_function_creators = 1;DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGINRETURN (SELECT email FROM employees WHERE employee_id = emp_id);
END //
DELIMITER ;-- 调用
SELECT email_by_id(101);
SET @emp_id := 102;
SELECT email_by_id(@emp_id);

4.3 常见问题处理

创建存储函数时若报错you might want to use the less safe log_bin_trust_function_creators variable，有两种解决方式：

1. 增加函数特性：添加[NOT] DETERMINISTIC和{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}；
2. 修改全局变量：SET GLOBAL log_bin_trust_function_creators = 1;。

4.4 存储过程与存储函数对比

特性	存储过程（PROCEDURE）	存储函数（FUNCTION）
关键字	PROCEDURE	FUNCTION
调用方式	CALL 存储过程()	SELECT 函数()
返回值	0个或多个（通过OUT/INOUT参数）	只能是1个
应用场景	数据更新、复杂逻辑处理	查询单个值并返回
其他	功能更强大，可执行表操作	可嵌入SQL语句中使用

---

5. 存储过程和函数的查看、修改、删除

5.1 查看

方式1：SHOW CREATE语句

-- 查看存储过程
SHOW CREATE PROCEDURE 存储过程名;-- 查看存储函数
SHOW CREATE FUNCTION 函数名;

方式2：SHOW STATUS语句

-- 查看所有存储过程
SHOW PROCEDURE STATUS;-- 查看指定存储过程
SHOW PROCEDURE STATUS LIKE '存储过程名';-- 查看存储函数
SHOW FUNCTION STATUS LIKE '函数名';

方式3：查询information_schema.Routines表

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = '存储过程/函数名' 
AND ROUTINE_TYPE = 'PROCEDURE'/'FUNCTION';

5.2 修改

修改存储过程/函数仅改变特性，不修改逻辑，使用ALTER语句：

ALTER {PROCEDURE | FUNCTION} 存储过程/函数名
[characteristics ...];

示例：修改存储过程的权限和注释

ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT '查询最高工资';

示例：修改存储函数的读写权限和注释

ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME';

5.3 删除

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程/函数名;

示例

DROP PROCEDURE IF EXISTS show_min_salary;
DROP FUNCTION IF EXISTS count_by_id;

---

6. 关于存储过程使用的争议

6.1 优点

1. 一次编译，多次使用：存储过程仅在创建时编译，后续调用无需重新编译，提升执行效率；
2. 减少开发工作量：将复杂逻辑封装为模块，可重复使用，代码结构清晰；
3. 安全性强：可设置用户权限控制数据访问；
4. 减少网络传输量：客户端仅需调用存储过程，无需发送大量SQL语句；
5. 良好的封装性：复杂数据库操作仅需一次数据库连接，减少连接次数。

6.2 缺点

1. 可移植性差：不同数据库的存储过程语法差异大，无法跨数据库移植；
2. 调试困难：多数DBMS对存储过程的调试支持有限，复杂存储过程的开发和维护难度高；
3. 版本管理困难：存储过程本身无版本控制，数据表结构变更可能导致存储过程失效；
4. 不适合高并发场景：高并发场景下，存储过程会增加数据库压力，且扩展性差；
5. 阿里开发规范：强制禁止使用存储过程，因其难以调试、扩展和移植。

---