编辑“︁PHP存储过程”︁（章节）

=== 电商平台订单处理 ===
<syntaxhighlight lang="sql">
CREATE PROCEDURE process_order(
    IN user_id INT,
    IN product_ids VARCHAR(255),
    IN quantities VARCHAR(255),
    OUT order_id INT
)
BEGIN
    -- 变量声明
    DECLARE i INT DEFAULT 0;
    DECLARE product_count INT;
    DECLARE current_pid INT;
    DECLARE current_qty INT;
    
    -- 创建订单
    INSERT INTO orders (user_id, order_date) VALUES (user_id, NOW());
    SET order_id = LAST_INSERT_ID();
    
    -- 处理订单项
    SET product_count = LENGTH(product_ids) - LENGTH(REPLACE(product_ids, ',', '')) + 1;
    
    WHILE i < product_count DO
        SET current_pid = SUBSTRING_INDEX(SUBSTRING_INDEX(product_ids, ',', i+1), ',', -1);
        SET current_qty = SUBSTRING_INDEX(SUBSTRING_INDEX(quantities, ',', i+1), ',', -1);
        
        INSERT INTO order_items (order_id, product_id, quantity)
        VALUES (order_id, current_pid, current_qty);
        
        UPDATE products SET stock = stock - current_qty WHERE id = current_pid;
        
        SET i = i + 1;
    END WHILE;
    
    -- 更新订单总价
    UPDATE orders o
    SET total_amount = (
        SELECT SUM(p.price * oi.quantity)
        FROM order_items oi
        JOIN products p ON oi.product_id = p.id
        WHERE oi.order_id = o.id
    )
    WHERE o.id = order_id;
END
</syntaxhighlight>