Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 40 ยท Stored Programs and Triggers

Variables and Stored Functions in MariaDB

Declare and use MariaDB user variables, local variables and system variables, then write stored functions with DECLARE, IF, CASE and RETURN, including DETERMINISTIC and DELIMITER.

Sooner or later a query needs a remembered value: a tax rate, the id just inserted, a running counter, a threshold shared by several statements. MariaDB has three kinds of variables for that, and a stored function lets you package a calculation under a name and use it in any query.

The MariaDB-specific syntax in this lesson (variables, DELIMITER, CREATE FUNCTION) cannot run in the in-browser engine, so the MariaDB code is read-only. Each section ends with a runnable stand-in that shows the same idea in portable SQL.

Three kinds of variables

Kind Syntax Lives for Typical use
User variable SET @rate = 0.2; SELECT @rate; the connection (session) quick scripts, passing values between statements
Local variable DECLARE n INT DEFAULT 0; one stored program (BEGIN ... END) working values in procedures, functions, triggers
System variable SET SESSION sql_mode = '...' / SET GLOBAL max_connections = 300 session or server configuration

User variables

User variables start with @, need no declaration, and have no fixed type: the type follows the last value assigned. They disappear when the connection closes, and each connection has its own.

SQL
SET @rate = 0.2, @min_total = 50;

SELECT id, total, ROUND(total * @rate, 2) AS tax
FROM orders
WHERE total >= @min_total;

SELECT MAX(total) INTO @biggest FROM orders;
SELECT @biggest;

-- assignment inside a query uses :=
SELECT @running := @running + total AS running_total
FROM orders, (SELECT @running := 0) AS init
ORDER BY id;

The last pattern was the old way to build running totals before window functions; prefer window functions now, because the evaluation order of := inside a query is not guaranteed.

An unset user variable is NULL, so a typo such as @ratte silently makes expressions NULL instead of failing. Prepared statements also take user variables: EXECUTE stmt USING @id;.

Local variables

Local variables exist only inside a compound statement and must be declared at the top, before cursors and handlers, with a type:

SQL
BEGIN
  DECLARE v_total DECIMAL(10,2) DEFAULT 0;
  DECLARE v_name  VARCHAR(80);
  SELECT SUM(total) INTO v_total FROM orders WHERE customer_id = 7;
  SET v_name = 'Ana';
END

Use a prefix such as v_ or p_ for variables and parameters. If a local variable has the same name as a column, the variable wins inside expressions, which creates baffling bugs (WHERE id = id is always true).

System variables

SQL
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SELECT @@global.max_connections, @@session.sql_mode;
SET SESSION sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE';
SET GLOBAL slow_query_log = ON;       -- needs privilege, lost at restart
SET PERSIST ...;                       -- MySQL only; in MariaDB edit the .cnf file

SET GLOBAL changes the running server but not the configuration file, and applies to new connections only. To keep a setting across restarts, put it in the [mysqld] section of the server option file (for example /etc/mysql/mariadb.conf.d/50-server.cnf); MariaDB 10.x has no SET PERSIST.

Portable stand-in: because plain SQL has no variables, use a one-row CTE (or a settings table) as the "variable" and join to it:

SQL A named parameter with a CTE ยท database: company

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

Change the two numbers in one place and every use follows, the same benefit as SET @bonus_rate = 0.15.

Stored functions

A stored function takes parameters, runs a block of statements and returns exactly one value, so it can sit inside any expression: SELECT total_with_tax(total) FROM orders. Functions are stored in a database and run on the server next to the data.

Because a body contains ;, the mariadb command-line client needs a different statement delimiter while you define it:

SQL
DELIMITER //

CREATE FUNCTION total_with_tax(p_amount DECIMAL(10,2), p_rate DECIMAL(4,3))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
  RETURN ROUND(p_amount * (1 + p_rate), 2);
END //

DELIMITER ;

SELECT total_with_tax(100, 0.2);   -- 120.00

DELIMITER is a client command, not SQL, so you do not use it in drivers, application code, or GUI tools that send the whole CREATE as one statement.

