MySQL存儲過程全解析

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_timeFROM usersWHERE is_active = 1ORDER 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 THENSET @sql = CONCAT(@sql, ' AND gender = ?');SET @gender = p_gender;END IF;IF p_min_age IS NOT NULL THENSET @sql = CONCAT(@sql, ' AND age >= ?');SET @min_age = p_min_age;END IF;IF p_max_age IS NOT NULL THENSET @sql = CONCAT(@sql, ' AND age <= ?');SET @max_age = p_max_age;END IF;IF p_is_active IS NOT NULL THENSET @sql = CONCAT(@sql, ' AND is_active = ?');SET @is_active = p_is_active;END IF;-- 添加排序條件SET @sql = CONCAT(@sql, ' ORDER BY user_id ASC');-- 準備并執行動態SQLPREPARE 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 THENEXECUTE 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 THENEXECUTE stmt USING @gender, @min_age, @max_age;ELSEIF p_gender IS NOT NULL AND p_min_age IS NOT NULL THENEXECUTE stmt USING @gender, @min_age;ELSEIF p_gender IS NOT NULL THENEXECUTE stmt USING @gender;ELSEEXECUTE 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
)
BEGINDECLARE 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 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Customer not found';END IF;-- 根據VIP等級設置折扣率IF v_vip_level >= 3 THENSET v_discount_rate = 0.10; -- VIP3及以上享受10%折扣ELSEIF v_vip_level = 2 THENSET 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');-- 獲取新訂單IDSET 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)
BEGINDECLARE 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_idFROM orders WHERE order_id = p_order_id;IF v_order_status IS NULL THENSET p_message = 'Order not found';SET p_item_id = -1;ELSEIF v_order_status != 'pending' THENSET 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_quantityFROM productsWHERE product_id = p_product_id;IF v_product_price IS NULL THENSET p_message = 'Product not found';SET p_item_id = -1;ELSEIF v_stock_quantity < p_quantity THENSET 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)
BEGINDECLARE 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_idFROM orders WHERE order_id = p_order_id;IF v_order_status IS NULL THENSET 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' THENSET 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_totalFROM order_itemsWHERE order_id = p_order_id;IF v_current_total IS NULL THENSET 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_levelFROM customersWHERE customer_id = v_customer_id;-- 根據VIP等級設置折扣率IF v_vip_level >= 3 THENSET v_discount_rate = 0.10; -- VIP3及以上享受10%折扣ELSEIF v_vip_level = 2 THENSET 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 ordersSET total_amount = p_total_amount,discount_amount = p_discount_amount,final_amount = p_final_amountWHERE 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))
BEGINDECLARE 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_statusFROM ordersWHERE order_id = p_order_id;IF v_final_amount IS NULL THENSET p_status = 'failed';SET p_message = 'Order not found';ELSEIF v_order_status != 'pending' THENSET p_status = v_order_status;SET p_message = CONCAT('Order is already ', v_order_status);ELSEIF p_payment_amount < v_final_amount THENSET p_status = 'failed';SET p_message = CONCAT('Insufficient payment. Required: ', v_final_amount);ELSE-- 更新訂單狀態UPDATE ordersSET status = 'paid',payment_method = p_payment_methodWHERE order_id = p_order_id;-- 更新客戶總消費金額UPDATE customersSET total_spent = total_spent + v_final_amountWHERE 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)
BEGINDECLARE 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_levelFROM customersWHERE customer_id = p_customer_id;-- 根據消費金額確定新的VIP等級IF v_total_spent >= 10000 THENSET v_new_level = 5; -- 鉆石VIPELSEIF v_total_spent >= 5000 THENSET v_new_level = 4; -- 白金VIPELSEIF v_total_spent >= 2000 THENSET v_new_level = 3; -- 黃金VIPELSEIF v_total_spent >= 1000 THENSET v_new_level = 2; -- 白銀VIPELSESET v_new_level = 1; -- 普通會員END IF;-- 如果VIP等級提升，則更新IF v_new_level > v_current_level THENUPDATE customersSET vip_level = v_new_levelWHERE 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))
BEGINDECLARE v_order_status VARCHAR(20);DECLARE v_customer_id INT;-- 獲取訂單狀態SELECT status, customer_id INTO v_order_status, v_customer_idFROM ordersWHERE order_id = p_order_id;IF v_order_status IS NULL THENSET p_status = 'error';SET p_message = 'Order not found';ELSEIF v_order_status = 'cancelled' THENSET p_status = 'cancelled';SET p_message = 'Order is already cancelled';ELSEIF v_order_status NOT IN ('pending', 'paid') THENSET p_status = v_order_status;SET p_message = CONCAT('Cannot cancel order in ', v_order_status, ' status');ELSE-- 更新訂單狀態UPDATE ordersSET status = 'cancelled'WHERE order_id = p_order_id;-- 恢復庫存UPDATE products pJOIN order_items oi ON p.product_id = oi.product_idSET p.stock_quantity = p.stock_quantity + oi.quantityWHERE 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_itemsWHERE 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',1FROM ordersWHERE order_id = p_order_id;-- 更新客戶總消費金額(減去退款)UPDATE customersSET 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 THENINSERT INTO temp_sales_reportSELECT 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_priceFROM order_items oiJOIN products p ON oi.product_id = p.product_idJOIN orders o ON oi.order_id = o.order_idWHERE o.status = 'paid'AND o.order_date BETWEEN p_start_date AND p_end_dateGROUP BY p.category, p.product_id, p.nameORDER BY p.category, total_sales DESC;ELSEINSERT INTO temp_sales_reportSELECT 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_priceFROM order_items oiJOIN products p ON oi.product_id = p.product_idJOIN orders o ON oi.order_id = o.order_idWHERE o.status = 'paid'AND o.order_date BETWEEN p_start_date AND p_end_dateAND p.category = p_categoryGROUP BY p.category, p.product_id, p.nameORDER 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_priceFROM 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;