mariadb-syntax-procedures-functions

name	mariadb-syntax-procedures-functions
description	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 inside a handler, DETERMINISTIC vs NOT DETERMINISTIC semantics for the optimizer, READS SQL DATA / MODIFIES SQL DATA / CONTAINS SQL / NO SQL data-access clauses, SQL SECURITY DEFINER vs INVOKER, max_sp_recursion_depth tuning, and the 11.8+ IN OUT and per-parameter DEFAULT Oracle-mode extensions. Keywords: stored procedure, stored function, CREATE PROCEDURE, CREATE FUNCTION, DELIMITER, IN OUT INOUT parameters, DECLARE variable, IF THEN ELSEIF, CASE WHEN, LOOP, WHILE, REPEAT UNTIL, ITERATE, LEAVE, cursor, DECLARE CURSOR, OPEN FETCH CLOSE, DECLARE HANDLER, CONTINUE HANDLER, EXIT HANDLER, SQLSTATE, SQLWARNING, NOT FOUND, SQLEXCEPTION, SIGNAL, RESIGNAL, MESSAGE_TEXT, MYSQL_ERRNO, 45000, DETERMINISTIC, NOT DETERMINISTIC, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA, NO SQL, SQL SECURITY DEFINER, SQL SECURITY INVOKER, recursion limit, max_sp_recursion_depth, IN OUT 11.8, DEFAULT parameter 11.8, SYS_REFCURSOR, OUT parameter SET only, why is my function slow in WHERE, function blocks index, procedure cursor loop hangs, silent abort in handler, how do I raise a custom error, how do I catch duplicate key, function deterministic mistake, recursive procedure error, DELIMITER not reset
license	MIT
compatibility	Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata	{"author":"OpenAEC-Foundation","version":"1.0"}

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 and the procedure either silently exits (no handler) or hangs forever (handler badly written). Pattern : .