# 示例1：用户注册存储过程
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_register_user(
-> IN p_name VARCHAR(50),
-> IN p_email VARCHAR(100),
-> IN p_password VARCHAR(100),
-> OUT p_user_id INT
-> )
BEGIN
INSERT INTO fgedu_users (name, email, password, created_at)
VALUES (p_name, p_email, MD5(p_password), NOW());
SET p_user_id = LAST_INSERT_ID();
— 创建用户账户
INSERT INTO fgedu_accounts (user_id, balance, created_at)
VALUES (p_user_id, 0.00, NOW());
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 调用注册存储过程
MariaDB [fgedudb]> CALL fgedu_register_user(‘John Doe’, ‘john@fgedu.net.cn’, ‘password123’, @user_id);
Query OK, 2 rows affected (0.01 sec)
MariaDB [fgedudb]> SELECT @user_id;
+———+
| @user_id |
+———+
| 11 |
+———+
# 示例2：批量插入数据存储过程
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_batch_insert(IN p_count INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= p_count DO
INSERT INTO fgedu_test (name, value)
VALUES (CONCAT(‘test_’, i), i);
SET i = i + 1;
END WHILE;
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 调用批量插入存储过程
MariaDB [fgedudb]> CALL fgedu_batch_insert(100);
Query OK, 1 row affected (0.05 sec)
# 验证数据
MariaDB [fgedudb]> SELECT COUNT(*) FROM fgedu_test;
+———-+
| COUNT(*) |
+———-+
| 100 |
+———-+

# 示例：订单处理存储过程
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_process_order(
-> IN p_user_id INT,
-> IN p_product_id INT,
-> IN p_quantity INT,
-> OUT p_order_id INT,
-> OUT p_status VARCHAR(20)
-> )
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SET p_status = ‘ERROR’;
END;
DECLARE v_product_price DECIMAL(10,2);
DECLARE v_stock INT;
DECLARE v_total_amount DECIMAL(10,2);
START TRANSACTION;
— 检查商品库存
SELECT price, stock INTO v_product_price, v_stock
FROM fgedu_products WHERE id = p_product_id;
IF v_stock < p_quantity THEN
SET p_status = ‘OUT_OF_STOCK’;
ROLLBACK;
RETURN;
END IF;
— 计算总金额
SET v_total_amount = v_product_price * p_quantity;
— 检查用户余额
IF (SELECT balance FROM fgedu_accounts WHERE user_id = p_user_id) < v_total_amount THEN
SET p_status = ‘INSUFFICIENT_BALANCE’;
ROLLBACK;
RETURN;
END IF;
— 扣减库存
UPDATE fgedu_products SET stock = stock – p_quantity WHERE id = p_product_id;
— 扣减用户余额
UPDATE fgedu_accounts SET balance = balance – v_total_amount WHERE user_id = p_user_id;
— 创建订单
INSERT INTO fgedu_orders (user_id, total_amount, status, created_at)
VALUES (p_user_id, v_total_amount, ‘PENDING’, NOW());
SET p_order_id = LAST_INSERT_ID();
— 创建订单明细
INSERT INTO fgedu_order_items (order_id, product_id, quantity, price)
VALUES (p_order_id, p_product_id, p_quantity, v_product_price);
— 记录交易
INSERT INTO fgedu_transactions (user_id, order_id, amount, type, created_at)
VALUES (p_user_id, p_order_id, v_total_amount, ‘PURCHASE’, NOW());
COMMIT;
SET p_status = ‘SUCCESS’;
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 调用订单处理存储过程
MariaDB [fgedudb]> CALL fgedu_process_order(1, 1, 2, @order_id, @status);
Query OK, 1 row affected (0.01 sec)
MariaDB [fgedudb]> SELECT @order_id, @status;
+———-+——–+
| @order_id | @status |
+———-+——–+
| 1 | SUCCESS |
+———-+——–+

# 启用存储过程调试
MariaDB [fgedudb]> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
# 创建调试存储过程
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_debug_demo()
BEGIN
DECLARE v_counter INT DEFAULT 0;
DECLARE v_max INT DEFAULT 100000;
DECLARE v_start_time TIMESTAMP;
SET v_start_time = NOW();
WHILE v_counter < v_max DO
INSERT INTO fgedu_debug (value, created_at) VALUES (v_counter, NOW());
SET v_counter = v_counter + 1;
END WHILE;
SELECT TIMESTAMPDIFF(SECOND, v_start_time, NOW()) AS execution_time;
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 测试调试存储过程
MariaDB [fgedudb]> CALL fgedu_debug_demo();
+—————-+———–+
| execution_time |
+—————-+———–+
| 15 |
+—————-+———–+
# 优化存储过程 – 使用批量插入
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE fgedu_optimized_demo()
BEGIN
DECLARE v_counter INT DEFAULT 0;
DECLARE v_max INT DEFAULT 100000;
DECLARE v_start_time TIMESTAMP;
DECLARE v_values TEXT DEFAULT ”;
SET v_start_time = NOW();
WHILE v_counter < v_max DO
SET v_values = CONCAT(v_values, ‘(‘, v_counter, ‘, NOW())’);
IF v_counter < v_max - 1 THEN
SET v_values = CONCAT(v_values, ‘,’);
END IF;
SET v_counter = v_counter + 1;
END WHILE;
SET @sql = CONCAT(‘INSERT INTO fgedu_debug (value, created_at) VALUES ‘, v_values);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT TIMESTAMPDIFF(SECOND, v_start_time, NOW()) AS execution_time;
END //
DELIMITER;
Query OK, 0 rows affected (0.00 sec)
# 测试优化后的存储过程
MariaDB [fgedudb]> CALL fgedu_optimized_demo();
+—————-+———–+
| execution_time |
+—————-+———–+
| 2 |
+—————-+———–+