Stored Procedures in MariaDB: Smarter, Easier and More Powerful

MariaDB supports stored procedures written in procedural SQL. Which essentially means “SQL with IF’s and loops”.

Most DBMSs do the same, but every project supports different, incompatible syntaxes. MariaDB implemented Oracle’s dialect, called PL/SQL.

The base of the current procedural language was implemented in MySQL, before MariaDB existed. As far as I know, MySQL Community Edition improved triggers a bit, but never improved the procedural language itself. MariaDB developed several features and optimisations and made stored procedures and triggers faster and easier to develop and maintain. Some PL/SQL features were included in the native language, with the same or similar syntax. Other features were developed specifically for MariaDB procedural SQL.

Other implementations of procedural SQL have a specific name, but MariaDB tends to refer to its language as non-Oracle mode. It was pointed out that it’s not idea to use a competitor’s name to indicate a native feature, but apparently this is not going to change.

Oracle mode: PL/SQL in MariaDB

It can be used by adding the ORACLE flag to the sql_mode variable, before writing a stored procedure. This is pretty simple:

SET SESSION sql_mode = CONCAT(@@sql_mode, ',ORACLE');
DELIMITER ||
CREATE OR REPLACE PROCEDURE test() AS BEGIN END ||
DELIMITER ;

PL/SQL support is partial, but it should be sufficient for most purposes. The most important missing feature is built-in packages. There is a task to move PL/SQL support to a dedicated plugin: MDEV-23288.

MariaDB native procedural SQL

Here I’m going to list some important improvements that MariaDB implemented to its procedural SQL. These improvements are not in MySQL.

OR REPLACE

In MariaDB, all CREATE statements can also be written as CREATE OR REPLACE.

This might look like a cosmetic feature, but it isn’t. It’s actually extremely important to maintain production servers, because MariaDB doesn’t support transactional DDL. In other words, any CREATE, ALTER or DROP statement will implicitly commit the active transaction, if any. They are crash-safe, but a sequence of DDL statements cannot be executed atomically. The clients will be able to see every intermediate state.

But with CREATE OR REPLACE, replacing an object (like a table or a trigger) is an atomic operation. At no point in time the object will not exist. Consider this example:

DELIMITER ||
CREATE FUNCTION f1()
    RETURNS TEXT
BEGIN
    RETURN 'Something important';
END ||

CREATE OR REPLACE FUNCTION f1()
    RETURNS TEXT
BEGIN
    RETURN 'Something MORE important!';
END ||
DELIMITER ;

At any point in time, one version of f1() will exist. Applications will not get any error, when they try to run it.

Anonymous blocks

Anonymous blocks contain procedural SQL that needs to be executed one time, without the need of creating a stored procedure. Here is an example:

DELIMITER ||
BEGIN NOT ATOMIC
    IF NOT @x > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid @x';
    END IF;
    WHILE @x > 0 DO
        INSERT INTO randomness (random_number) VALUES (RAND());
        SET @x = @x + 1;
    END WHILE;
END ||
DELIMITER ;

The typical use case for this is to implement schema versioning in SQL. You can have complex logic that checks the current schema, and makes the necessary modification. This seems to me particularly useful in the extremely unfortunate situations where you can’t rely on having a consistent schema, because too many people have access to it and have the permissions to run DDL statements. For example, when your application is distributed to customers and runs on their servers.

However, nothing prevents you from using anonymous blocks in other ways. If the logic you need is easier to implement in procedural SQL than in your host language, like Perl, you can send a whole anonymous block to MariaDB as if it was a single query.

Packages

Packages contain stored procedures and functions. They are similar to classes: with packages, we can define public methods, private methods and private variables.

As long as the signature of public procedures and functions is not modified, we can recreate a package atomically, with one statement: CREATE OR REPLACE PACKAGE BODY.

I will explain and demonstrate packages in a future article.

Aggregate functions

An aggregate function is a function like COUNT(), AVG() or MAX(). It takes any number of rows as input, usually only one column. It loops over the rows, and produces a single output value. MariaDB allows us to write stored aggregate functions.

A typical example for this is to implement a mean. MariaDB, like other DBMSs, has built-in AVG() and STDDEV() functions for the arithmetic mean and the standard deviation. But in some situations, other mean types can be more adequate, like the geometric mean or the harmonic mean. I implemented some means in the mariadb-stats library.

I will write a dedicate article to explain aggregate functions and show some examples.

Implicit cursors

MariaDB supports the FOR loop. It can be used in various ways, but my favourite is to loop over a query without declaring a cursor:

DELIMITER ||
BEGIN NOT ATOMIC

DECLARE r ROW (sch VARCHAR(64));
FOR r IN (SELECT SCHEMA_NAME AS sch FROM information_schema.SCHEMATA)
DO
    EXECUTE IMMEDIATE SFORMAT('SELECT ''{0}'', COUNT(*) > 0 FROM {0}.config LIMIT 1;', r.sch);
END FOR;

END;
||

If you understand what this block does… please ✎ drop us a comment! If you don’t understand EXECUTE IMMEDIATE, keep reading. If you don’t understand the SFORMAT() function, see How to compose strings in MariaDB.

Now that we can do this, is it still necessary to create cursors? Usually, I’d say it isn’t. But there are cases when you want to have a cursor to do something more complex than looping over it. In those cases, FOR might not be what you need.

Cursors with parameters

In MySQL, cursors don’t have parameters. A cursor is defined for a query that must be known when the cursor is declared, and can’t be dynamic.

A workaround exists but it’s verbose and, frankly, ugly. You can define a cursor that reads all rows from a view. Later, you can redefine the view to select the rows you actually need.

MariaDB supports cursor parameters. You can use them in this way:

DECLARE cur_customers CURSOR (in_city VARCHAR(100), in_country VARCHAR(100)) FOR
    SELECT id, name, surname, city, country
        FROM user
        WHERE (in_city IS NULL OR city = in_city)
        OR (in_country IS NULL OR country = in_country)
;
...
OPEN cur_customers (NULL, 'Scotland');

As you can see in this example, I use the NULL trick to make cursor parameters optional. In the case above, in_city IS NULL is true, so the condition on the city column is not evaluated at all (it can’t slow down the query). On the contrary, in_country is not NULL, so the condition on the country column is evaluated.

EXECUTE IMMEDIATE

The old EXECUTE syntax is the following:

PREPARE stmt FROM 'SELECT id FROM user WHERE name = ?';
EXECUTE stmt USING @name;
DEALLOCATE PREPARE stmt;

This syntax makes sense if you want to prepare a statement once and run it multiple times. But sometimes you prepare it once and run it once. It won’t help performance, but that is the only way to compose an SQL query dynamically inside routines or anonymous blocks, when parameters are not enough.

Apart from being unnecessarily verbose, the old syntax prevents recursion, where a procedure prepares the statement stmt, then it calls itself, and then tries again to create a statement with the same name.

The new syntax is the following:

EXECUTE IMMEDIATE
    'SELECT id FROM user WHERE name = ?'
    USING @name;

Conclusions

This is only a selection of the features that I consider most useful. MariaDB extends stored routines in many ways, making them much more useful than they are in MySQL. I know: nowadays, stored procedures have a bad reputation. But give them a try, and you’ll probably find them useful in some practical cases.

Federico Razzoli