Function in MariaDB

by

MariaDB Functions
A function in MariaDB can be simply understood as a stored program that is used to pass parameters into them and that returns a value. MariaDB allow the users to create their own function. It also facilitates to eliminate or remove an already existing function from the MariaDB database.

MariaDB Create Function:

Syntax: To create a function in MariaDB database. 

CREATE   
[ DEFINER = { CURRENT_USER | user_name } ]   
FUNCTION function_name [ (parameter data type, parameter data type) ]  
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;

Parameters:
DEFINER clause: It is an optional clause which is used to specify a definer, by including the DEFINER clause where user_name is the definer for the function. By default, the definer is the user that created the function.
function_name: It is used to specify the name of the function.
return_datatype: It is used to specify the data type of the function’s return value.
LANGUAGE SQL: It is used to specify the Language SQL in the syntax for portability. It has no impact on the function.
DETERMINISTIC: It is used to specify that the function will always return one result given a set of input parameters.
NOT DETERMINISTIC: It is used to specify that the function may return a different result given a set of input parameters.
CONTAINS SQL: It is used to specify an informative clause to tell that the function contains SQL, but the database does not verify that this is true.
NO SQL: It is used to specify an informative clause that is not used and will have no impact on the function.
READS SQL DATA: It is used to specify an informative clause to tell that the function will read data using SELECT statements but does not modify any data.
MODIFIES SQL DATA: It is used to specify an informative clause to tell that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.
declaration_section: It is used to specify the place in the function to declare local variables.
executable_section: It is used to specify the place in the function to enter the code for the function.

Example: 

DELIMITER //  
CREATE FUNCTION Calculate ( start_value INT )  
RETURNS INT DETERMINISTIC  
BEGIN  
   DECLARE total_sum INT;  
   SET total_sum = 0;  
   label1: WHILE total_sum <= 100 DO  
     SET total_sum = total_sum + start_value;  
   END WHILE label1;  
   RETURN total_sum;  
END; //  
DELIMITER ;

Explanation:
Here we are creating a function Calculate in MariaDB database.

MariaDB DROP Function:

In MariaDB, we can also drop an already existing function from the database.

Syntax: To drop a function from MariaDB database. 

DROP FUNCTION [ IF EXISTS ] func_name;

Parameters:
func_name: It is used to specify the name of the function to drop.

Example: 

DROP FUNCTION Calculate;