Stored Procedures and Stored Functions with MariaDB Xpand

Overview

Stored routines can be used to execute procedural code on the database server. Stored routines refers to stored procedures and stored functions. MariaDB Xpand supports both stored procedures and stored functions.

This documentation applies to both Xpand topologies. Most examples are designed for the Xpand Performance Topology. However, the examples explicitly mention any differences that are relevant for the Xpand Storage Engine Topology.

Stored Procedures and Stored Functions

When using the Xpand Performance topology, MariaDB Xpand supports both stored procedures and stored functions.

When using the Xpand Storage Engine topology, MariaDB Enterprise Server supports both stored procedures and stored functions.

Languages

When using the Xpand Performance topology, MariaDB Xpand supports the following languages for stored procedures and stored functions:

  • MariaDB SQL/PSM

When using the Xpand Storage Engine topology, MariaDB Enterprise Server supports the following languages for stored procedures and stored functions:

  • MariaDB SQL/PSM

  • MariaDB SQL/PL (a compatible subset of Oracle PL/SQL)

SQL Modes

The SQL mode is configured with the sql_mode system variable.

When using the Xpand Performance topology, MariaDB Xpand supports the following SQL modes for stored routines:

  • The default SQL mode is supported.

  • The TRADITIONAL SQL mode is supported.

When using the Xpand Storage Engine topology, MariaDB Enterprise Server supports the following SQL modes for stored routines:

  • The default SQL mode is supported.

  • The TRADITIONAL SQL mode is supported.

Synchronization

When using the Xpand Performance topology, MariaDB Xpand automatically synchronizes stored routines on all Xpand nodes.

When using the Xpand Storage Engine topology, MariaDB Enterprise Server does not automatically synchronize stored routines on all ES nodes. ES nodes require a replication solution, such as MariaDB Replication to synchronize stored routines. Without replication, stored routines need to be created separately on each ES node.

Unsupported Statements

When using the Xpand Performance topology, MariaDB Xpand does not support the following statements in stored routines:

  • BACKUP

  • SAVEPOINT

  • RELEASE SAVEPOINT

  • CASE (SQL:2003 syntax)

  • RESIGNAL

Special Handling

Duplicate Labels

When using the Xpand Performance topology, MariaDB Xpand does not report an error when a label name is used multiple times in a stored routine.

Call Stored Procedure from Prepared Statement

When using the Xpand Performance topology, MariaDB Xpand does not support calling a stored routine from a prepared statement.

Resources

Application Development

MariaDB Knowledge Base