Cursors and Handlers

Cursors, Conditions, and Handlers

A cursor walks a result set row by row, forward only. A handler reacts to a condition (an error number, an SQLSTATE, SQLEXCEPTION, SQLWARNING or NOT FOUND) and then continues (CONTINUE) or leaves the block (EXIT). SIGNAL raises your own condition, and RESIGNAL re-raises the one being handled. Declare variables, then cursors, then handlers. place_order walks a JSON cart (JSON_TABLE) with the checkout pattern of Transaction Statements:

place_order: a cursor, two handlers, SIGNAL and a transactionSQL

DELIMITER //
CREATE PROCEDURE place_order(IN p_customer INT UNSIGNED, IN p_cart JSON,
                             OUT p_order INT UNSIGNED)
BEGIN
  DECLARE v_done BOOL DEFAULT FALSE;
  DECLARE v_product, v_qty INT UNSIGNED;
  DECLARE cart CURSOR FOR SELECT id, qty FROM JSON_TABLE(p_cart, '$[*]'
    COLUMNS (id INT UNSIGNED PATH '$.id', qty INT UNSIGNED PATH '$.qty')) AS c;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
  START TRANSACTION;
  INSERT INTO orders (customer_id, ordered_at) VALUES (p_customer, NOW());
  SET p_order = LAST_INSERT_ID();
  OPEN cart;
  cart_loop: LOOP
    FETCH cart INTO v_product, v_qty;
    IF v_done THEN LEAVE cart_loop; END IF;
    UPDATE products SET stock = stock - v_qty WHERE id = v_product AND stock >= v_qty;
    IF ROW_COUNT() = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock', MYSQL_ERRNO = 5001;
    END IF;
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
      SELECT p_order, id, v_qty, price FROM products WHERE id = v_product;
  END LOOP;
  CLOSE cart;
  COMMIT;
END //
DELIMITER ;
CALL place_order(7, '[{"id": 5, "qty": 2}, {"id": 7, "qty": 1}]', @id);
CALL place_order(7, '[{"id": 1, "qty": 1}, {"id": 3, "qty": 1}]', @id);
SELECT @id AS order_id, order_total(@id) AS total, (SELECT COUNT(*) FROM orders) AS orders,
  (SELECT GROUP_CONCAT(stock ORDER BY id) FROM products WHERE id IN (1, 5, 7)) AS stock_1_5_7;

Output

ERROR 5001 (45000): Out of stock
+----------+-------+--------+-------------+
| order_id | total | orders | stock_1_5_7 |
+----------+-------+--------+-------------+
|       10 | 80.50 |     10 | 25,5,59     |
+----------+-------+--------+-------------+

Order 10 went through. The second cart took a copy of product 1 and an order row before finding product 3 sold out, and the EXIT handler rolled both back. Without RESIGNAL the caller would see success; PHP receives a PDOException with this SQLSTATE and message (Error Modes and Exceptions), and @id keeps its old value. In a handler, GET STACKED DIAGNOSTICS reads the error for logging. Beware: NOT FOUND also fires on a SELECT ... INTO that finds nothing, and START TRANSACTION inside a routine commits any transaction the caller had open.

MENU