1、存储过程的概念
存储过程是事先经过编译并存储在数据库中的一段sql语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理效率是很有好处。
2、存储过程的优点
- 存储过程是通过处理封装在容易使用的单元中，简化了复杂的操作简化对变动的管理。如果表名、列名、或业务逻辑有了变化，只需要更改存储过程的代码，使用它的人不用更改自己的代码。
- 使用存储过程有助于提高应用程序的性能。当创建的存储过程被编译之后，就存储在数据库中。但是，mysql实现的存储过程略有不同。
- Mysql存储过程是按需编译，在编译存储过程之后，mysql将其放入缓存中。Mysql为每一连接维护自己的存储过程高速缓存，如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
- 存储过程有助于减少应用程序和数据库服务器之间的流量。因为应用程序不必发送多个冗长的sql语句，只有发送存储过程中的名称和参数即可
- 存储过程对任何应用程序都是可重用的和透明的，存储过程将数据库接口暴露给所有地方应用程序，以方便开发人员不必开发存储过程中支持的功能。
- 存储的程序是安全的。数据库管理员是可以向访问数据库中存储过程的应用程序授予适当的权限，而不是向基础数据库表提供任何权限。
3、存储过程的缺点
- 如果使用大量的存储过程，那么使用这些存储过程的每个连接的内存使用量将大大增加，此外，如果在存储过程中过渡使用大量的逻辑操作，那么CPU的使用率也在增加，因为mysql数据库最初的设计就侧重于高效的查询，而不是逻辑运算。
- 存储过程的构造使得开发具有了复杂的业务逻辑的存储过程变得困难
- 很难调试存储过程，只有少数数据库管理系统允许调试存储过程，不幸的是，mysql不提供调试存储过程的功能。
- 开发和维护存储过程都不易
- 开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能，这可能导致应用程序开发和维护阶段的问题。
- 对数据库依赖程度较高，移植性差
4、存储过程的定义
DELIMITER $$
CREATE
[DEFINER = {USER | CURRENT_USER}]
PROCEDURE
SQL SECURITY INVOKER -- 使用调用者的权限而不是创建者的
BEGIN
[DECLARE 变量名 类型 [DEFAULT 值]]
存储过程的语句块
END $$
DELIMITER;注：DELIMITER 命令可以将结束命令修改为其他字符
5、查看存储过程
-- 查看存储过程的状态
SHOW PROCEDURE STATUS LIKE 存储过程名;
-- 查看存储过程的定义
SHOW CREATE PROCEDURE 存储过程名;
-- 存储过程的信息都存储在 information_schema 数据库下的 Routines 表中，
-- 可以通过查询该表的记录来查询存储过程的信息，SQL 语句如下：
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;6、修改存储过程
MySQL 中修改存储过程的语法格式如下：
ALTER PROCEDURE 存储过程名 [ 特征 ... ]特征指定了存储过程的特性，可能的取值有：
- CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
- NO SQL 表示子程序中不包含 SQL 语句。
- READS SQL DATA 表示子程序中包含读数据的语句。
- MODIFIES SQL DATA 表示子程序中包含写数据的语句。
- SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
- DEFINER 表示只有定义者自己才能够执行。
- INVOKER 表示调用者可以执行。
- COMMENT 'string' 表示注释信息。
7、MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。
语法格式如下：
DROP PROCEDURE [ IF EXISTS ] <过程名>8、存储过程的调用
CALL 存储过程名([参数列表]);9、存储过程的参数有哪几种类型?分别表示什么?
IN类型的参数表示接受调用者传入的数据
Out类型的参数表示向调用者返回数据
Inout类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。
10、存储过程简单操作
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户唯一标识，自增主键',
username VARCHAR(50) NOT NULL COMMENT '用户登录名，唯一标识用户',
gender CHAR(1) CHECK (gender IN ('M', 'F')) COMMENT '用户性别：M=男，F=女',
age INT COMMENT '用户年龄，以周岁计算',
email VARCHAR(100) COMMENT '用户电子邮箱，可用于登录和找回密码',
is_active TINYINT(1) DEFAULT 1 COMMENT '账户状态：1=活跃，0=禁用',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '账户创建时间',
INDEX idx_gender (gender) COMMENT '性别字段索引，加速性别相关查询',
INDEX idx_age (age) COMMENT '年龄字段索引，加速年龄相关查询',
INDEX idx_active (is_active) COMMENT '活跃状态索引，加速状态筛选查询'
) COMMENT='系统用户信息表，存储所有注册用户的基本信息';
1. 无参数存储过程 - 获取所有活跃用户
DELIMITER //
-- 创建无参数存储过程：获取所有活跃用户
-- 过程名：sp_get_active_users
CREATE PROCEDURE sp_get_active_users()
BEGIN
/*
* 功能：查询所有活跃用户(即is_active=1的用户)
* 排序：按创建时间降序排列
* 输出：用户ID、用户名、性别、年龄、邮箱和创建时间
*/
SELECT
user_id,
username,
gender,
age,
email,
create_time
FROM
users
WHERE
is_active = 1
ORDER BY
create_time DESC;
-- 返回查询结果条数
SELECT CONCAT('查询到 ', ROW_COUNT(), ' 条活跃用户记录') AS result_message;
END //
DELIMITER ;
-- 调用无参数存储过程
CALL sp_get_active_users();2. 带参数存储过程 - 根据条件筛选用户
DELIMITER //
-- 创建带参数存储过程：根据条件筛选用户
-- 过程名：sp_filter_users
CREATE PROCEDURE sp_filter_users(
IN p_gender CHAR(1), -- 性别参数：'M'=男，'F'=女，NULL=不筛选
IN p_min_age INT, -- 最小年龄：NULL=不限制
IN p_max_age INT, -- 最大年龄：NULL=不限制
IN p_is_active TINYINT -- 活跃状态：1=活跃，0=不活跃，NULL=不筛选
)
BEGIN
/*
* 功能：根据多种条件组合筛选用户
* 参数说明：
* - p_gender: 按性别筛选
* - p_min_age/p_max_age: 按年龄范围筛选
* - p_is_active: 按活跃状态筛选
* 排序：默认按用户ID升序排列
*/
-- 使用动态SQL构建查询语句
SET @sql = 'SELECT user_id, username, gender, age, email, is_active, create_time
FROM users
WHERE 1=1';
-- 根据参数动态添加条件
IF p_gender IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' AND gender = ?');
SET @gender = p_gender;
END IF;
IF p_min_age IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' AND age >= ?');
SET @min_age = p_min_age;
END IF;
IF p_max_age IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' AND age <= ?');
SET @max_age = p_max_age;
END IF;
IF p_is_active IS NOT NULL THEN
SET @sql = CONCAT(@sql, ' AND is_active = ?');
SET @is_active = p_is_active;
END IF;
-- 添加排序条件
SET @sql = CONCAT(@sql, ' ORDER BY user_id ASC');
-- 准备并执行动态SQL
PREPARE stmt FROM @sql;
-- 根据参数是否存在绑定变量
IF p_gender IS NOT NULL AND p_min_age IS NOT NULL AND p_max_age IS NOT NULL AND p_is_active IS NOT NULL THEN
EXECUTE stmt USING @gender, @min_age, @max_age, @is_active;
ELSEIF p_gender IS NOT NULL AND p_min_age IS NOT NULL AND p_max_age IS NOT NULL THEN
EXECUTE stmt USING @gender, @min_age, @max_age;
ELSEIF p_gender IS NOT NULL AND p_min_age IS NOT NULL THEN
EXECUTE stmt USING @gender, @min_age;
ELSEIF p_gender IS NOT NULL THEN
EXECUTE stmt USING @gender;
ELSE
EXECUTE stmt;
END IF;
DEALLOCATE PREPARE stmt;
-- 返回查询结果统计
SELECT CONCAT('查询完成，共返回 ', ROW_COUNT(), ' 条记录') AS result_message;
END //
DELIMITER ;
-- 调用带参数存储过程的各种示例
-- 示例1：查询所有女性用户
CALL sp_filter_users('F', NULL, NULL, NULL);
-- 示例2：查询年龄在20-30岁之间的活跃用户
CALL sp_filter_users(NULL, 20, 30, 1);
-- 示例3：查询所有不活跃的男性用户
CALL sp_filter_users('M', NULL, NULL, 0);
-- 示例4：查询所有用户(不传任何参数)
CALL sp_filter_users(NULL, NULL, NULL, NULL);3. 带输出参数的存储过程 - 统计用户信息
DELIMITER //
-- 创建带输出参数的存储过程：统计用户信息
-- 过程名：sp_user_statistics
CREATE PROCEDURE sp_user_statistics(
OUT total_users INT, -- 输出参数：总用户数
OUT active_users INT, -- 输出参数：活跃用户数
OUT avg_age DECIMAL(5,2) -- 输出参数：平均年龄(保留2位小数)
)
BEGIN
/*
* 功能：统计用户基本信息
* 输出参数：
* - total_users: 总用户数
* - active_users: 活跃用户数
* - avg_age: 全体用户平均年龄
*/
-- 统计总用户数
SELECT COUNT(*) INTO total_users FROM users;
-- 统计活跃用户数
SELECT COUNT(*) INTO active_users FROM users WHERE is_active = 1;
-- 计算平均年龄
SELECT AVG(age) INTO avg_age FROM users WHERE age IS NOT NULL;
-- 同时返回统计结果(可选)
SELECT
total_users AS '总用户数',
active_users AS '活跃用户数',
avg_age AS '平均年龄';
END //
DELIMITER ;
-- 调用带输出参数的存储过程
-- 方法1：获取输出参数值
CALL sp_user_statistics(@total, @active, @avg_age);
SELECT @total AS total_users, @active AS active_users, @avg_age AS average_age;
-- 方法2：直接查看结果集(因为过程中有SELECT语句)
CALL sp_user_statistics(@total, @active, @avg_age);存储过程管理命令
-- 查看所有存储过程
SHOW PROCEDURE STATUS;
-- 查看特定存储过程的定义
SHOW CREATE PROCEDURE sp_filter_users;
-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_get_active_users;11、存储过程实战案例：电商订单处理系统
场景描述
我们需要为电商平台设计一个订单处理系统，主要功能包括：
- 创建新订单
- 处理订单支付
- 更新库存
- 计算订单折扣
- 生成销售报表
-- 数据库准备
-- 用户表
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(20),
vip_level INT DEFAULT 1,
registration_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_orders INT DEFAULT 0,
total_spent DECIMAL(10,2) DEFAULT 0.00
);
-- 商品表
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT NOT NULL,
category VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 订单表
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2) NOT NULL,
discount_amount DECIMAL(10,2) DEFAULT 0.00,
final_amount DECIMAL(10,2) NOT NULL,
status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
payment_method VARCHAR(50),
shipping_address TEXT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- 订单明细表
CREATE TABLE order_items (
item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
subtotal DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
-- 库存变动记录表
CREATE TABLE inventory_logs (
log_id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
change_quantity INT NOT NULL,
change_type ENUM('purchase', 'sale', 'return', 'adjustment') NOT NULL,
related_order_id INT,
log_date DATETIME DEFAULT CURRENT_TIMESTAMP,
notes TEXT,
FOREIGN KEY (product_id) REFERENCES products(product_id),
FOREIGN KEY (related_order_id) REFERENCES orders(order_id)
);存储过程实现
1. 创建新订单
DELIMITER $$
CREATE PROCEDURE create_new_order(
IN p_customer_id INT,
IN p_payment_method VARCHAR(50),
IN p_shipping_address TEXT,
OUT p_order_id INT
)
BEGIN
DECLARE v_order_count INT;
DECLARE v_vip_level INT;
DECLARE v_discount_rate DECIMAL(5,2) DEFAULT 0.00;
-- 检查客户是否存在
SELECT COUNT(*), vip_level INTO v_order_count, v_vip_level
FROM customers
WHERE customer_id = p_customer_id;
IF v_order_count = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer not found';
END IF;
-- 根据VIP等级设置折扣率
IF v_vip_level >= 3 THEN
SET v_discount_rate = 0.10; -- VIP3及以上享受10%折扣
ELSEIF v_vip_level = 2 THEN
SET v_discount_rate = 0.05; -- VIP2享受5%折扣
END IF;
-- 创建订单头
INSERT INTO orders (
customer_id,
payment_method,
shipping_address,
status
) VALUES (
p_customer_id,
p_payment_method,
p_shipping_address,
'pending'
);
-- 获取新订单ID
SET p_order_id = LAST_INSERT_ID();
-- 更新客户订单计数
UPDATE customers
SET total_orders = total_orders + 1
WHERE customer_id = p_customer_id;
-- 记录日志
INSERT INTO system_logs (action, table_name, record_id, user_id)
VALUES ('create', 'orders', p_order_id, p_customer_id);
END$$
DELIMITER ;2. 添加订单项
DELIMITER $$
CREATE PROCEDURE add_order_item(
IN p_order_id INT,
IN p_product_id INT,
IN p_quantity INT,
OUT p_item_id INT,
OUT p_message VARCHAR(255)
BEGIN
DECLARE v_product_price DECIMAL(10,2);
DECLARE v_stock_quantity INT;
DECLARE v_subtotal DECIMAL(10,2);
DECLARE v_order_status VARCHAR(20);
DECLARE v_customer_id INT;
-- 检查订单状态
SELECT status, customer_id INTO v_order_status, v_customer_id
FROM orders
WHERE order_id = p_order_id;
IF v_order_status IS NULL THEN
SET p_message = 'Order not found';
SET p_item_id = -1;
ELSEIF v_order_status != 'pending' THEN
SET p_message = 'Cannot add items to a non-pending order';
SET p_item_id = -1;
ELSE
-- 获取产品信息和库存
SELECT price, stock_quantity INTO v_product_price, v_stock_quantity
FROM products
WHERE product_id = p_product_id;
IF v_product_price IS NULL THEN
SET p_message = 'Product not found';
SET p_item_id = -1;
ELSEIF v_stock_quantity < p_quantity THEN
SET p_message = CONCAT('Insufficient stock. Available: ', v_stock_quantity);
SET p_item_id = -1;
ELSE
-- 计算小计
SET v_subtotal = v_product_price * p_quantity;
-- 添加订单项
INSERT INTO order_items (
order_id,
product_id,
quantity,
unit_price,
subtotal
) VALUES (
p_order_id,
p_product_id,
p_quantity,
v_product_price,
v_subtotal
);
SET p_item_id = LAST_INSERT_ID();
SET p_message = 'Item added successfully';
-- 预扣库存
UPDATE products
SET stock_quantity = stock_quantity - p_quantity
WHERE product_id = p_product_id;
-- 记录库存变动
INSERT INTO inventory_logs (
product_id,
change_quantity,
change_type,
related_order_id,
notes
) VALUES (
p_product_id,
-p_quantity,
'sale',
p_order_id,
CONCAT('Reserved for order #', p_order_id)
);
-- 记录日志
INSERT INTO system_logs (action, table_name, record_id, user_id)
VALUES ('add_item', 'order_items', p_item_id, v_customer_id);
END IF;
END IF;
END$$
DELIMITER ;3. 计算订单总金额并应用折扣
DELIMITER $$
CREATE PROCEDURE calculate_order_total(
IN p_order_id INT,
OUT p_total_amount DECIMAL(10,2),
OUT p_discount_amount DECIMAL(10,2),
OUT p_final_amount DECIMAL(10,2),
OUT p_message VARCHAR(255)
BEGIN
DECLARE v_customer_id INT;
DECLARE v_vip_level INT;
DECLARE v_discount_rate DECIMAL(5,2) DEFAULT 0.00;
DECLARE v_current_total DECIMAL(10,2);
DECLARE v_order_status VARCHAR(20);
-- 检查订单状态
SELECT status, customer_id INTO v_order_status, v_customer_id
FROM orders
WHERE order_id = p_order_id;
IF v_order_status IS NULL THEN
SET p_message = 'Order not found';
SET p_total_amount = 0;
SET p_discount_amount = 0;
SET p_final_amount = 0;
ELSEIF v_order_status != 'pending' THEN
SET p_message = 'Cannot calculate total for a non-pending order';
SET p_total_amount = 0;
SET p_discount_amount = 0;
SET p_final_amount = 0;
ELSE
-- 计算订单原始总金额
SELECT SUM(subtotal) INTO v_current_total
FROM order_items
WHERE order_id = p_order_id;
IF v_current_total IS NULL THEN
SET p_message = 'No items in the order';
SET p_total_amount = 0;
SET p_discount_amount = 0;
SET p_final_amount = 0;
ELSE
-- 获取客户VIP等级
SELECT vip_level INTO v_vip_level
FROM customers
WHERE customer_id = v_customer_id;
-- 根据VIP等级设置折扣率
IF v_vip_level >= 3 THEN
SET v_discount_rate = 0.10; -- VIP3及以上享受10%折扣
ELSEIF v_vip_level = 2 THEN
SET v_discount_rate = 0.05; -- VIP2享受5%折扣
END IF;
-- 计算折扣金额和最终金额
SET p_total_amount = v_current_total;
SET p_discount_amount = ROUND(v_current_total * v_discount_rate, 2);
SET p_final_amount = p_total_amount - p_discount_amount;
-- 更新订单金额
UPDATE orders
SET total_amount = p_total_amount,
discount_amount = p_discount_amount,
final_amount = p_final_amount
WHERE order_id = p_order_id;
SET p_message = 'Order total calculated successfully';
-- 记录日志
INSERT INTO system_logs (action, table_name, record_id, user_id)
VALUES ('calculate_total', 'orders', p_order_id, v_customer_id);
END IF;
END IF;
END$$
DELIMITER ;4. 处理订单支付
DELIMITER $$
CREATE PROCEDURE process_order_payment(
IN p_order_id INT,
IN p_payment_method VARCHAR(50),
IN p_payment_amount DECIMAL(10,2),
OUT p_status VARCHAR(20),
OUT p_message VARCHAR(255))
BEGIN
DECLARE v_final_amount DECIMAL(10,2);
DECLARE v_customer_id INT;
DECLARE v_order_status VARCHAR(20);
-- 获取订单信息
SELECT final_amount, customer_id, status
INTO v_final_amount, v_customer_id, v_order_status
FROM orders
WHERE order_id = p_order_id;
IF v_final_amount IS NULL THEN
SET p_status = 'failed';
SET p_message = 'Order not found';
ELSEIF v_order_status != 'pending' THEN
SET p_status = v_order_status;
SET p_message = CONCAT('Order is already ', v_order_status);
ELSEIF p_payment_amount < v_final_amount THEN
SET p_status = 'failed';
SET p_message = CONCAT('Insufficient payment. Required: ', v_final_amount);
ELSE
-- 更新订单状态
UPDATE orders
SET status = 'paid',
payment_method = p_payment_method
WHERE order_id = p_order_id;
-- 更新客户总消费金额
UPDATE customers
SET total_spent = total_spent + v_final_amount
WHERE customer_id = v_customer_id;
-- 检查并升级VIP等级
CALL check_vip_upgrade(v_customer_id);
SET p_status = 'paid';
SET p_message = 'Payment processed successfully';
-- 记录支付日志
INSERT INTO payment_logs (
order_id,
customer_id,
amount,
payment_method,
status
) VALUES (
p_order_id,
v_customer_id,
p_payment_amount,
p_payment_method,
'completed'
);
-- 记录系统日志
INSERT INTO system_logs (action, table_name, record_id, user_id)
VALUES ('payment', 'orders', p_order_id, v_customer_id);
END IF;
END$$
DELIMITER ;5. 检查并升级VIP等级
DELIMITER $$
CREATE PROCEDURE check_vip_upgrade(IN p_customer_id INT)
BEGIN
DECLARE v_total_spent DECIMAL(10,2);
DECLARE v_current_level INT;
DECLARE v_new_level INT DEFAULT 1;
-- 获取客户当前消费总额和VIP等级
SELECT total_spent, vip_level INTO v_total_spent, v_current_level
FROM customers
WHERE customer_id = p_customer_id;
-- 根据消费金额确定新的VIP等级
IF v_total_spent >= 10000 THEN
SET v_new_level = 5; -- 钻石VIP
ELSEIF v_total_spent >= 5000 THEN
SET v_new_level = 4; -- 白金VIP
ELSEIF v_total_spent >= 2000 THEN
SET v_new_level = 3; -- 黄金VIP
ELSEIF v_total_spent >= 1000 THEN
SET v_new_level = 2; -- 白银VIP
ELSE
SET v_new_level = 1; -- 普通会员
END IF;
-- 如果VIP等级提升，则更新
IF v_new_level > v_current_level THEN
UPDATE customers
SET vip_level = v_new_level
WHERE customer_id = p_customer_id;
-- 记录VIP升级日志
INSERT INTO vip_upgrade_logs (
customer_id,
old_level,
new_level,
upgrade_date
) VALUES (
p_customer_id,
v_current_level,
v_new_level,
NOW()
);
-- 发送通知(模拟)
INSERT INTO notifications (
customer_id,
title,
message,
is_read
) VALUES (
p_customer_id,
'VIP等级提升',
CONCAT('恭喜您已升级为VIP', v_new_level, '会员!'),
0
);
END IF;
END$$
DELIMITER ;6. 取消订单
DELIMITER $$
CREATE PROCEDURE cancel_order(
IN p_order_id INT,
OUT p_status VARCHAR(20),
OUT p_message VARCHAR(255))
BEGIN
DECLARE v_order_status VARCHAR(20);
DECLARE v_customer_id INT;
-- 获取订单状态
SELECT status, customer_id INTO v_order_status, v_customer_id
FROM orders
WHERE order_id = p_order_id;
IF v_order_status IS NULL THEN
SET p_status = 'error';
SET p_message = 'Order not found';
ELSEIF v_order_status = 'cancelled' THEN
SET p_status = 'cancelled';
SET p_message = 'Order is already cancelled';
ELSEIF v_order_status NOT IN ('pending', 'paid') THEN
SET p_status = v_order_status;
SET p_message = CONCAT('Cannot cancel order in ', v_order_status, ' status');
ELSE
-- 更新订单状态
UPDATE orders
SET status = 'cancelled'
WHERE order_id = p_order_id;
-- 恢复库存
UPDATE products p
JOIN order_items oi ON p.product_id = oi.product_id
SET p.stock_quantity = p.stock_quantity + oi.quantity
WHERE oi.order_id = p_order_id;
-- 记录库存恢复日志
INSERT INTO inventory_logs (
product_id,
change_quantity,
change_type,
related_order_id,
notes
) SELECT
product_id,
quantity,
'return',
p_order_id,
CONCAT('Order #', p_order_id, ' cancelled')
FROM order_items
WHERE order_id = p_order_id;
-- 如果是已支付订单，需要退款(模拟)
IF v_order_status = 'paid' THEN
-- 记录退款日志
INSERT INTO payment_logs (
order_id,
customer_id,
amount,
payment_method,
status,
is_refund
) SELECT
p_order_id,
v_customer_id,
final_amount,
payment_method,
'refunded',
1
FROM orders
WHERE order_id = p_order_id;
-- 更新客户总消费金额(减去退款)
UPDATE customers
SET total_spent = total_spent - (
SELECT final_amount FROM orders WHERE order_id = p_order_id
)
WHERE customer_id = v_customer_id;
-- 检查VIP降级
CALL check_vip_upgrade(v_customer_id);
-- 发送通知(模拟)
INSERT INTO notifications (
customer_id,
title,
message,
is_read
) VALUES (
v_customer_id,
'订单取消',
CONCAT('您的订单 #', p_order_id, ' 已取消，退款将原路返回。'),
0
);
END IF;
SET p_status = 'cancelled';
SET p_message = 'Order cancelled successfully';
-- 记录系统日志
INSERT INTO system_logs (action, table_name, record_id, user_id)
VALUES ('cancel', 'orders', p_order_id, v_customer_id);
END IF;
END$$
DELIMITER ;7. 生成销售报表
DELIMITER $$
CREATE PROCEDURE generate_sales_report(
IN p_start_date DATE,
IN p_end_date DATE,
IN p_category VARCHAR(50) DEFAULT NULL)
BEGIN
-- 创建临时表存储报表数据
DROP TEMPORARY TABLE IF EXISTS temp_sales_report;
CREATE TEMPORARY TABLE temp_sales_report (
category VARCHAR(50),
product_id INT,
product_name VARCHAR(100),
units_sold INT,
total_sales DECIMAL(12,2),
avg_price DECIMAL(10,2)
);
-- 填充报表数据
IF p_category IS NULL THEN
INSERT INTO temp_sales_report
SELECT
p.category,
p.product_id,
p.name AS product_name,
SUM(oi.quantity) AS units_sold,
SUM(oi.subtotal) AS total_sales,
ROUND(SUM(oi.subtotal) / SUM(oi.quantity), 2) AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'paid'
AND o.order_date BETWEEN p_start_date AND p_end_date
GROUP BY p.category, p.product_id, p.name
ORDER BY p.category, total_sales DESC;
ELSE
INSERT INTO temp_sales_report
SELECT
p.category,
p.product_id,
p.name AS product_name,
SUM(oi.quantity) AS units_sold,
SUM(oi.subtotal) AS total_sales,
ROUND(SUM(oi.subtotal) / SUM(oi.quantity), 2) AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'paid'
AND o.order_date BETWEEN p_start_date AND p_end_date
AND p.category = p_category
GROUP BY p.category, p.product_id, p.name
ORDER BY total_sales DESC;
END IF;
-- 输出报表
SELECT * FROM temp_sales_report;
-- 输出汇总信息
SELECT
COUNT(DISTINCT product_id) AS total_products,
SUM(units_sold) AS total_units_sold,
SUM(total_sales) AS grand_total_sales,
ROUND(SUM(total_sales) / SUM(units_sold), 2) AS overall_avg_price
FROM temp_sales_report;
-- 记录报表生成日志
INSERT INTO report_logs (
report_type,
start_date,
end_date,
filter_category,
generated_at
) VALUES (
'sales_report',
p_start_date,
p_end_date,
p_category,
NOW()
);
END$$
DELIMITER ;存储过程使用示例
创建新订单
-- 创建新订单
CALL create_new_order(1, 'credit_card', '123 Main St, Anytown, USA', @new_order_id);
SELECT @new_order_id;
-- 添加订单项
CALL add_order_item(@new_order_id, 1, 2, @item_id1, @message1);
SELECT @item_id1, @message1;
CALL add_order_item(@new_order_id, 3, 1, @item_id2, @message2);
SELECT @item_id2, @message2;
-- 计算订单总金额
CALL calculate_order_total(@new_order_id, @total_amount, @discount_amount, @final_amount, @calc_message);
SELECT @total_amount, @discount_amount, @final_amount, @calc_message;
-- 处理支付
CALL process_order_payment(@new_order_id, 'credit_card', @final_amount, @payment_status, @payment_message);
SELECT @payment_status, @payment_message;生成销售报表
-- 生成所有类别的销售报表
CALL generate_sales_report('2023-01-01', '2023-12-31', NULL);
-- 生成特定类别的销售报表
CALL generate_sales_report('2023-01-01', '2023-12-31', 'Electronics');取消订单
-- 取消订单
CALL cancel_order(@new_order_id, @cancel_status, @cancel_message);
SELECT @cancel_status, @cancel_message;存储过程管理
查看存储过程
-- 查看存储过程状态
SHOW PROCEDURE STATUS LIKE 'calculate_order_total';
-- 查看存储过程定义
SHOW CREATE PROCEDURE calculate_order_total;
-- 通过information_schema查看
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME = 'calculate_order_total';修改存储过程
-- 修改存储过程特征
ALTER PROCEDURE calculate_order_total
SQL SECURITY INVOKER
COMMENT 'Calculates order total with discounts';删除存储过程
-- 删除存储过程
DROP PROCEDURE IF EXISTS calculate_order_total;