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)
BEGINSELECT * 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)
)
BEGINSELECT SUM(quantity * unit_price) INTO total_amountFROM order_itemsWHERE 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)
)
BEGINDECLARE current_price DECIMAL(10,2);-- 获取当前价格SELECT price INTO current_price FROM products WHERE id = product_id;-- IF 条件判断IF price_change > 0 THENUPDATE products SET price = current_price + price_change WHERE id = product_id;SELECT 'Price increased' AS Result;ELSEIF price_change < 0 THENUPDATE products SET price = current_price + price_change WHERE id = product_id;SELECT 'Price decreased' AS Result;ELSESELECT 'No price change' AS Result;END IF;
END //DELIMITER ;

3.2 循环语句

DELIMITER //CREATE PROCEDURE GenerateTestOrders(IN customer_id INT,IN num_orders INT
)
BEGINDECLARE i INT DEFAULT 1;DECLARE order_date DATE;-- WHILE 循环WHILE i <= num_orders DOSET 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)
)
BEGINDECLARE total_spent DECIMAL(10,2);SELECT SUM(total_amount) INTO total_spentFROM ordersWHERE customer_id = customer_id;-- CASE 语句CASEWHEN 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)
)
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN-- 错误处理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 THENSIGNAL 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
)
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SELECT 'Order processing failed' AS Result;END;START TRANSACTION;-- 减少库存UPDATE products pJOIN order_items oi ON p.product_id = oi.product_idSET p.stock = p.stock - oi.quantityWHERE 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)
)
BEGINDECLARE done INT DEFAULT FALSE;DECLARE v_customer_id INT;DECLARE v_total DECIMAL(10,2);-- 声明游标DECLARE cur CURSOR FORSELECT customer_id, SUM(total_amount) AS monthly_totalFROM ordersWHERE DATE_FORMAT(order_date, '%Y-%m') = report_monthGROUP 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: LOOPFETCH cur INTO v_customer_id, v_total;IF done THENLEAVE read_loop;END IF;-- 计算客户等级CASEWHEN 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 ;