Stored procedures and functions: MySQL

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrite, call and drop stored procedures and functions, and decide when logic belongs in the database.

Logic that lives in the database

Until now you have sent individual statements. A stored routine is a program saved in the database and run by name. There are two kinds:

A stored procedure is run with CALL. It can take parameters, run several statements, return results and report values through OUT parameters.
A stored function returns exactly one value and is used inside queries like the built-in functions (ROUND, CONCAT).

Routines are useful for logic that several applications share, for multi-step operations that should run on the server in one round trip, and for controlling access (a user may be allowed to CALL a procedure without having rights on the tables beneath it).

The DELIMITER trick

A routine body contains semicolons. The mysql client would send the text up to the first one and break your CREATE PROCEDURE in half. The client command DELIMITER temporarily changes the terminator to something else, such as //:

text

DELIMITER //
CREATE PROCEDURE ... BEGIN ... END //
DELIMITER ;

DELIMITER is understood by the client (the mysql command, Workbench), not by the server, so you do not use it when creating routines from application code.

A first procedure

sql

USE shop;
DROP PROCEDURE IF EXISTS customer_orders;
DELIMITER //
CREATE PROCEDURE customer_orders(IN p_customer_id INT)
BEGIN
  SELECT o.id, o.order_date, o.status,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = p_customer_id
  GROUP BY o.id, o.order_date, o.status
  ORDER BY o.order_date;
END //
DELIMITER ;

Run it with CALL, passing the customer id:

sql

CALL customer_orders(1);

result

+----+------------+-----------+--------+
| id | order_date | status    | total  |
+----+------------+-----------+--------+
|  1 | 2024-01-05 | delivered |  84.89 |
|  2 | 2024-02-14 | delivered |  89.50 |
| 11 | 2024-04-12 | delivered | 109.45 |
| 20 | 2024-07-19 | shipped   | 218.74 |
+----+------------+-----------+--------+

The parameter is named p_customer_id. A p_ prefix is a handy habit: if a parameter had the same name as a column, MySQL would use the column and your filter would silently do the wrong thing.

OUT parameters and variables

A procedure can hand values back through OUT parameters. Inside the body, DECLARE creates local variables (declarations must come first), and SELECT ... INTO fills a variable:

sql

DROP PROCEDURE IF EXISTS order_summary;
DELIMITER //
CREATE PROCEDURE order_summary(IN p_order_id INT, OUT p_lines INT, OUT p_total DECIMAL(10,2))
BEGIN
  SELECT COUNT(*), COALESCE(SUM(quantity * unit_price), 0)
  INTO p_lines, p_total
  FROM order_items
  WHERE order_id = p_order_id;
END //
DELIMITER ;

CALL order_summary(11, @lines, @total);
SELECT @lines AS lines_in_order, @total AS order_total;

result

+----------------+-------------+
| lines_in_order | order_total |
+----------------+-------------+
|              3 |      109.45 |
+----------------+-------------+

The @lines and @total are user variables (the next lesson covers them), used here to catch the outputs.

Control flow

Routines support IF, CASE, WHILE, REPEAT and LOOP. This procedure adds stock but refuses nonsense input, and uses SIGNAL to raise an error of its own:

sql

DROP PROCEDURE IF EXISTS add_stock;
DELIMITER //
CREATE PROCEDURE add_stock(IN p_product_id INT, IN p_units INT)
BEGIN
  DECLARE v_exists INT DEFAULT 0;
  IF p_units <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Units must be positive';
  END IF;
  SELECT COUNT(*) INTO v_exists FROM products WHERE id = p_product_id;
  IF v_exists = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown product';
  END IF;
  UPDATE products SET stock = stock + p_units WHERE id = p_product_id;
  SELECT id, name, stock FROM products WHERE id = p_product_id;
END //
DELIMITER ;

CALL add_stock(15, 40);

result

+----+--------------------+-------+
| id | name               | stock |
+----+--------------------+-------+
| 15 | Puzzle 1000 Pieces |    40 |
+----+--------------------+-------+

The error paths:

sql

CALL add_stock(15, -5);

result

ERROR 1644 (45000): Units must be positive

sql

CALL add_stock(999, 5);

result

ERROR 1644 (45000): Unknown product

Handling errors

A HANDLER catches a condition so the routine can respond instead of failing. A common pattern wraps work in a transaction and rolls back on any SQL exception:

sql

