MYSQL存储过程

概念

MySQL 存储过程（Stored Procedure）是一组预编译的 SQL 语句，用于在数据库中执行特定的任务或操作。它类似于其他编程语言中的函数或方法，可以接受输入参数、处理数据，并返回结果。存储过程存储在数据库中，可以通过调用其名称来执行。

特点

预编译：存储过程在创建时被编译，之后每次调用时不需要重新编译，这提高了执行效率.
封装性：将复杂的 SQL 逻辑封装在存储过程中，简化了应用程序的代码，使得数据库操作更加模块化.
安全性：通过存储过程可以限制用户对数据库的直接访问，仅允许用户通过存储过程执行特定的操作，从而提高了数据库的安全性.
复用性：存储过程可以在不同的应用程序中被重复调用，避免了重复编写相同的 SQL 语句，提高了代码的复用性.
事务管理：存储过程可以包含事务控制语句（如 BEGIN TRANSACTION、COMMIT、ROLLBACK），确保数据的完整性和一致性.

存储过程的创建

-- 创建一个计算平均价格的存储过程
create procedure CalculateAveragePrice()
begin-- 计算平均价格的 SQL 语句-- 使用 select 语句查询 products 表的平均价格，并将结果命名为 average_priceselect avg(price) as average_price from products;
end;

存储过程的调用

-- 调用计算平均价格的存储过程
call CalculateAveragePrice();

存储过程的删除

-- 删除计算平均价格的存储过程
drop procedure CalculateAveragePrice;

存储过程的修改

-- 修改计算平均价格的存储过程
-- 设置存储过程的字符集和注释信息
alter procedure CalculateAveragePrice
character set utf8mb4 collate utf8mb4_0900_ai_ci comment '计算平均价格的存储过程';

MySQL 存储过程的控制语句

变量作用域

-- 变量作用域示例
create procedure VariableScopeExample()
begin-- 声明一个变量 x1，初始值为 'outer'declare x1 varchar(5) default 'outer';-- 开始一个新的作用域begin-- 在新作用域中声明一个同名变量 x1，初始值为 'inner'-- 这里的 x1 将覆盖外部作用域的 x1declare x1 varchar(5) default 'inner';-- 选择并输出当前作用域的 x1，结果为 'inner'select x1;end;-- 选择并输出外部作用域的 x1，结果为 'outer'select x1;
end;

条件语句：if-then-else

-- if-then-else 条件语句示例
create procedure ConditionalInsertUpdate(in parameter int)
begin-- 声明一个变量 vardeclare var int;-- 将输入参数 parameter 加 1 后赋值给 varset var = parameter + 1;-- 如果 var 等于 0，则向表 t 插入值 17if var = 0 theninsert into t values(17);end if;-- 如果输入参数 parameter 等于 0，则更新表 t 的 s1 字段，使其值加 1-- 否则，更新表 t 的 s1 字段，使其值加 2if parameter = 0 thenupdate t set s1 = s1 + 1;elseupdate t set s1 = s1 + 2;end if;
end;

条件语句：case语句

-- case 语句示例
create procedure CaseBasedInsert(in parameter int)
begin-- 声明一个变量 vardeclare var int;-- 将输入参数 parameter 加 1 后赋值给 varset var = parameter + 1;-- 根据 var 的值执行不同的插入操作case varwhen 0 then-- 如果 var 为 0，则向表 t 插入值 17insert into t values(17);when 1 then-- 如果 var 为 1，则向表 t 插入值 18insert into t values(18);else-- 如果 var 为其他值，则向表 t 插入值 19insert into t values(19);end case;
end;

循环语句：while ···· end while

-- while 循环示例
create procedure WhileLoopInsert()
begin-- 声明一个变量 var，初始值为 0declare var int;set var = 0;-- 当 var 小于 6 时，执行循环while var < 6 do-- 向表 t 插入当前 var 的值insert into t values(var);-- 将 var 加 1set var = var + 1;end while;
end;

循环语句：repeat···· end repeat

-- repeat 循环示例
create procedure RepeatLoopInsert()
begin-- 声明一个变量 v，初始值为 0declare v int;set v = 0;-- 重复执行循环，直到 v 大于等于 5repeat-- 向表 t 插入当前 v 的值insert into t values(v);-- 将 v 加 1set v = v + 1;until v >= 5end repeat;
end;

