Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 75 ยท Programmable Objects

Stored Procedures and Functions

Write MySQL stored procedures and functions: DELIMITER, IN, OUT and INOUT parameters, variables, IF, CASE and loops, DETERMINISTIC, SQL SECURITY and calling them from code.

A stored routine is a named block of SQL and procedural code that lives inside the database. You create it once, and any client can run it by name. MySQL has two kinds: a procedure, which you run with CALL and which can return result sets and output parameters, and a function, which returns a single value and can be used inside a query like UPPER() or ROUND().

Routines can save network round trips (one call instead of ten statements), keep a rule in one place and let you grant access to an operation without granting access to the tables behind it. They also have costs, which we cover at the end, and many teams keep business logic in application code instead. Know both sides. Routines are a MySQL server feature that SQLite does not have, so the routine code is read-only here. The live examples show the same logic as plain SQL, which is also what you should reach for first.

Procedure or function?

Procedure Run with CALL. Can change data and return rows.
Function Returns one value. Use it inside a query.

Functions can be used in SELECT, procedures cannot.

1 / 2

Your first procedure and the DELIMITER trick

A routine body contains semicolons, but the mysql client also ends statements at a semicolon, so it would cut your CREATE PROCEDURE in half. The fix is DELIMITER, a client command (not SQL) that changes the terminator while you define the routine:

SQL
DELIMITER //

CREATE PROCEDURE add_order_item(IN p_order_id INT, IN p_product_id INT, IN p_qty INT)
BEGIN
  DECLARE v_price DECIMAL(10,2);

  SELECT price INTO v_price FROM products WHERE id = p_product_id;

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (p_order_id, p_product_id, p_qty, v_price);
END //

DELIMITER ;

CALL add_order_item(1001, 7, 3);

All routine code in this lesson is read-only, because it needs a real MySQL server. Programs that send statements through a driver (PHP, Python) do not use DELIMITER at all: they send the whole CREATE PROCEDURE text as one statement.

Notes on the structure:

  • BEGIN ... END wraps the body. A single statement may skip it.
  • Prefix parameters and variables (p_, v_). If a parameter has the same name as a column, the column wins inside a query, which causes silent bugs such as WHERE id = id matching every row.
  • DECLARE statements must come first in the block, before any other statement.
  • SELECT ... INTO var stores a single row's values in variables. It raises a "no data" condition if there are no rows (see the error handling lesson) and an error if there is more than one row.

Parameters: IN, OUT and INOUT

SQL
DELIMITER //

CREATE PROCEDURE order_totals(
  IN  p_customer_id INT,
  OUT p_order_count INT,
  INOUT p_running_total DECIMAL(12,2)
)
BEGIN
  DECLARE v_sum DECIMAL(12,2);

  SELECT COUNT(*), COALESCE(SUM(total), 0)
    INTO p_order_count, v_sum
  FROM orders
  WHERE customer_id = p_customer_id;

  SET p_running_total = p_running_total + v_sum;
END //

DELIMITER ;

SET @total = 100.00;
CALL order_totals(42, @n, @total);
SELECT @n AS orders, @total AS running_total;
  • IN (the default) passes a value into the procedure. Changes inside do not affect the caller.
  • OUT starts as NULL inside and sends a value back to the caller.
  • INOUT passes a value in and sends the changed value back.

The caller passes user variables (@total) for OUT and INOUT. User variables begin with @, belong to the connection and need no declaration, while DECLAREd local variables exist only inside the routine block. A procedure can also simply end with a SELECT, which returns a result set to the client, and that is the most common way to return data.

Control flow

Routines have the usual structures. Statements that end a block need a semicolon, and each keyword pairs with a specific ending:

SQL
-- IF / ELSEIF / ELSE
IF p_total >= 100 THEN
  SET v_fee = 0;
ELSEIF p_total >= 50 THEN
  SET v_fee = 2.50;
ELSE
  SET v_fee = 5.00;
END IF;

-- CASE (searched form)
CASE
  WHEN v_days <= 1 THEN SET v_label = 'new';
  WHEN v_days <= 30 THEN SET v_label = 'recent';
  ELSE SET v_label = 'old';
END CASE;

-- WHILE
SET v_i = 1;
WHILE v_i <= 5 DO
  INSERT INTO numbers (n) VALUES (v_i);
  SET v_i = v_i + 1;
END WHILE;

-- LOOP with LEAVE (like break) and ITERATE (like continue)
retry: LOOP
  SET v_tries = v_tries + 1;
  IF v_tries > 3 THEN LEAVE retry; END IF;
END LOOP retry;

-- REPEAT ... UNTIL (runs at least once)
REPEAT
  SET v_n = v_n * 2;
UNTIL v_n > 1000 END REPEAT;

Beware that CASE as a statement in a routine raises an error ("Case not found for CASE statement") if no WHEN matches and there is no ELSE. The CASE expression inside a query, in contrast, simply returns NULL.

Because loops in a routine process data row by row, most of them can and should be replaced by one set-based query. The logic above is simple to run live as plain SQL. Here the shipping fee rule from the IF block becomes a CASE expression over a whole table at once:

SQL The IF/ELSEIF fee rule as one set-based query

Press Run (or Ctrl + Enter) to see the result here.

