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/.