MySQL存储过程怎么编写？MySQL复杂逻辑的30个实现案例

DELIMITER

DELIMITER //

CREATE PROCEDURE sp_example_procedure(
    IN param1 INT,
    OUT result_param VARCHAR(255)
)
BEGIN
    -- 声明局部变量
    DECLARE var_temp INT;

    -- 设置变量值
    SET var_temp = param1 * 10;

    -- 条件判断
    IF var_temp > 100 THEN
        SET result_param = 'Value is large';
    ELSE
        SET result_param = 'Value is small';
    END IF;

    -- 执行其他SQL语句，例如插入、更新、删除或查询
    -- INSERT INTO my_table (col1) VALUES (var_temp);
    -- SELECT COUNT(*) INTO var_temp FROM another_table;

    -- 错误处理（可选但强烈推荐）
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 记录错误信息或抛出自定义错误
        ROLLBACK; -- 如果有事务，回滚
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'An error occurred during procedure execution.';
    END;

    -- 事务控制（如果需要）
    -- START TRANSACTION;
    -- 执行一系列操作...
    -- COMMIT;
    -- ROLLBACK;

END //

DELIMITER ;

CREATE PROCEDURE

IN

OUT

INOUT

BEGIN

END

DECLARE

SET

IF...THEN...ELSEIF...END IF

CASE

LOOP

WHILE

REPEAT

DECLARE HANDLER

UPDATE

DELIMITER //

CREATE PROCEDURE sp_process_loyal_users()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE user_id_var INT;
    DECLARE user_score_var DECIMAL(10, 2);

    -- 声明游标，用于遍历需要处理的用户
    DECLARE cur_users CURSOR FOR
        SELECT user_id, score
        FROM users
        WHERE last_login_date > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
          AND total_orders > 10;

    -- 声明NOT FOUND handler，用于在游标遍历结束后设置done为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_users;

    user_loop: LOOP
        FETCH cur_users INTO user_id_var, user_score_var;

        IF done THEN
            LEAVE user_loop;
        END IF;

        -- 这里是复杂的业务逻辑
        -- 例如：根据用户积分更新用户等级
        IF user_score_var >= 1000 THEN
            UPDATE users SET user_level = 'VIP' WHERE user_id = user_id_var;
            INSERT INTO user_rewards (user_id, reward_type, reward_date)
            VALUES (user_id_var, 'VIP_Bonus', CURDATE());
        ELSEIF user_score_var >= 500 THEN
            UPDATE users SET user_level = 'Gold' WHERE user_id = user_id_var;
        END IF;

        -- 还可以有其他复杂的条件判断和操作
        -- CALL sp_send_notification(user_id_var, 'Your level has been updated!');

    END LOOP user_loop;

    CLOSE cur_users;

END //

DELIMITER ;

cur_users

LOOP

FETCH

user_score_var

UPDATE

INSERT

DECLARE CONTINUE HANDLER FOR NOT FOUND

IF...ELSEIF...END IF

CASE

DELIMITER //

CREATE PROCEDURE sp_process_order_status(
    IN order_id_param INT,
    IN new_status_param VARCHAR(50),
    IN payment_method_param VARCHAR(50)
)
BEGIN
    DECLARE current_order_status VARCHAR(50);
    DECLARE customer_id_var INT;

    -- 获取当前订单状态和客户ID
    SELECT order_status, customer_id
    INTO current_order_status, customer_id_var
    FROM orders
    WHERE order_id = order_id_param;

    -- 如果订单不存在，抛出错误
    IF customer_id_var IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order not found.';
    END IF;

    -- 根据新状态和支付方式执行不同逻辑
    CASE new_status_param
        WHEN 'Paid' THEN
            -- 检查是否已支付，避免重复操作
            IF current_order_status = 'Pending Payment' THEN
                UPDATE orders SET order_status = 'Paid', payment_date = NOW() WHERE order_id = order_id_param;
                -- 根据支付方式执行不同操作
                IF payment_method_param = 'CreditCard' THEN
                    -- 调用第三方支付接口（这里是模拟）
                    CALL sp_log_payment_transaction(order_id_param, 'CreditCard', 'Success');
                ELSEIF payment_method_param = 'PayPal' THEN
                    CALL sp_log_payment_transaction(order_id_param, 'PayPal', 'Success');
                END IF;
                -- 发送支付成功通知
                CALL sp_send_notification(customer_id_var, 'Your order has been paid!');
            END IF;
        WHEN 'Shipped' THEN
            -- 确保只有已支付的订单才能发货
            IF current_order_status = 'Paid' THEN
                UPDATE orders SET order_status = 'Shipped', ship_date = NOW() WHERE order_id = order_id_param;
                CALL sp_send_notification(customer_id_var, 'Your order has been shipped!');
            ELSE
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order must be paid before shipping.';
            END IF;
        WHEN 'Cancelled' THEN
            -- 取消订单，可能需要退款
            IF current_order_status IN ('Pending Payment', 'Paid') THEN
                UPDATE orders SET order_status = 'Cancelled', cancel_date = NOW() WHERE order_id = order_id_param;
                -- 如果已支付，处理退款逻辑
                IF current_order_status = 'Paid' THEN
                    CALL sp_process_refund(order_id_param);
                END IF;
                CALL sp_send_notification(customer_id_var, 'Your order has been cancelled.');
            END IF;
        ELSE
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid new status.';
    END CASE;

END //

DELIMITER ;

CASE

new_status_param

IF

Paid

Shipped

DELIMITER //

CREATE PROCEDURE sp_transfer_funds(
    IN from_account_id INT,
    IN to_account_id INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 捕获任何SQL异常，回滚事务
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Funds transfer failed due to an internal error.';
    END;

    -- 检查转账金额是否有效
    IF amount <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer amount must be positive.';
    END IF;

    -- 检查账户是否存在
    IF NOT EXISTS(SELECT 1 FROM accounts WHERE account_id = from_account_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Source account not found.';
    END IF;

    IF NOT EXISTS(SELECT 1 FROM accounts WHERE account_id = to_account_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Destination account not found.';
    END IF;

    -- 开始事务
    START TRANSACTION;

    -- 1. 从源账户扣款
    UPDATE accounts
    SET balance = balance - amount
    WHERE account_id = from_account_id;

    -- 检查是否余额不足
    IF (SELECT balance FROM accounts WHERE account_id = from_account_id) < 0 THEN
        ROLLBACK; -- 余额不足，回滚事务
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds in source account.';
    END IF;

    -- 2. 向目标账户增加款项
    UPDATE accounts
    SET balance = balance + amount
    WHERE account_id = to_account_id;

    -- 3. 记录交易日志
    INSERT INTO transactions (from_account_id, to_account_id, amount, transaction_date)
    VALUES (from_account_id, to_account_id, amount, NOW());

    -- 如果所有操作都成功，提交事务
    COMMIT;

END //

DELIMITER ;

START TRANSACTION

COMMIT

ROLLBACK

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SIGNAL SQLSTATE