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.
Advertisements