MariaDB Stored Procedures and Stored Functions
Deterministic guidance for writing stored procedures, stored functions, control flow, cursors, error handlers (DECLARE HANDLER, SIGNAL, RESIGNAL), and choosing DETERMINISTIC vs NOT DETERMINISTIC. This skill covers the routine body itself. For triggers, events, and views, see the sibling skill mariadb-syntax-triggers-events-views.
Quick Reference
- ALWAYS use
DELIMITER // (or any non-semicolon) before CREATE PROCEDURE / CREATE FUNCTION in client tools so the ; inside the body is not interpreted as end-of-statement. Reset with DELIMITER ; afterwards. This is a client-side directive, NOT SQL. Drivers that send one statement per call (PDO, JDBC executeUpdate) do not need DELIMITER ; the multi-statement client (mariadb CLI, MySQL Workbench) does. Verified : KB create-procedure/.
- ALWAYS write
DETERMINISTIC only when the function truly is deterministic. A function is deterministic only when "it can produce only one result for a given list of parameters". Functions that call NOW(), CURRENT_TIMESTAMP(), RAND(), UUID(), CONNECTION_ID(), LAST_INSERT_ID(), or that read session variables (@var) or non-immutable table data are NOT deterministic. Mis-declaring as DETERMINISTIC lets the optimizer cache or hoist the call, producing wrong results. Verified : KB create-function/.
- NEVER call a NOT DETERMINISTIC function in a WHERE clause on a large table. The optimizer cannot fold the call to a constant, so it is re-evaluated per row, no index on the function-result can be used, and the plan degrades to a full scan with a per-row procedure call. ALWAYS push the predicate to a constant-folded expression in the application, or store the result in a column.
- ALWAYS declare local variables and cursors and handlers in the correct order inside
BEGIN ... END : (1) local DECLARE var TYPE, then (2) DECLARE condition_name CONDITION, then (3) DECLARE cursor_name CURSOR, then (4) DECLARE ... HANDLER. Any other order is a syntax error. Verified : KB declare-cursor/.
- ALWAYS attach
DECLARE CONTINUE HANDLER FOR NOT FOUND to detect end-of-cursor. The handler sets a flag the LOOP body checks ; without it, FETCH past the last row raises SQLSTATE 02000 and the procedure either silently exits (no handler) or hangs forever (handler badly written). Pattern : DECLARE done INT DEFAULT 0 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;.
- ALWAYS use
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...' for user-defined errors. 45000 is the documented class for application-raised conditions ; it surfaces as an error (not a warning) when unhandled. Optionally set MYSQL_ERRNO = <integer> for a custom numeric code. Verified : KB signal/.
- ALWAYS use
RESIGNAL (not SIGNAL) inside a handler to re-raise the caught error. RESIGNAL with no arguments re-throws the original condition unchanged ; RESIGNAL SET MESSAGE_TEXT = '...' re-throws with an overridden message but preserves the original SQLSTATE and MYSQL_ERRNO. Using SIGNAL instead loses the original error context. Verified : KB resignal/.
- ALWAYS raise
max_sp_recursion_depth before calling a recursive procedure. Default is 0, which forbids recursion ; max is 255. Set per-session : SET @@SESSION.max_sp_recursion_depth = 32 ;. Without this, the second call into the same procedure errors with Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded. Verified : KB create-procedure/.
- ALWAYS pick the right characteristic clause. Functions default to
CONTAINS SQL and NOT DETERMINISTIC. Set READS SQL DATA when the body has SELECT, MODIFIES SQL DATA when it has INSERT / UPDATE / DELETE / DDL. These clauses are required to be accurate when log_bin_trust_function_creators = OFF (the secure default) ; otherwise CREATE FUNCTION is refused for binlog-safety. Verified : KB create-function/.
- ALWAYS prefer
SQL SECURITY INVOKER for routines that touch user data. The DEFINER default runs the body with the creator's privileges, which is convenient for exposing a subset of sensitive tables but is a privilege-escalation vector if the routine is misused. INVOKER defers privilege checks to the caller. Audit SHOW CREATE PROCEDURE regularly for unexpected DEFINER accounts.
- NEVER use
INVISIBLE, BEFORE triggers, or trigger-style transaction control inside a function. Triggers and views are not in this skill (see sibling mariadb-syntax-triggers-events-views) but the rule that "functions cannot contain transaction control" (COMMIT, ROLLBACK, SAVEPOINT, START TRANSACTION) is enforced at runtime with Explicit or implicit commit is not allowed in stored function or trigger. Procedures CAN contain transaction control.
OUT / INOUT / IN OUT on a function (10.8+) can ONLY be called via SET and CALL, NEVER inside SELECT. Attempting SELECT myfunc(@x) on a function with OUT/INOUT parameters raises error 4186. For most cases : if you need multi-value return, write a PROCEDURE with OUT parameters, not a FUNCTION. Verified : KB create-function/.
- MariaDB 11.8+ adds Oracle-mode
IN OUT and per-parameter DEFAULT. On 11.8 and later, CREATE PROCEDURE p(IN OUT a INT, IN b INT DEFAULT 0) is legal. On 10.6 through 11.7, only IN | OUT | INOUT modes exist and DEFAULT is a syntax error. Verified : KB create-procedure/.
Decision Trees
Tree 1 : Procedure or function?
What does the routine do?
Returns ONE scalar value, called inside expressions (SELECT, WHERE, SET) ?
-> FUNCTION with RETURNS type.
Is the result reproducible from inputs alone (no clock, no random, no session vars) ?
Yes -> DETERMINISTIC.
No -> NOT DETERMINISTIC (default). Avoid using in WHERE on large tables.
Returns multiple values, or no value, or mutates many rows ?
-> PROCEDURE.
Call site uses CALL p(...) ;
Needs to return values ? -> OUT or INOUT parameters.
Needs to commit / rollback ? -> ALLOWED in a procedure (NOT in a function).
Needs to be called from SELECT row-by-row ? -> NO. Functions can be ;
procedures CANNOT.
Tree 2 : Which handler do I need?
What condition do you want to catch?
Any error (catch-all) ?
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION ... -- aborts the BEGIN block
-> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ... -- swallows, continues
Specifically duplicate-key (ER_DUP_ENTRY) ?
-> SQLSTATE '23000' or numeric 1062
End of cursor data ?
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
Any warning ?
-> DECLARE CONTINUE HANDLER FOR SQLWARNING ...
Handler scope = the BEGIN ... END block where it is DECLAREd, including inner blocks
unless overridden by an inner handler. EXIT terminates the block, CONTINUE returns
to the statement after the failing one.
Tree 3 : DETERMINISTIC vs NOT DETERMINISTIC
Does the function body call any of :
NOW(), CURRENT_TIMESTAMP(), SYSDATE(), CURRENT_DATE(), CURRENT_TIME() ?
RAND(), UUID(), CONNECTION_ID(), LAST_INSERT_ID() ?
Read a session variable (@var) or @@session.<var> ?
SELECT from any table not guaranteed immutable ?
Call another NOT DETERMINISTIC function ?
Any "yes" -> NOT DETERMINISTIC (default). Document why.
All "no" -> DETERMINISTIC. The optimizer can cache the call and replicate
safely under STATEMENT binlog.
Tree 4 : SIGNAL vs RESIGNAL
Am I currently inside an active handler?
No -> SIGNAL SQLSTATE 'xxxxx' SET MESSAGE_TEXT = '...' ;
(Used to raise NEW application-level errors. Use SQLSTATE '45000'.)
Yes -> RESIGNAL ; -- re-throw unchanged
RESIGNAL SET MESSAGE_TEXT = 'wrap' ; -- re-throw with new text,
-- original SQLSTATE preserved
(Used to add context to a CAUGHT error without losing the diagnostic area.)
Using SIGNAL inside a handler is legal but LOSES the original error's
SQLSTATE and code unless you copy them manually.
Patterns
Pattern 1 : Procedure with IN and OUT parameters
-- 10.6+ : returns counts via OUT parameters, modifies data, INVOKER security.
DELIMITER //
CREATE OR REPLACE PROCEDURE archive_orders_before(
IN cutoff DATETIME,
OUT moved INT,
OUT skipped INT
)
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT 'Moves orders.created_at < cutoff to orders_archive'
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < cutoff;
SET moved = ROW_COUNT();
DELETE FROM orders WHERE created_at < cutoff;
SET skipped = (SELECT COUNT(*) FROM orders WHERE created_at < cutoff);
COMMIT;
END//
DELIMITER ;
-- Call site
CALL archive_orders_before('2025-01-01', @m, @s);
SELECT @m AS moved, @s AS skipped;
Pattern 2 : Deterministic function safe in WHERE
-- 10.6+ : pure arithmetic, deterministic, indexable on the column it computes on.
DELIMITER //
CREATE OR REPLACE FUNCTION net_price(gross DECIMAL(12,2), vat_rate DECIMAL(5,4))
RETURNS DECIMAL(12,2)
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
RETURN gross / (1 + vat_rate);
END//
DELIMITER ;
-- Safe in WHERE on a generated column :
ALTER TABLE invoices
ADD COLUMN net AS (net_price(gross, vat_rate)) PERSISTENT,
ADD INDEX ix_net (net);
SELECT id FROM invoices WHERE net > 100;
Pattern 3 : Cursor loop with NOT FOUND handler
-- 10.6+ : iterate over a result set inside a procedure.
DELIMITER //
CREATE OR REPLACE PROCEDURE flag_high_value_customers()
MODIFIES SQL DATA
BEGIN
DECLARE v_id BIGINT;
DECLARE v_total DECIMAL(15,2);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 10000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO v_id, v_total;
IF done = 1 THEN
LEAVE read_loop;
END IF;
UPDATE customers SET tier = 'high' WHERE id = v_id;
END LOOP;
CLOSE cur;
END//
DELIMITER ;
Pattern 4 : Custom error with SIGNAL plus catching with handler
-- 10.6+ : raise a domain error, caller catches via handler.
DELIMITER //
CREATE OR REPLACE PROCEDURE withdraw(IN acct INT, IN amt DECIMAL(15,2))
MODIFIES SQL DATA
BEGIN
DECLARE bal DECIMAL(15,2);
START TRANSACTION;
SELECT balance INTO bal FROM accounts WHERE id = acct FOR UPDATE;
IF bal IS NULL THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'account not found', MYSQL_ERRNO = 31001;
END IF;
IF bal < amt THEN
ROLLBACK;
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'insufficient funds', MYSQL_ERRNO = 31002;
END IF;
UPDATE accounts SET balance = balance - amt WHERE id = acct;
COMMIT;
END//
DELIMITER ;
Pattern 5 : Recursive procedure with max_sp_recursion_depth
-- 10.6+ : factorial via recursion. Without the SET, second call errors out.
SET @@SESSION.max_sp_recursion_depth = 32;
DELIMITER //
CREATE OR REPLACE PROCEDURE factorial(IN n INT, INOUT result BIGINT)
CONTAINS SQL
BEGIN
IF n <= 1 THEN
SET result = 1;
ELSE
CALL factorial(n - 1, result);
SET result = result * n;
END IF;
END//
DELIMITER ;
CALL factorial(10, @r); SELECT @r; -- 3628800
References
references/methods.md : full grammar, all relevant SQLSTATE codes, characteristic matrix, system variables.
references/examples.md : 10+ working procedure / function examples covering IN/OUT, cursors, handlers, SIGNAL, RESIGNAL, recursion, nested handlers.
references/anti-patterns.md : 8 production-grade anti-patterns with why-it-fails and corrected alternative.
Official source URLs (last verified 2026-05-19)
https://mariadb.com/kb/en/create-procedure/
https://mariadb.com/kb/en/create-function/
https://mariadb.com/kb/en/declare-handler/
https://mariadb.com/kb/en/signal/
https://mariadb.com/kb/en/resignal/
https://mariadb.com/kb/en/declare-cursor/
https://mariadb.com/kb/en/cursors/
Related skills
mariadb-syntax-triggers-events-views : triggers, scheduled events, views ; shares DECLARE HANDLER and SIGNAL semantics.
mariadb-impl-transactions-and-locking : transactional patterns invoked from procedures.
mariadb-errors-procedures-and-handlers : diagnostic recipes when a routine misbehaves.
1---2name: mariadb-syntax-procedures-functions3description: Use when writing stored procedures or stored functions, designing error handling with DECLARE HANDLER, using SIGNAL and RESIGNAL for custom errors, choosing DETERMINISTIC vs NOT DETERMINISTIC, or porting MySQL procedures to MariaDB. Prevents the common mistake of marking a function DETERMINISTIC when it reads NOW() or session variables, putting heavy stored-function logic in WHERE clauses (defeats index usage), missing EXIT or CONTINUE handlers leading to silent loop-abort, and forgetting to raise max_sp_recursion_depth before calling a recursive procedure. Covers CREATE PROCEDURE syntax with IN / OUT / INOUT parameters, CREATE FUNCTION with RETURNS, DELIMITER usage, local DECLARE variables, control flow (IF, CASE, LOOP, WHILE, REPEAT, ITERATE, LEAVE), cursor lifecycle (DECLARE / OPEN / FETCH / CLOSE with NOT FOUND handler), error handling via DECLARE HANDLER for CONTINUE / EXIT on SQLSTATE / SQLWARNING / NOT FOUND / SQLEXCEPTION / numeric error codes, SIGNAL with SQLSTATE 45000 and MESSAGE_TEXT, RESIGNAL ins4license: MIT5---67# MariaDB Stored Procedures and Stored Functions89Deterministic guidance for writing stored procedures, stored functions, control flow, cursors, error handlers (DECLARE HANDLER, SIGNAL, RESIGNAL), and choosing DETERMINISTIC vs NOT DETERMINISTIC. This skill covers the routine body itself. For triggers, events, and views, see the sibling skill `mariadb-syntax-triggers-events-views`.1011## Quick Reference1213- **ALWAYS use `DELIMITER //` (or any non-semicolon) before `CREATE PROCEDURE` / `CREATE FUNCTION` in client tools** so the `;` inside the body is not interpreted as end-of-statement. Reset with `DELIMITER ;` afterwards. This is a client-side directive, NOT SQL. Drivers that send one statement per call (PDO, JDBC `executeUpdate`) do not need DELIMITER ; the multi-statement client (`mariadb` CLI, MySQL Workbench) does. Verified : KB `create-procedure/`.14- **ALWAYS write `DETERMINISTIC` only when the function truly is deterministic**. A function is deterministic only when "it can produce only one result for a given list of parameters". Functions that call `NOW()`, `CURRENT_TIMESTAMP()`, `RAND()`, `UUID()`, `CONNECTION_ID()`, `LAST_INSERT_ID()`, or that read session variables (`@var`) or non-immutable table data are NOT deterministic. Mis-declaring as DETERMINISTIC lets the optimizer cache or hoist the call, producing wrong results. Verified : KB `create-function/`.15- **NEVER call a NOT DETERMINISTIC function in a WHERE clause on a large table**. The optimizer cannot fold the call to a constant, so it is re-evaluated per row, no index on the function-result can be used, and the plan degrades to a full scan with a per-row procedure call. ALWAYS push the predicate to a constant-folded expression in the application, or store the result in a column.16- **ALWAYS declare local variables and cursors and handlers in the correct order inside `BEGIN ... END`** : (1) local `DECLARE var TYPE`, then (2) `DECLARE condition_name CONDITION`, then (3) `DECLARE cursor_name CURSOR`, then (4) `DECLARE ... HANDLER`. Any other order is a syntax error. Verified : KB `declare-cursor/`.17- **ALWAYS attach `DECLARE CONTINUE HANDLER FOR NOT FOUND` to detect end-of-cursor**. The handler sets a flag the LOOP body checks ; without it, FETCH past the last row raises SQLSTATE `02000` and the procedure either silently exits (no handler) or hangs forever (handler badly written). Pattern : `DECLARE done INT DEFAULT 0 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;`.18- **ALWAYS use `SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...'` for user-defined errors**. `45000` is the documented class for application-raised conditions ; it surfaces as an error (not a warning) when unhandled. Optionally set `MYSQL_ERRNO = <integer>` for a custom numeric code. Verified : KB `signal/`.19- **ALWAYS use `RESIGNAL` (not `SIGNAL`) inside a handler to re-raise the caught error**. `RESIGNAL` with no arguments re-throws the original condition unchanged ; `RESIGNAL SET MESSAGE_TEXT = '...'` re-throws with an overridden message but preserves the original SQLSTATE and MYSQL_ERRNO. Using `SIGNAL` instead loses the original error context. Verified : KB `resignal/`.20- **ALWAYS raise `max_sp_recursion_depth` before calling a recursive procedure**. Default is `0`, which forbids recursion ; max is `255`. Set per-session : `SET @@SESSION.max_sp_recursion_depth = 32 ;`. Without this, the second call into the same procedure errors with `Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded`. Verified : KB `create-procedure/`.21- **ALWAYS pick the right characteristic clause**. Functions default to `CONTAINS SQL` and `NOT DETERMINISTIC`. Set `READS SQL DATA` when the body has `SELECT`, `MODIFIES SQL DATA` when it has `INSERT` / `UPDATE` / `DELETE` / DDL. These clauses are required to be accurate when `log_bin_trust_function_creators = OFF` (the secure default) ; otherwise `CREATE FUNCTION` is refused for binlog-safety. Verified : KB `create-function/`.22- **ALWAYS prefer `SQL SECURITY INVOKER` for routines that touch user data**. The `DEFINER` default runs the body with the creator's privileges, which is convenient for exposing a subset of sensitive tables but is a privilege-escalation vector if the routine is misused. `INVOKER` defers privilege checks to the caller. Audit `SHOW CREATE PROCEDURE` regularly for unexpected `DEFINER` accounts.23- **NEVER use `INVISIBLE`, `BEFORE` triggers, or trigger-style transaction control inside a function**. Triggers and views are not in this skill (see sibling `mariadb-syntax-triggers-events-views`) but the rule that "functions cannot contain transaction control" (`COMMIT`, `ROLLBACK`, `SAVEPOINT`, `START TRANSACTION`) is enforced at runtime with `Explicit or implicit commit is not allowed in stored function or trigger`. Procedures CAN contain transaction control.24- **`OUT` / `INOUT` / `IN OUT` on a function (10.8+)** can ONLY be called via `SET` and `CALL`, NEVER inside `SELECT`. Attempting `SELECT myfunc(@x)` on a function with OUT/INOUT parameters raises error 4186. For most cases : if you need multi-value return, write a PROCEDURE with OUT parameters, not a FUNCTION. Verified : KB `create-function/`.25- **MariaDB 11.8+ adds Oracle-mode `IN OUT` and per-parameter `DEFAULT`**. On 11.8 and later, `CREATE PROCEDURE p(IN OUT a INT, IN b INT DEFAULT 0)` is legal. On 10.6 through 11.7, only `IN | OUT | INOUT` modes exist and DEFAULT is a syntax error. Verified : KB `create-procedure/`.2627## Decision Trees2829### Tree 1 : Procedure or function?3031```32What does the routine do?33 Returns ONE scalar value, called inside expressions (SELECT, WHERE, SET) ?34 -> FUNCTION with RETURNS type.35 Is the result reproducible from inputs alone (no clock, no random, no session vars) ?36 Yes -> DETERMINISTIC.37 No -> NOT DETERMINISTIC (default). Avoid using in WHERE on large tables.3839 Returns multiple values, or no value, or mutates many rows ?40 -> PROCEDURE.41 Call site uses CALL p(...) ;42 Needs to return values ? -> OUT or INOUT parameters.43 Needs to commit / rollback ? -> ALLOWED in a procedure (NOT in a function).44 Needs to be called from SELECT row-by-row ? -> NO. Functions can be ;45 procedures CANNOT.46```4748### Tree 2 : Which handler do I need?4950```51What condition do you want to catch?52 Any error (catch-all) ?53 -> DECLARE EXIT HANDLER FOR SQLEXCEPTION ... -- aborts the BEGIN block54 -> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION ... -- swallows, continues55 Specifically duplicate-key (ER_DUP_ENTRY) ?56 -> SQLSTATE '23000' or numeric 106257 End of cursor data ?58 -> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;59 Any warning ?60 -> DECLARE CONTINUE HANDLER FOR SQLWARNING ...6162Handler scope = the BEGIN ... END block where it is DECLAREd, including inner blocks63unless overridden by an inner handler. EXIT terminates the block, CONTINUE returns64to the statement after the failing one.65```6667### Tree 3 : DETERMINISTIC vs NOT DETERMINISTIC6869```70Does the function body call any of :71 NOW(), CURRENT_TIMESTAMP(), SYSDATE(), CURRENT_DATE(), CURRENT_TIME() ?72 RAND(), UUID(), CONNECTION_ID(), LAST_INSERT_ID() ?73 Read a session variable (@var) or @@session.<var> ?74 SELECT from any table not guaranteed immutable ?75 Call another NOT DETERMINISTIC function ?7677 Any "yes" -> NOT DETERMINISTIC (default). Document why.78 All "no" -> DETERMINISTIC. The optimizer can cache the call and replicate79 safely under STATEMENT binlog.80```8182### Tree 4 : SIGNAL vs RESIGNAL8384```85Am I currently inside an active handler?86 No -> SIGNAL SQLSTATE 'xxxxx' SET MESSAGE_TEXT = '...' ;87 (Used to raise NEW application-level errors. Use SQLSTATE '45000'.)8889 Yes -> RESIGNAL ; -- re-throw unchanged90 RESIGNAL SET MESSAGE_TEXT = 'wrap' ; -- re-throw with new text,91 -- original SQLSTATE preserved92 (Used to add context to a CAUGHT error without losing the diagnostic area.)93 Using SIGNAL inside a handler is legal but LOSES the original error's94 SQLSTATE and code unless you copy them manually.95```9697## Patterns9899### Pattern 1 : Procedure with IN and OUT parameters100101```sql102-- 10.6+ : returns counts via OUT parameters, modifies data, INVOKER security.103DELIMITER //104CREATE OR REPLACE PROCEDURE archive_orders_before(105 IN cutoff DATETIME,106 OUT moved INT,107 OUT skipped INT108)109 MODIFIES SQL DATA110 SQL SECURITY INVOKER111 COMMENT 'Moves orders.created_at < cutoff to orders_archive'112BEGIN113 DECLARE EXIT HANDLER FOR SQLEXCEPTION114 BEGIN115 ROLLBACK;116 RESIGNAL;117 END;118119 START TRANSACTION;120 INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < cutoff;121 SET moved = ROW_COUNT();122 DELETE FROM orders WHERE created_at < cutoff;123 SET skipped = (SELECT COUNT(*) FROM orders WHERE created_at < cutoff);124 COMMIT;125END//126DELIMITER ;127128-- Call site129CALL archive_orders_before('2025-01-01', @m, @s);130SELECT @m AS moved, @s AS skipped;131```132133### Pattern 2 : Deterministic function safe in WHERE134135```sql136-- 10.6+ : pure arithmetic, deterministic, indexable on the column it computes on.137DELIMITER //138CREATE OR REPLACE FUNCTION net_price(gross DECIMAL(12,2), vat_rate DECIMAL(5,4))139 RETURNS DECIMAL(12,2)140 DETERMINISTIC141 CONTAINS SQL142 SQL SECURITY INVOKER143BEGIN144 RETURN gross / (1 + vat_rate);145END//146DELIMITER ;147148-- Safe in WHERE on a generated column :149ALTER TABLE invoices150 ADD COLUMN net AS (net_price(gross, vat_rate)) PERSISTENT,151 ADD INDEX ix_net (net);152SELECT id FROM invoices WHERE net > 100;153```154155### Pattern 3 : Cursor loop with NOT FOUND handler156157```sql158-- 10.6+ : iterate over a result set inside a procedure.159DELIMITER //160CREATE OR REPLACE PROCEDURE flag_high_value_customers()161 MODIFIES SQL DATA162BEGIN163 DECLARE v_id BIGINT;164 DECLARE v_total DECIMAL(15,2);165 DECLARE done INT DEFAULT 0;166 DECLARE cur CURSOR FOR167 SELECT customer_id, SUM(total)168 FROM orders169 GROUP BY customer_id170 HAVING SUM(total) > 10000;171 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;172173 OPEN cur;174 read_loop: LOOP175 FETCH cur INTO v_id, v_total;176 IF done = 1 THEN177 LEAVE read_loop;178 END IF;179 UPDATE customers SET tier = 'high' WHERE id = v_id;180 END LOOP;181 CLOSE cur;182END//183DELIMITER ;184```185186### Pattern 4 : Custom error with SIGNAL plus catching with handler187188```sql189-- 10.6+ : raise a domain error, caller catches via handler.190DELIMITER //191CREATE OR REPLACE PROCEDURE withdraw(IN acct INT, IN amt DECIMAL(15,2))192 MODIFIES SQL DATA193BEGIN194 DECLARE bal DECIMAL(15,2);195 START TRANSACTION;196 SELECT balance INTO bal FROM accounts WHERE id = acct FOR UPDATE;197 IF bal IS NULL THEN198 ROLLBACK;199 SIGNAL SQLSTATE '45000'200 SET MESSAGE_TEXT = 'account not found', MYSQL_ERRNO = 31001;201 END IF;202 IF bal < amt THEN203 ROLLBACK;204 SIGNAL SQLSTATE '45000'205 SET MESSAGE_TEXT = 'insufficient funds', MYSQL_ERRNO = 31002;206 END IF;207 UPDATE accounts SET balance = balance - amt WHERE id = acct;208 COMMIT;209END//210DELIMITER ;211```212213### Pattern 5 : Recursive procedure with max_sp_recursion_depth214215```sql216-- 10.6+ : factorial via recursion. Without the SET, second call errors out.217SET @@SESSION.max_sp_recursion_depth = 32;218219DELIMITER //220CREATE OR REPLACE PROCEDURE factorial(IN n INT, INOUT result BIGINT)221 CONTAINS SQL222BEGIN223 IF n <= 1 THEN224 SET result = 1;225 ELSE226 CALL factorial(n - 1, result);227 SET result = result * n;228 END IF;229END//230DELIMITER ;231232CALL factorial(10, @r); SELECT @r; -- 3628800233```234235## References236237- `references/methods.md` : full grammar, all relevant SQLSTATE codes, characteristic matrix, system variables.238- `references/examples.md` : 10+ working procedure / function examples covering IN/OUT, cursors, handlers, SIGNAL, RESIGNAL, recursion, nested handlers.239- `references/anti-patterns.md` : 8 production-grade anti-patterns with why-it-fails and corrected alternative.240241### Official source URLs (last verified 2026-05-19)242243- `https://mariadb.com/kb/en/create-procedure/`244- `https://mariadb.com/kb/en/create-function/`245- `https://mariadb.com/kb/en/declare-handler/`246- `https://mariadb.com/kb/en/signal/`247- `https://mariadb.com/kb/en/resignal/`248- `https://mariadb.com/kb/en/declare-cursor/`249- `https://mariadb.com/kb/en/cursors/`250251### Related skills252253- `mariadb-syntax-triggers-events-views` : triggers, scheduled events, views ; shares DECLARE HANDLER and SIGNAL semantics.254- `mariadb-impl-transactions-and-locking` : transactional patterns invoked from procedures.255- `mariadb-errors-procedures-and-handlers` : diagnostic recipes when a routine misbehaves.