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; -- and 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 ;

-- Parameters
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 ;