Stored Procedures

-- PostgreSQL: stored procedure to process an order
CREATE OR REPLACE PROCEDURE process_order(
  p_user_id INT,
  p_product_id INT,
  p_quantity INT,
  p_payment_method VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_price DECIMAL(10,2);
  v_stock INT;
  v_order_id INT;
  v_total DECIMAL(10,2);
BEGIN
  -- Get product info
  SELECT price, stock INTO v_price, v_stock
  FROM products WHERE id = p_product_id FOR UPDATE;

  -- Check stock
  IF v_stock < p_quantity THEN
    RAISE EXCEPTION 'Insufficient stock for product %', p_product_id;
  END IF;

  v_total := v_price * p_quantity;

  -- Insert order
  INSERT INTO orders (user_id, total_amount, status)
  VALUES (p_user_id, v_total, 'pending')
  RETURNING id INTO v_order_id;

  -- Insert payment
  INSERT INTO payments (order_id, amount, payment_method, paid_at)
  VALUES (v_order_id, v_total, p_payment_method, NOW());

  -- Update stock
  UPDATE products SET stock = stock - p_quantity WHERE id = p_product_id;

  -- Confirm order
  UPDATE orders SET status = 'processing' WHERE id = v_order_id;
END;
$$;

-- Call the stored procedure
CALL process_order(1, 1, 2, 'credit_card');
-- '2 Laptops ordered by user 1, paid by credit card'

-- MySQL stored procedure syntax
DELIMITER //
CREATE PROCEDURE get_user_orders(IN p_user_id INT)
BEGIN
  SELECT o.id, o.total_amount, o.status
  FROM orders o WHERE o.user_id = p_user_id;
END //
DELIMITER ;

CALL get_user_orders(1);