Mysql笔记-存储过程与存储函数

1. 存储过程(Stored Procedure)

1.1 概述

1.1.1 定义：

存储过程是一组预编译的 SQL 语句和控制流语句（如条件判断、循环）的集合，无返回值（但可通过 OUT/INOUT 参数或结果集返回数据）。它支持参数传递、事务控制、异常处理等高级特性，适合封装复杂的业务逻辑（如批量数据操作、多表关联查询）。（打包sql）

1.1.2 作用-（打包sql）

1、简化操作，提高了sql语句的重用性，减少了开发程序员的压力
2、减少操作过程中的失误，提高效率
3、减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
4、减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性

1.1.3 适合场景 --（其实现在也不常用了）

复杂业务逻辑：如订单处理（涉及库存扣减、支付记录、物流状态更新）。
批量数据操作：如定时任务（每日统计销售额并生成报表）。
3. 事务控制：确保多个 SQL 操作要么全部成功，要么全部回滚（START TRANSACTION + COMMIT/ROLLBACK）。
4. 性能优化：减少网络传输（一次调用执行多个语句），利用预编译提升执行效率。

1.2 创建及使用存储过程

1.2.1 创建语法：

DELIMITER 新的结束标记 如 $ ---此句非必须 
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[特性列表...]
BEGIN
存储过程体
END $ --如上方的DELIMITER是$ 这里也要是￥

特性列表（characteristics ）：可选，常见如 DETERMINISTIC（结果确定）、NO SQL（无 SQL 查询）、READS SQL DATA（读取 SQL 数据）等（用于优化器或安全策略）。-此处暂不展开
BEGIN 和 END: 存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END。
DELIMITER:因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。 “DELIMITER //”语句的作用是将MySQL的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。

1.2.2 参数-IN\OUT：

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

IN ：当前参数为输入参数，也就是表示入参；
存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN ，表示输入参数。
OUT ：当前参数为输出参数，也就是表示出参；(执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。)
INOUT ：当前参数既可以为输入参数，也可以为输出参数。
注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

1.2.3 调用存储过程

一、语法格式

CALL 存储过程名(实参列表)

二、调用不参数的存储过程

1、调用in模式的参数：
CALL sp1('值');
2、调用out模式的参数：
SET @name;
CALL sp1(@name);
SELECT @name;
3、调用inout模式的参数：
SET @name=值;
CALL sp1(@name);
SELECT @name;

1.2.4 示例

举例1（标识符使用）：创建存储过程select_all_data()，查看 emps 表的所有数据

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

举例2（参数使用）:创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN
SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END //
DELIMITER ;

2. 存储函数（Stored Function）

存储函数是一段返回单个值的特殊存储过程，必须有且仅有一个返回值（通过 RETURNS 子句声明类型）。它的参数只能是输入参数（IN 类型），且返回值通常用于 SQL 表达式（如 SELECT、WHERE 子句）。

2.1 概述

2.1.1 定义

存储函数是一段返回单个值的特殊存储过程，必须有且仅有一个返回值（通过 RETURNS 子句声明类型）。它的参数只能是输入参数（IN 类型），且返回值通常用于 SQL 表达式（如 SELECT、WHERE 子句）。
举例：常见的函数：LENGTH、SUBSTR、CONCAT等

2.1.2 作用

封装计算逻辑，返回单个值
简化 SQL 查询，避免重复代码
替代视图或触发器中的复杂逻辑
提高执行效率：存储函数在数据库中预编译，调用时直接执行编译后的代码，减少了 SQL 解析和传输的开销，尤其适用于高频调用的轻量级计算

2.1.3 适合场景–（这个现在也不常用）

1. 计算密集型操作：如日期格式化（FORMAT_DATE()）、数值计算（CALC_TAX()）。
2. 数据校验：如验证手机号格式（返回 VALID 或 INVALID）。
3. 简化 SQL 查询：在 SELECT 语句中调用函数，避免重复编写复杂表达式（如 SELECT GET_USERNAME(user_id)）。

2.2 创建及使用存储过程

2.2.1 创建语法：

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END

