totn MariaDB

MariaDB: Functions

This MariaDB tutorial explains how to create and drop functions in MariaDB with syntax and examples.

What is a function in MariaDB?

In MariaDB, a function is a stored program that you can pass parameters into and then return a value.

Create Function

Just as you can create functions in other languages, you can create your own functions in MariaDB. Let's take a closer look.

Syntax

The syntax to create a function in MariaDB is:

CREATE 
[ DEFINER = { CURRENT_USER | user_name } ] 
FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype [ LANGUAGE SQL
                        | DETERMINISTIC
                        | NOT DETERMINISTIC
                        | { CONTAINS SQL 
                          | NO SQL
                          | READS SQL DATA
                          | MODIFIES SQL DATA }
                        | SQL SECURITY { DEFINER | INVOKER }
                        | COMMENT 'comment_value'

BEGIN

   declaration_section

   executable_section

END;
DEFINER clause
Optional. If not specified, the definer is the user that created the function. If you wish to specify a different definer, you must include the DEFINER clause where user_name is the definer for the function.
function_name
The name to assign to this function in MariaDB.
parameter
One or more parameters passed into the function. When creating a function, all parameters are considered to be IN parameters (not OUT or INOUT parameters) where the parameters can be referenced by the function but can not be overwritten by the function.
return_datatype
The data type of the function's return value.
LANGUAGE SQL
It is in the syntax for portability but will have no impact on the function.
DETERMINISTIC
It means that the function will always return one result given a set of input parameters.
NOT DETERMINISTIC
It means that the function may return a different result given a set of input parameters. The result may be affected by table data, random numbers or server variables.
CONTAINS SQL
It is the default. It is an informative clause that tells MariaDB that the function contains SQL, but the database does not verify that this is true.
NO SQL
An informative clause that is not used and will have no impact on the function.
READS SQL DATA
An informative clause that tells MariaDB that the function will read data using SELECT statements but does not modify any data.
MODIFIES SQL DATA
An informative clause that tells MariaDB that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.
declaration_section
The place in the function where you declare local variables.
executable_section
The place in the function where you enter the code for the function.

Example

Let's look at an example that shows how to create a function in MariaDB:

DELIMITER //

CREATE FUNCTION CalcValue ( starting_value INT )
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE total_value INT;

   SET total_value = 0;

   label1: WHILE total_value <= 3000 DO
     SET total_value = total_value + starting_value;
   END WHILE label1;

   RETURN total_value;

END; //

DELIMITER ;

You could then reference your new function as follows:

SELECT CalcValue (1000);

Drop Function

Once you have created your function in MariaDB, you might find that you need to remove it from the database.

Syntax

The syntax to a drop a function in MariaDB is:

DROP FUNCTION [ IF EXISTS ] function_name;
function_name
The name of the function that you wish to drop.

Example

Let's look at an example of how to drop a function in MariaDB.

For example:

DROP FUNCTION CalcValue;

This example would drop the function called CalcValue.

Share on: