Stored Procedures and Functions

Article · 3:00

Routines that run inside the server, their parameter modes, and an honest account of when to use them.

A stored procedure is a named block of SQL with parameters, called with CALL. Parameters are IN (the default, passed in), OUT (written back to the caller) or INOUT (both). Procedures can contain control flow - IF, CASE, WHILE, REPEAT, LOOP - and declare local variables and cursors, which makes them a small procedural language living inside the database.

A stored function returns a single value and can be used inside a query like a built-in. It must be declared DETERMINISTIC, NOT DETERMINISTIC or NO SQL, and the declaration is not cosmetic: a function the optimiser believes is deterministic can be evaluated once instead of once per row, and lying about it produces wrong results.

Because routines use ; internally, you must change the statement delimiter while defining one - the DELIMITER $$ ritual that confuses everybody the first time.

The honest trade-off: routines put logic next to the data, which means less round-tripping and one place to enforce a rule across several applications. They are also harder to version-control, harder to test, invisible to your application's error tracking, and written in a language most of your team does not know. A good default is to use them for data-intensive operations where moving the rows to the application is the cost, and to keep business logic in the application.

-- The delimiter has to change, because the body contains semicolons.
DELIMITER $$

CREATE PROCEDURE place_order(
  IN  p_customer_id INT UNSIGNED,
  IN  p_product_id  INT UNSIGNED,
  IN  p_quantity    SMALLINT UNSIGNED,
  OUT p_order_id    INT UNSIGNED,
  OUT p_status      VARCHAR(40)
)
BEGIN
  DECLARE v_price DECIMAL(10,2);
  DECLARE v_stock SMALLINT UNSIGNED;

  -- Any SQL error rolls the whole thing back and reports it.
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_status = 'failed';
  END;

  START TRANSACTION;

  SELECT price, stock INTO v_price, v_stock
  FROM products WHERE id = p_product_id FOR UPDATE;

  IF v_stock < p_quantity THEN
    SET p_status = 'out of stock';
    ROLLBACK;
  ELSE
    INSERT INTO orders (customer_id, reference, status)
    VALUES (p_customer_id, CONCAT('ORD', LPAD(FLOOR(RAND()*1e7), 7, '0')), 'pending');

    SET p_order_id = LAST_INSERT_ID();

    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
    VALUES (p_order_id, p_product_id, p_quantity, v_price);

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

    SET p_status = 'ok';
    COMMIT;
  END IF;
END$$

-- A function returns one value and is usable inside a query. The
-- determinism declaration is not cosmetic: the optimiser believes it.
CREATE FUNCTION order_total(p_order_id INT UNSIGNED)
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE v_total DECIMAL(10,2);
  SELECT COALESCE(SUM(quantity * unit_price), 0) INTO v_total
  FROM order_items WHERE order_id = p_order_id;
  RETURN v_total;
END$$

DELIMITER ;

CALL place_order(1, 1, 2, @order_id, @status);
SELECT @order_id, @status, order_total(@order_id);