Key clauses:

  • RETURNS type is required, and every path must reach a RETURN, otherwise the call fails at run time.
  • DETERMINISTIC promises the same inputs always give the same output. NOT DETERMINISTIC (default) is right for functions using NOW() or RAND(). A false DETERMINISTIC can make the optimizer or replication produce wrong results.
  • Data access hints: NO SQL, READS SQL DATA, MODIFIES SQL DATA. With binary logging enabled, creating a function that does not declare DETERMINISTIC, NO SQL or READS SQL DATA may fail unless log_bin_trust_function_creators=1.
  • SQL SECURITY DEFINER | INVOKER as for views.

Control flow inside functions

SQL
DELIMITER //
CREATE FUNCTION shipping_fee(p_weight_kg DECIMAL(6,2), p_country CHAR(2))
RETURNS DECIMAL(6,2)
DETERMINISTIC
BEGIN
  DECLARE v_fee DECIMAL(6,2);

  IF p_country = 'NL' THEN
    SET v_fee = 4.50;
  ELSEIF p_country IN ('BE', 'DE', 'FR') THEN
    SET v_fee = 7.90;
  ELSE
    SET v_fee = 15.00;
  END IF;

  RETURN v_fee + CASE
                   WHEN p_weight_kg <= 1  THEN 0
                   WHEN p_weight_kg <= 5  THEN 2.50
                   ELSE 6.00
                 END;
END //
DELIMITER ;

Available constructs: IF ... ELSEIF ... ELSE ... END IF, CASE ... WHEN, loops with WHILE ... DO ... END WHILE, REPEAT ... UNTIL ... END REPEAT, LOOP ... LEAVE label ... END LOOP, and SELECT ... INTO var. Loops are covered in stored procedures.

MariaDB 10.3+ also supports Oracle-style syntax when sql_mode includes ORACLE, plus FOR ... IN loops and DECLARE ... TYPE OF.

Lookup functions

A function can query a table with SELECT ... INTO:

SQL
CREATE FUNCTION dept_headcount(p_dept INT) RETURNS INT
READS SQL DATA
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = p_dept);

A single RETURN needs no BEGIN ... END, and therefore no DELIMITER.

Managing functions

SQL
SHOW FUNCTION STATUS WHERE Db = 'hr';
SHOW CREATE FUNCTION total_with_tax;
DROP FUNCTION IF EXISTS total_with_tax;

To change the body, drop and recreate, or use CREATE OR REPLACE FUNCTION (MariaDB 10.1.3+). ALTER FUNCTION only changes characteristics such as the comment or SQL SECURITY.

Portable stand-in: SQL cannot define a function, but a view over CASE expresses the same business rule once, and queries then reuse it.

SQL Business rule in one place (view stand-in)

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

When not to use stored functions

A scalar function in a WHERE clause runs once per row, and it hides the expression from the optimizer, so an index on the column cannot be used. A function that runs a query inside runs that query per row (a hidden N+1). Keep functions small, deterministic and side-effect free; put orchestration into procedures. Logic that changes fast usually belongs in application code under version control; use stored code where it saves round trips or enforces a rule for every client.

Common mistakes

  • Forgetting DELIMITER in the CLI, so the client cuts the body at the first ;.
  • Naming a parameter the same as a column.
  • Declaring DETERMINISTIC on a function that calls NOW().
  • Using user variables for values that must survive reconnects (they will not).
  • Declaring variables after other statements: DECLARE must come first in the block.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How long does a user variable such as @rate live?

    User variables are per session and vanish when the connection closes.
  2. 2. Where must DECLARE statements appear in a BEGIN ... END block?

    MariaDB requires declarations first (variables, then cursors, then handlers).
  3. 3. Why is DELIMITER // used when creating a function in the mariadb client?

    DELIMITER is a client-side command that changes the character that ends a statement.
  4. 4. A function returns NOW() plus a number of days. Which characteristic is correct?

    The result changes with time even for identical inputs, so it is not deterministic.

Summary

  • User variables (@x) are per connection, local variables (DECLARE) per stored program, system variables configure the server.
  • Stored functions return one value and can be used inside queries; mark them correctly (DETERMINISTIC, READS SQL DATA).
  • Use DELIMITER only in the CLI; CREATE OR REPLACE FUNCTION updates one in place.
  • Keep functions small; avoid per-row queries and function calls on indexed columns in WHERE.

Next: Stored Procedures