DROP PROCEDURE IF EXISTS place_order;
DELIMITER //
CREATE PROCEDURE place_order(IN p_customer_id INT, IN p_product_id INT, IN p_quantity INT, OUT p_new_order_id INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_new_order_id = NULL;
  END;
  START TRANSACTION;
    INSERT INTO orders (id, customer_id, order_date, status)
      SELECT COALESCE(MAX(id), 0) + 1, p_customer_id, '2024-08-01', 'pending' FROM orders;
    SET p_new_order_id = (SELECT MAX(id) FROM orders);
    INSERT INTO order_items (order_id, product_id, quantity, unit_price)
      SELECT p_new_order_id, id, p_quantity, price FROM products WHERE id = p_product_id;
  COMMIT;
END //
DELIMITER ;

CALL place_order(2, 12, 3, @new_id);
SELECT @new_id AS new_order_id;
CALL place_order(999, 12, 3, @bad_id);
SELECT @bad_id AS failed_order_id;

result

+--------------+
| new_order_id |
+--------------+
|           21 |
+--------------+
+-----------------+
| failed_order_id |
+-----------------+
|            NULL |
+-----------------+

(In the sample database orders.id is not AUTO_INCREMENT, so the procedure works out the next number itself. In a real table you would let MySQL generate it and read LAST_INSERT_ID().) The second call used a customer that does not exist, which violates the foreign key. The handler rolled back the transaction and returned NULL.

Stored functions

A function takes parameters and RETURNS one value. Declare it DETERMINISTIC if the same input always gives the same output, or READS SQL DATA if it reads tables:

sql

DROP FUNCTION IF EXISTS price_with_gst;
DELIMITER //
CREATE FUNCTION price_with_gst(p_price DECIMAL(10,2), p_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(p_price * (1 + p_rate / 100), 2);
END //
DELIMITER ;

SELECT name, price, price_with_gst(price, 18) AS with_gst FROM products WHERE category_id = 5;

result

+---------------------+-------+----------+
| name                | price | with_gst |
+---------------------+-------+----------+
| Building Blocks Set | 49.99 |    58.99 |
| Puzzle 1000 Pieces  | 19.99 |    23.59 |
+---------------------+-------+----------+

Because functions can be used inside any query, a slow function runs once per row. Keep them simple.

Managing routines

sql

SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'shop' ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

result

+-----------------+--------------+
| ROUTINE_NAME    | ROUTINE_TYPE |
+-----------------+--------------+
| price_with_gst  | FUNCTION     |
| add_stock       | PROCEDURE    |
| customer_orders | PROCEDURE    |
| order_summary   | PROCEDURE    |
| place_order     | PROCEDURE    |
+-----------------+--------------+

SHOW PROCEDURE STATUS gives more detail, SHOW CREATE PROCEDURE name\G displays the source, and DROP PROCEDURE IF EXISTS name; removes a routine. To change a routine, drop it and create it again.

Pros and cons

Advantages: less network traffic for multi-statement work, one tested implementation shared by every client, and tighter permission control.

Disadvantages: logic in the database is harder to test, version-control and debug than application code. It can tie you to MySQL's dialect and puts more CPU load on the database server, which is the hardest part to scale. Many teams keep routines for data-heavy operations and maintenance, and leave business rules in application code.

Common mistakes

Forgetting DELIMITER in the client, so the routine is cut in half.
Parameter names that match column names, quietly turning filters into no-ops.
Declaring variables after other statements. DECLARE must come first in a block.
Writing functions that run queries on each row of a big result.
Using OUT parameters from application code without checking your driver supports them.

Exercise

Write a function full_name(first, last) that returns the two names joined with a space and the last name in capitals (for example Asha VERMA). Use it to list the first four customers. Then write a procedure low_stock(IN limit_units INT) that lists products with stock below the limit, and call it with 40.

Show one possible answer

sql

DROP FUNCTION IF EXISTS full_name;
DROP PROCEDURE IF EXISTS low_stock;
DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50)) RETURNS VARCHAR(110) DETERMINISTIC
BEGIN
  RETURN CONCAT(p_first, ' ', UPPER(p_last));
END //
CREATE PROCEDURE low_stock(IN p_limit INT)
BEGIN
  SELECT id, name, stock FROM products WHERE stock < p_limit ORDER BY stock, id;
END //
DELIMITER ;

SELECT full_name(first_name, last_name) AS customer FROM customers WHERE id <= 4;
CALL low_stock(40);

result

+--------------+
| customer     |
+--------------+
| Asha VERMA   |
| Ben CARTER   |
| Chloe DUBOIS |
| Daniel KIM   |
+--------------+
+----+-----------------------------+-------+
| id | name                        | stock |
+----+-----------------------------+-------+
|  4 | Noise-Cancelling Headphones |    25 |
| 14 | Building Blocks Set         |    35 |
+----+-----------------------------+-------+

Key points

A stored procedure is a named block of SQL saved in the database and run with CALL; a stored function returns one value and can be used inside queries.
Use DELIMITER to change the statement terminator while typing a routine body that contains semicolons.
Parameters are IN (default), OUT or INOUT. Inside, DECLARE local variables and use IF, CASE, WHILE and loops.
SIGNAL raises your own errors and DECLARE ... HANDLER catches errors. Keep routines small; business logic that changes often is usually easier to maintain in application code.

Logic that lives in the database

The DELIMITER trick

A first procedure

OUT parameters and variables

Control flow

Handling errors

Stored functions

Managing routines

Pros and cons

Common mistakes

Exercise

Key points

Check yourself