MySQL存储过程
MySQL 存储过程（Stored Procedure）是一组预编译的 SQL 语句集合，保存在数据库中，可通过名称直接调用。它能封装复杂业务逻辑、提高执行效率、增强代码复用性，是数据库编程的重要工具。
- 预编译：存储过程创建时被编译，后续调用无需重新编译，比动态 SQL 执行更快。
- 封装性：将多步 SQL 操作（查询、插入、更新等）封装为一个单元，隐藏实现细节。
- 可复用：一次创建，多次调用，减少应用程序中的重复 SQL 代码。
- 安全性：可授予用户调用存储过程的权限，而不直接授予表权限，增强数据安全。
1、存储过程创建与管理
1.1 创建存储过程
基本语法：
DELIMITER //
CREATE PROCEDURE procedure_name (
[IN | OUT | INOUT] parameter_name data_type [(length)]
[, ...]
)
BEGIN
-- SQL 语句
-- 控制结构
-- 异常处理
END //
DELIMITER ;
示例：创建简单存储过程
DELIMITER //
-- 创建获取员工信息的存储过程
CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)
BEGIN
SELECT * FROM employees WHERE employee_id = emp_id;
END //
DELIMITER ;
1.2 调用存储过程
-- 调用存储过程
CALL GetEmployeeDetails(1001);
-- 调用带输出参数的存储过程
CALL CalculateOrderTotal(123, @total);
SELECT @total;
1.3 查看存储过程
-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'your_database';
-- 查看存储过程定义
SHOW CREATE PROCEDURE GetEmployeeDetails;
-- 从信息模式查询
SELECT * FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = 'your_database';
1.4 修改存储过程
-- MySQL 不支持直接修改存储过程，需先删除再重建
DROP PROCEDURE IF EXISTS GetEmployeeDetails;
DELIMITER //
CREATE PROCEDURE GetEmployeeDetails(IN emp_id INT)
BEGIN
-- 修改后的逻辑
SELECT employee_id, first_name, last_name, department
FROM employees WHERE employee_id = emp_id;
END //
DELIMITER ;
1.5 删除存储过程
DROP PROCEDURE IF EXISTS GetEmployeeDetails;
2、参数类型详解
2.1 参数类型
| 类型 | 描述 | 示例 |
|---|---|---|
| IN | 输入参数（默认） | IN emp_id INT |
| OUT | 输出参数 | OUT total_sales DECIMAL(10,2) |
| INOUT | 输入输出参数 | INOUT counter INT |
2.2 参数使用示例
DELIMITER //
-- 带输入和输出参数的存储过程
CREATE PROCEDURE CalculateOrderTotal(
IN order_id INT,
OUT total_amount DECIMAL(10,2)
)
BEGIN
SELECT SUM(quantity * unit_price) INTO total_amount
FROM order_items
WHERE order_id = order_id;
END //
DELIMITER ;
-- 调用示例
CALL CalculateOrderTotal(123, @order_total);
SELECT @order_total AS OrderTotal;
3、控制结构
3.1 条件语句
DELIMITER //
CREATE PROCEDURE UpdateProductPrice(
IN product_id INT,
IN price_change DECIMAL(5,2)
)
BEGIN
DECLARE current_price DECIMAL(10,2);
-- 获取当前价格
SELECT price INTO current_price FROM products WHERE id = product_id;
-- IF 条件判断
IF price_change > 0 THEN
UPDATE products SET price = current_price + price_change WHERE id = product_id;
SELECT 'Price increased' AS Result;
ELSEIF price_change < 0 THEN
UPDATE products SET price = current_price + price_change WHERE id = product_id;
SELECT 'Price decreased' AS Result;
ELSE
SELECT 'No price change' AS Result;
END IF;
END //
DELIMITER ;
3.2 循环语句
DELIMITER //
CREATE PROCEDURE GenerateTestOrders(
IN customer_id INT,
IN num_orders INT
)
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE order_date DATE;
-- WHILE 循环
WHILE i <= num_orders DO
SET order_date = DATE_ADD(CURDATE(), INTERVAL i DAY);
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (customer_id, order_date, RAND() * 1000);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
3.3 CASE 语句
DELIMITER //
CREATE PROCEDURE GetCustomerLevel(
IN customer_id INT,
OUT customer_level VARCHAR(20)
)
BEGIN
DECLARE total_spent DECIMAL(10,2);
SELECT SUM(total_amount) INTO total_spent
FROM orders
WHERE customer_id = customer_id;
-- CASE 语句
CASE
WHEN total_spent > 10000 THEN SET customer_level = 'Platinum';
WHEN total_spent > 5000 THEN SET customer_level = 'Gold';
WHEN total_spent > 1000 THEN SET customer_level = 'Silver';
ELSE SET customer_level = 'Standard';
END CASE;
END //
DELIMITER ;
4、错误处理与事务
4.1 错误处理
DELIMITER //
CREATE PROCEDURE SafeTransferFunds(
IN from_account INT,
IN to_account INT,
IN amount DECIMAL(10,2)
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- 错误处理
ROLLBACK;
SELECT 'Transaction failed: ' + SQLSTATE AS Result;
END;
START TRANSACTION;
-- 扣款
UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
-- 验证余额
IF (SELECT balance FROM accounts WHERE account_id = from_account) < 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
END IF;
-- 存款
UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
COMMIT;
SELECT 'Transaction successful' AS Result;
END //
DELIMITER ;
4.2 事务控制
DELIMITER //
CREATE PROCEDURE ProcessOrder(
IN order_id INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Order processing failed' AS Result;
END;
START TRANSACTION;
-- 减少库存
UPDATE products p
JOIN order_items oi ON p.product_id = oi.product_id
SET p.stock = p.stock - oi.quantity
WHERE oi.order_id = order_id;
-- 标记订单为已处理
UPDATE orders SET status = 'processed' WHERE order_id = order_id;
-- 记录处理日志
INSERT INTO order_logs (order_id, action, timestamp)
VALUES (order_id, 'processed', NOW());
COMMIT;
SELECT 'Order processed successfully' AS Result;
END //
DELIMITER ;
🔍 五、游标使用
5.1 游标基础
DELIMITER //
CREATE PROCEDURE GenerateMonthlyReport(
IN report_month CHAR(7)
)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE v_customer_id INT;
DECLARE v_total DECIMAL(10,2);
-- 声明游标
DECLARE cur CURSOR FOR
SELECT customer_id, SUM(total_amount) AS monthly_total
FROM orders
WHERE DATE_FORMAT(order_date, '%Y-%m') = report_month
GROUP BY customer_id;
-- 声明处理程序
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 创建临时表存储结果
CREATE TEMPORARY TABLE IF NOT EXISTS monthly_report (
customer_id INT,
total_spent DECIMAL(10,2),
customer_level VARCHAR(20)
);
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_customer_id, v_total;
IF done THEN
LEAVE read_loop;
END IF;
-- 计算客户等级
CASE
WHEN v_total > 5000 THEN SET @level = 'Gold';
WHEN v_total > 1000 THEN SET @level = 'Silver';
ELSE SET @level = 'Standard';
END CASE;
-- 插入结果
INSERT INTO monthly_report (customer_id, total_spent, customer_level)
VALUES (v_customer_id, v_total, @level);
END LOOP;
CLOSE cur;
-- 返回结果
SELECT * FROM monthly_report ORDER BY total_spent DESC;
-- 清理临时表
DROP TEMPORARY TABLE IF EXISTS monthly_report;
END //
DELIMITER ;
浙公网安备 33010602011771号