Stored Procedures and Functions

Procedures versus functions#

A function returns a single value and can be used inside a query. A procedure is called with CALL, can have IN, OUT, and INOUT parameters, can return result sets, and can change data freely.

The delimiter dance#

The client splits on ;, but a routine body contains semicolons. Change the delimiter while defining one:

DELIMITER $$

CREATE PROCEDURE sp_restock(IN p_product_id INT, IN p_qty INT)
BEGIN
  UPDATE products SET stock = stock + p_qty WHERE id = p_product_id;
  SELECT id, name, stock FROM products WHERE id = p_product_id;
END$$

DELIMITER ;

CALL sp_restock(3, 25);

DELIMITER is a client command, not SQL. Application drivers don't need it.

Variables and parameters#

DELIMITER $$
CREATE PROCEDURE sp_order_summary(IN p_order_id BIGINT, OUT p_total DECIMAL(12,2))
BEGIN
  DECLARE v_count INT DEFAULT 0;

  SELECT COUNT(*), COALESCE(SUM(qty * unit_price), 0)
  INTO v_count, p_total
  FROM order_items WHERE order_id = p_order_id;

  IF v_count = 0 THEN
    SET p_total = NULL;
  END IF;
END$$
DELIMITER ;

CALL sp_order_summary(1, @t);
SELECT @t;

DECLARE must come first in a block. Prefix parameter and variable names (p_, v_). If a parameter has the same name as a column, the parameter silently wins inside your query, a nasty bug.

Functions#

DELIMITER $$
CREATE FUNCTION fn_line_total(p_qty INT, p_price DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
  RETURN p_qty * p_price;
END$$
DELIMITER ;

SELECT order_id, fn_line_total(qty, unit_price) FROM order_items;

DETERMINISTIC promises the same inputs give the same output. If binary logging is on and you don't declare the function's nature, MySQL may refuse to create it. A function called per row inside a query runs that many times, so heavy logic in a function on a big table is slow.

Execution model#

CALL sp_x(args) ─▶ parse (cached per connection) ─▶ run statements in order
                                                    inside the caller's session

Routines run in the caller's session and transaction by default, with the definer's or invoker's privileges depending on SQL SECURITY.

Should logic live here?#

Pros: fewer round trips, one place for rules enforced for every client, controlled access (grant EXECUTE only). Cons: harder to version-control, test, and debug; business logic is split between app and database; MySQL's procedural language is basic. I keep routines for data-heavy batch work and strict invariants, and leave the rest in application code.

Common mistakes#

• Naming a parameter the same as a column.
• Forgetting DELIMITER in the CLI.
• Calling a slow function per row on millions of rows.
• Not keeping routine definitions in source control.

Practice#

Write a procedure sp_place_order(IN p_customer INT, IN p_product INT, IN p_qty INT) that inserts an order and one item, reading the price from products. Then write a function fn_customer_total(customer_id) and use it in a SELECT over all customers.