Control Flow, Cursors, and Error Handlers

Branching#

IF p_qty <= 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'qty must be positive';
ELSEIF p_qty > 1000 THEN
  SET p_qty = 1000;
ELSE
  SET p_qty = p_qty;
END IF;

CASE works as a statement inside routines too. SIGNAL raises your own error with a custom message. It's how a routine rejects bad input.

Loops#

DELIMITER $$
CREATE PROCEDURE sp_fill_numbers(IN p_max INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= p_max DO
    INSERT INTO numbers (n) VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

REPEAT ... UNTIL cond END REPEAT checks at the end, so it always runs once. A plain LOOP runs forever until you LEAVE label. Always make sure the counter changes, or you've written an infinite loop on the server.

Cursors#

A cursor lets you walk a result set one row at a time.

DELIMITER $$
CREATE PROCEDURE sp_flag_big_customers()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_spent DECIMAL(12,2);

  DECLARE cur CURSOR FOR
    SELECT customer_id, SUM(total) FROM v_order_totals GROUP BY customer_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_spent;
    IF done = 1 THEN LEAVE read_loop; END IF;
    IF v_spent > 1000 THEN
      UPDATE customers SET tier = 'gold' WHERE id = v_id;
    END IF;
  END LOOP;
  CLOSE cur;
END$$
DELIMITER ;

OPEN ▶ FETCH ▶ row? ──yes──▶ process ──┐
              │ no                      │
              └▶ NOT FOUND handler sets done ▶ LEAVE ▶ CLOSE

Declaration order is strict: variables, then cursors, then handlers.

Error handlers#

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  ROLLBACK;
  RESIGNAL;
END;

EXIT leaves the block, CONTINUE carries on after handling. Combining a handler with a transaction gives you "all or nothing" inside the routine. RESIGNAL passes the original error back to the caller instead of hiding it.

Cursors are the slow path#

That loop above runs a statement per row. The same job as a single set-based statement:

UPDATE customers c
JOIN (SELECT customer_id, SUM(total) AS spent FROM v_order_totals GROUP BY customer_id) t
  ON t.customer_id = c.id
SET c.tier = 'gold'
WHERE t.spent > 1000;

It's typically orders of magnitude faster, because the engine handles all rows in one pass. Use a cursor only when each row needs logic that truly can't be expressed in set form, such as calling another procedure per row.

Common mistakes#

• Reaching for a cursor first instead of thinking in sets.
• Declaring things in the wrong order and getting a syntax error.
• Swallowing errors with a handler that does nothing.
• Writing loops with no way out.

Practice#

Write the cursor version of "flag customers who spent over 1000" and the single-UPDATE version. Run both on a table you've filled with 100,000 generated rows (use the recursive CTE from Module 5) and compare the times. Then add an exit handler that rolls back and re-raises on any error.