1、参数列表：FUNCTION中总是默认为IN参数。（指定参数为IN、OUT或INOUT只对存储过程是合法的，）
2、RETURNS type 语句表示函数返回数据的类型；（必须有）
3、characteristic 同存储过程相同。
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略。同存储过程相似。

2.2.2 调用存储过程

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL.的开发者定义的

SELECT 函数名(实参列表)

2.2.3 示例

存储函数创建示例

#示例1
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
DETERMINISTIC
CONTAINS SQLBEGIN
RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END //
DELIMITER ;##示例2
DELIMITER $$
CREATE FUNCTION CalculateTax(orderAmount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC  -- 声明结果确定（相同输入返回相同结果）
READS SQL DATA  -- 声明仅读取数据（无写操作）
BEGINRETURN orderAmount * 0.1;  -- 返回计算结果
END
$$
DELIMITER ;

存储函数调用示例

SELECT count_by_id(@dept_id);--调用无参数
SELECT email_by_name();--调用有参数

3. 存储过程与存储函数管理

存储过程与存储函数的查看、修改，删除

3.1 查看

查看创建信息–SHOW CREATE

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
--示例
SHOW CREATE FUNCTION test_db.CountProc；

查看状态信息-SHOW STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW PROCEDURE STATUS LIKE 'SELECT%' \G

查看属性信息-information_schema.Routines表

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
-示例
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION' \G

3.2 修改

修改已创建的存储过程或存储函数主要有两种方式：使用 ALTER 语句修改对象属性或使用 CREATE OR REPLACE 语句覆盖原定义（推荐用于逻辑修改）
一、使用 ALTER PROCEDURE（FUNCTION）修改属性（有限修改）
ALTER PROCEDURE 用于修改存储过程的元数据属性（如注释、参数模式、安全模式等），但无法修改存储过程的逻辑代码块（即 BEGIN…END 内的 SQL 语句）。

-- 原存储过程定义（假设已存在）
DELIMITER $$
CREATE PROCEDURE GetUserCount()
BEGINSELECT COUNT(*) FROM users;
END
$$
DELIMITER ;-- 修改注释和安全模式
ALTER PROCEDURE GetUserCount
COMMENT '获取系统总用户数（仅读取数据）'
READS SQL DATA;

二、使用 CREATE OR REPLACE PROCEDURE 覆盖原定义（推荐逻辑修改）
若需修改存储过程的逻辑代码块（如调整 SQL 语句、参数列表或流程控制），必须通过 CREATE OR REPLACE PROCEDURE 重新定义。该语句会删除原存储过程并创建新的同名过程，因此需确保新定义与原过程类型一致（均为 PROCEDURE）。

CREATE [OR REPLACE] PROCEDURE procedure_name([参数列表])
[特性列表]
BEGIN-- 新的 SQL 逻辑代码块
END;

三、完全删除后重新创建（备用方案）
若 CREATE OR REPLACE 无法满足需求（如参数类型变更导致无法覆盖），可先删除原存储过程，再重新创建。

3.3 删除

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名
--示例
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

4. 存储过程与存储函数比较

维度	存储过程	存储函数
返回值	可无返回值，支持通过 `OUT/INOUT` 参数传递多个值，或返回多结果集	必须有且仅返回单一值（标量或表）
参数类型	支持 `IN` / `OUT` / `INOUT` 三类参数	仅支持 `IN` 类型参数
调用方式	通过 `CALL 过程名()` 独立调用	直接嵌入 SQL 语句（如 `SELECT 函数名()`）
事务处理	允许使用 `COMMIT` / `ROLLBACK` 控制事务	禁止事务操作
数据修改权限	允许执行 `INSERT` / `UPDATE` / `DELETE` 等 DML 操作	禁止修改数据库状态（只读）
临时表	可创建并使用临时表	仅支持使用表变量（不能创建临时表）
适用场景	复杂业务逻辑（批量处理、事务性操作），如订单支付、数据迁移	轻量级计算（数值转换、动态字段生成），如税率计算
语法要求	使用 `BEGIN...END` 包裹复杂逻辑	必须包含 `RETURN` 语句定义返回值

阿里开发规范
【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
存储过程主要缺点如下：
1、可移植性差。存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。
2、调试困难。只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。
3、存储过程的版本管理很困难。比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。
4、它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就
不适用了。