mysql事务和存储过程结合使用方法

DELIMITER //

CREATE PROCEDURE `your_procedure_name`(
    IN param1 INT,
    IN param2 VARCHAR(255)
)
BEGIN
    -- 声明一个变量来存储错误信息或状态
    DECLARE exit_status BOOLEAN DEFAULT FALSE;
    DECLARE error_message TEXT;

    -- 声明一个退出处理器，当发生SQL异常时，它会被触发
    -- 这个处理器会在任何SQLSTATE '45000'（自定义错误）或SQLEXCEPTION（所有其他SQL错误）时执行
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET exit_status = TRUE; -- 标记为有错误发生
        GET DIAGNOSTICS CONDITION 1 error_message = MESSAGE_TEXT; -- 获取错误信息
        -- 可以选择在这里记录错误到日志表
        -- INSERT INTO error_log (procedure_name, error_text, timestamp) VALUES ('your_procedure_name', error_message, NOW());
    END;

    START TRANSACTION; -- 显式开始一个事务

    -- 假设这是你的第一个操作
    INSERT INTO `table1` (`col1`, `col2`) VALUES (param1, param2);

    -- 检查上一个操作是否成功，尽管有HANDLER，但这种显式检查在复杂逻辑中依然有用
    IF NOT exit_status THEN
        -- 假设这是你的第二个操作
        UPDATE `table2` SET `status` = 'processed' WHERE `id` = param1;
    END IF;

    -- 可以在这里加入更多操作...

    -- 如果在事务执行过程中没有发生错误（exit_status 仍为 FALSE）
    IF NOT exit_status THEN
        COMMIT; -- 提交所有更改
        SELECT 'Success' AS status_message;
    ELSE
        ROLLBACK; -- 回滚所有更改
        -- 返回错误信息，或者抛出自定义错误
        -- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Transaction failed: ', error_message);
        SELECT CONCAT('Failed: ', error_message) AS status_message;
    END IF;

END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE `process_order_with_error_handling`(
    IN p_order_id INT,
    IN p_product_id INT,
    IN p_quantity INT
)
BEGIN
    DECLARE v_error_code CHAR(5) DEFAULT '00000';
    DECLARE v_error_message TEXT;
    DECLARE v_rollback_needed BOOLEAN DEFAULT FALSE;

    -- 声明一个处理器来捕获SQL异常
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            v_error_code = RETURNED_SQLSTATE,
            v_error_message = MESSAGE_TEXT;
        SET v_rollback_needed = TRUE;
        -- 可以在这里记录更详细的错误信息
        -- INSERT INTO system_log (log_type, message, timestamp) VALUES ('ERROR', CONCAT('Order processing failed: ', v_error_message), NOW());
    END;

    START TRANSACTION;

    -- 1. 插入订单详情
    INSERT INTO `order_details` (`order_id`, `product_id`, `quantity`, `price`)
    VALUES (p_order_id, p_product_id, p_quantity, (SELECT `unit_price` FROM `products` WHERE `id` = p_product_id));

    -- 如果上一步出现错误，v_rollback_needed 已经为 TRUE
    IF NOT v_rollback_needed THEN
        -- 2. 更新产品库存
        UPDATE `products` SET `stock` = `stock` - p_quantity WHERE `id` = p_product_id;

        -- 检查库存是否足够，如果更新后库存为负，则视为业务错误
        IF ROW_COUNT() = 0 OR (SELECT `stock` FROM `products` WHERE `id` = p_product_id) < 0 THEN
            SET v_rollback_needed = TRUE;
            SET v_error_message = 'Insufficient stock or product not found.';
            -- 也可以使用 SIGNAL SQLSTATE '45000' 来抛出自定义错误，并由HANDLER捕获
            -- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock or product not found.';
        END IF;
    END IF;

    -- 3. 更新订单主表状态
    IF NOT v_rollback_needed THEN
        UPDATE `orders` SET `status` = 'processed', `updated_at` = NOW() WHERE `id` = p_order_id;
    END IF;

    -- 根据错误标志决定提交或回滚
    IF v_rollback_needed THEN
        ROLLBACK;
        -- 返回错误信息或状态
        SELECT 'FAILURE' AS status, v_error_code AS error_code, v_error_message AS message;
    ELSE
        COMMIT;
        SELECT 'SUCCESS' AS status, 'Order processed successfully.' AS message;
    END IF;

END //

DELIMITER ;