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