MariaDB Functions

MariaDB function is a stored program that is used to pass parameters into them and return a value.

We can create and drop functions in MariaDB.


# MariaDB Create Function

You can create your own function in MariaDB:

Syntax:


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; 

# Parameter Explanation

Example:

Create a function CalcValue in MariaDB database.


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 ; 

DEFINER clause: it is an optional clause. 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: It specifies the name to assign to this function in MariaDB.

return_datatype: It specifies 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.

MariaDB Functions 1

# Verification

You can see that program is executed successfully and a new function is created.

MariaDB Functions 2

Now you can reference your new function as follows:

MariaDB Functions 3

# MariaDB DROP Function

You can drop your created function very easily from your database.

Syntax:


DROP FUNCTION [ IF EXISTS ] function_name; 

# Parameter Explanation

function_name: It specifies the name of the function that you want to drop.

Example:

We have created a function name "CalcValue". Now drop the function.


DROP FUNCTION CalcValue; 
MariaDB Functions 4

Now you can see that function is deleted and not present in the list anymore.

MariaDB Functions 5