循环语句：loop ·····end loop

-- loop 循环示例
create procedure LoopInsert()
begin-- 声明一个变量 v，初始值为 0declare v int;set v = 0;-- 开始循环loop_label: loop-- 向表 t 插入当前 v 的值insert into t values(v);-- 将 v 加 1set v = v + 1;-- 如果 v 大于等于 5，则退出循环if v >= 5 thenleave loop_label;end if;end loop;
end;

其他控制语句

光标（Cursor）

-- 光标示例
create procedure CursorExample()
begin-- 声明一个变量 done，用于标记是否读取完毕declare done int default false;-- 声明一个变量 a，用于存储从光标中读取的值declare a int;-- 声明一个光标 cur，用于从 users 表中读取 id 字段declare cur cursor for select id from users;-- 声明一个继续处理程序，用于在没有更多行可读取时设置 done 为 truedeclare continue handler for not found set done = true;-- 打开光标open cur;-- 开始循环读取光标中的数据read_loop: loop-- 从光标中读取数据到变量 afetch cur into a;-- 如果 done 为 true，则退出循环if done thenleave read_loop;end if;-- 处理获取到的数据，这里仅选择并输出变量 aselect a;end loop;-- 关闭光标close cur;
end;

信号（Signal）

-- 信号示例
create procedure SignalExample()
begin-- 生成一个错误信号，SQLSTATE 代码为 '45000'，并设置错误信息为 '自定义错误信息'signal sqlstate '45000' set message_text = '自定义错误信息';
end;

处理程序（Handler）

-- 处理程序示例
create procedure HandlerExample()
begin-- 声明一个继续处理程序，用于捕获 SQL 异常declare continue handler for sqlexceptionbegin-- 处理异常，这里仅选择并输出 '发生异常'select '发生异常';end;-- 可能会引发异常的 SQL 语句，这里仅尝试向表 t 插入值 1insert into t values(1);
end;

应用场景

复杂查询：当需要执行复杂的查询操作，涉及多个表的连接、子查询、聚合函数等时，可以将这些查询逻辑封装在存储过程中，提高查询效率和代码的可读性.
批量操作：对于需要对大量数据进行批量插入、更新或删除的操作，存储过程可以一次性执行多条 SQL 语句，减少网络传输次数，提高执行效率.
数据完整性：通过存储过程可以确保数据的完整性约束，例如，在插入数据时检查数据的有效性、自动更新相关表的数据等，避免因应用程序逻辑错误导致的数据不一致问题.
业务逻辑处理：对于一些需要在数据库层面处理的业务逻辑，如计算复杂的业务指标、生成报表数据等，可以将这些逻辑封装在存储过程中，减轻应用程序的负担.
权限控制：通过存储过程可以实现更细粒度的权限控制，例如，允许用户调用存储过程执行特定的操作，但不允许用户直接访问底层的表和数据.
定时任务：可以创建存储过程来执行定时任务，如定期清理日志数据、更新统计信息等，通过数据库的定时任务调度功能来调用存储过程.

示例

假设有一个订单系统，需要在用户下单时执行以下操作：

检查库存是否充足.
如果库存充足，则更新库存数量、创建订单记录、生成订单号等.
如果库存不足，则返回错误信息.

这些操作可以通过一个存储过程来实现，如下所示：

DELIMITER //CREATE PROCEDURE PlaceOrder(IN product_id INT, IN quantity INT, IN user_id INT)
BEGINDECLARE stock INT;DECLARE order_id INT;-- 检查库存SELECT stock_quantity INTO stock FROM products WHERE id = product_id;IF stock >= quantity THEN-- 更新库存UPDATE products SET stock_quantity = stock_quantity - quantity WHERE id = product_id;-- 创建订单记录INSERT INTO orders (product_id, quantity, user_id) VALUES (product_id, quantity, user_id);SET order_id = LAST_INSERT_ID();-- 返回订单号SELECT order_id AS 'Order ID';ELSE-- 返回库存不足的错误信息SELECT 'Insufficient stock' AS 'Error';END IF;
END //DELIMITER ;