The same rule used in a loop would run once per order, while this query computes all of them in one pass. And the WHILE v_i <= 5 loop above, which inserted five rows one at a time, is also available without any loop, as a recursive query:

SQL The WHILE loop as a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

MySQL 8.0 supports the same WITH RECURSIVE syntax (see recursive CTEs), so you can often drop the loop there too.

Functions

A function takes IN parameters only, must RETURN exactly one value and is called inside an expression:

SQL
DELIMITER //

CREATE FUNCTION shipping_fee(p_total DECIMAL(10,2))
RETURNS DECIMAL(5,2)
DETERMINISTIC
NO SQL
BEGIN
  IF p_total >= 100 THEN RETURN 0.00;
  ELSEIF p_total >= 50 THEN RETURN 2.50;
  END IF;
  RETURN 5.00;
END //

DELIMITER ;

SELECT id, total, shipping_fee(total) AS fee FROM orders;

Every function declares its nature with characteristics:

  • DETERMINISTIC: the same inputs always give the same result. NOT DETERMINISTIC (the default) covers anything using time, randomness or data that changes.
  • NO SQL, READS SQL DATA, MODIFIES SQL DATA or CONTAINS SQL (the default): what kind of SQL the body runs.

These are not just labels. When the binary log is on (the usual production setup), MySQL refuses to create a function that is not declared DETERMINISTIC, NO SQL or READS SQL DATA, with error 1418, because a function with unknown behavior could produce different results on a replica. Declare them honestly. Do not "fix" the error by setting log_bin_trust_function_creators = 1 unless you understand the replication risk.

A function used in a WHERE clause runs once per candidate row, and it hides the expression from the optimizer, so an indexed column wrapped in your function cannot use the index. Treat functions as convenient for presentation and small calculations, not as a way to filter huge tables.

SQL SECURITY and privileges

SQL
CREATE DEFINER = 'admin'@'localhost' PROCEDURE close_month()
SQL SECURITY DEFINER
BEGIN
  -- runs with the privileges of 'admin', whoever calls it
END;
  • SQL SECURITY DEFINER (the default) runs with the privileges of the account that created the routine. This lets you give an application user only EXECUTE on a procedure while denying direct table access, which is a real security benefit.
  • SQL SECURITY INVOKER runs with the caller's own privileges, which is safer when the routine should never do more than the caller could.

Callers need the EXECUTE privilege on the routine. If the definer account is dropped, the routine fails until it is recreated, which is a common surprise after restoring a dump on a new server.

Managing and calling routines

SQL
SHOW PROCEDURE STATUS WHERE Db = 'shop';
SHOW CREATE PROCEDURE add_order_item;
DROP PROCEDURE IF EXISTS add_order_item;

From application code you call a procedure like any statement. With PDO in PHP:

PHP
$stmt = $pdo->prepare('CALL order_totals(?, @n, @total)');
$stmt->execute([42]);
$row = $pdo->query('SELECT @n AS orders, @total AS total')->fetch();

This block is read-only because it needs a MySQL server. A procedure that returns result sets also returns an extra status packet, so some drivers require you to read all result sets (nextRowset() in PDO) before the connection can run another statement. Test this early, because it is a classic source of "Commands out of sync" errors.

When to use routines (and when not to)

Good fits: an operation that must be atomic and close to the data, a security boundary (EXECUTE only), a batch job that touches millions of rows where round trips dominate.

Costs: logic is split between two languages and two places, version control and deployment of routines is easier to forget than application code, debugging tools are weak, they run on the database server (the hardest layer to scale) and the language is limited. Many teams keep routines for a few well-defined jobs and everything else in the application. Keep every routine under version control, deployed through migrations like any schema change.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why do you use DELIMITER // when creating a procedure in the mysql client?

    DELIMITER is a client command that changes the statement terminator so the whole CREATE PROCEDURE reaches the server as one statement. Drivers sending the whole text do not need it.
  2. 2. Which parameter mode sends a value into a procedure and returns the changed value to the caller?

    INOUT works in both directions. IN only passes a value in, and OUT only sends a value back.
  3. 3. What is the main reason a CREATE FUNCTION fails with error 1418 when the binary log is on?

    MySQL needs to know that the function is safe to replay on replicas, so it requires one of those characteristics when binary logging is enabled.
  4. 4. A routine has SQL SECURITY DEFINER. Whose privileges does it run with?

    DEFINER is the default: the routine runs with the creator's privileges, so you can grant only EXECUTE to callers.

Summary

  • Procedures run with CALL and can change data, take IN, OUT and INOUT parameters and return result sets. Functions return one value and can be used inside queries.
  • DELIMITER is a client feature for typing multi-statement bodies. Drivers do not need it.
  • Prefix parameters and variables so they never collide with column names, and put DECLARE first in a block.
  • IF, CASE, WHILE, LOOP and REPEAT exist, but a set-based query or recursive CTE is usually better than a row-by-row loop.
  • Declare functions as DETERMINISTIC, NO SQL or READS SQL DATA honestly, because binary logging depends on it.
  • SQL SECURITY DEFINER lets callers use a routine without table access. Keep routines in version control.

Next: Cursors, Handlers and Error Handling, where we deal with errors and row-by-row processing.