存储过程

DELIMITER //
CREATE PROCEDURE proc_name ([IN|OUT|INOUT] param type, ...)
BEGIN
  -- SQL statements
END //
DELIMITER ;

CALL proc_name(args);

DELIMITER //
CREATE PROCEDURE sp_create_order(
  IN p_user_id BIGINT,
  IN p_product_id BIGINT,
  IN p_quantity INT,
  OUT p_order_no VARCHAR(32)
)
BEGIN
  DECLARE v_price DECIMAL(10,2);
  DECLARE v_stock INT;

  -- 查询商品价格和库存
  SELECT price, stock INTO v_price, v_stock
  FROM products WHERE id = p_product_id FOR UPDATE;

  -- 检查库存
  IF v_stock < p_quantity THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
  END IF;

  -- 生成订单号
  SET p_order_no = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000), 4, '0'));

  -- 扣减库存
  UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

  -- 创建订单
  INSERT INTO orders (order_no, user_id, total_amount, status)
  VALUES (p_order_no, p_user_id, v_price * p_quantity, 0);
END //
DELIMITER ;

-- 调用并获取输出参数
CALL sp_create_order(1001, 2001, 2, @order_no);
SELECT @order_no;

-- 查看所有存储过程
SHOW PROCEDURE STATUS WHERE Db = 'ecommerce';

-- 查看存储过程定义
SHOW CREATE PROCEDURE sp_create_order;

-- 删除存储过程
DROP PROCEDURE IF EXISTS sp_create_order;

DELIMITER //
CREATE PROCEDURE sp_batch_expire_orders()
BEGIN
  DECLARE v_affected INT DEFAULT 1;

  -- 分批处理，每次 1000 条
  WHILE v_affected > 0 DO
    UPDATE orders
    SET status = -1, updated_at = NOW()
    WHERE status = 0
      AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
    LIMIT 1000;

    SET v_affected = ROW_COUNT();
  END WHILE;
END //
DELIMITER ;