How to Create Function in MariaDB

In this MariaDB Tutorial, we will learn, how to create a function in MariaDB. Let us see, the “MariaDB create function” and the below topics.

• MariaDB create function example
• MariaDB Function Example with SET variable
• MariaDB Drop Function
• How to create function with parameters in MariaDB
• Create function return table in MariaDB
• MariaDB create function declare
• MariaDB Function Select Into Variable
• MariaDB Function If
• MariaDB create function error 1064
• How to create function delimiter in MariaDB
• MariaDB create function deterministic

MariaDB create function example

A stored program is a function in MariaDB that takes a parameter and returns the value. The CREATE FUNCTION command is used to create a new function in MariaDB, the full syntax of the command is given below.

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

BEGIN

   declare_section

   execution_section

END;

• DEFINER clause
  • This one is Optional. The definer clause is used to define the user who creates the function. So to use the different definer, we need to write the DEFINER keyword with user_name, and this user_name is the new definer of the function.
• Function_name
  • The name that we want to give to a new function in MariaDB.
• Parameter_name
  • More than one parameter can be provided to the function. While creating a new function, all parameter needs to be the input parameters.
• Return_datatype
  • We mention what kind of value the function will return, after doing some calculation or processing, if the function returns the integer, then the data type of the function will be INT type.
• LANGUAGE SQL
  • When LANGUAGE SQL is specified, then it determines that function will return only one result given a list of parameters to the function.
• NOT DETERMINISTIC
  • It is just the opposite of the clause LANGUAGE SQL, It may give different results provided the list of parameters to the function.
• CONTAINS SQL
  • CONTAINS SQL clause is a default clause and an instructive clause that instruct MariaDB that the function is going to contain SQL.
• READS SQL DATA
  • It is an instructive clause that instructs the MariaDB function that reads data with help of SELECT statements but it will not change the data.
• MODIFIES SQL DATA
  • It is an instructive clause that instructs the MariaDB function to change SQL data using data definition language statements like INSERT, DELETE, UPDATE.
• Declare_section
  • In the declare section, local variables are declared.
• Execution_section
  • In the execution_section, where SQL query or code is written.

Let’s understand the use of the above syntax using an example in MariaDB.

DELIMITER //  
CREATE FUNCTION Calculation (new_value INT)  
RETURNS INT DETERMINISTIC  
BEGIN  
   DECLARE total INT;  
   SET total =1000;  
   label2: WHILE total <= 350 DO  
     SET total = total + new_value;  
   END WHILE label2;  
   RETURN total;  
END; //  
DELIMITER ;  

SELECT Calculation(150);

We’ve introduced a new function called Calculation with a new parameter called new_value and a data type of INT under the BEGIN and END sections, as well as a local variable called TOTAL with a data type of INT under the BEGIN and END sections, as can be seen in the above query.

We created a total variable in the Begin statement and set a condition that if the total variable’s value is greater than or equal to 350, it would add a new value to the total variable.

So, when utilizing the SELECT statement to call the new_value as 150 of the CALCULATION function, it will check whether the new number is more or equal to 350. If it is, it will add to the result set; else, it will return output as 1000.

Also, check: How to Create Trigger in MariaDB

MariaDB Function Example with SET variable

In this section, we will learn how to use the FUNCTION example with the SET variable in MariaDB, which is presented using syntax and examples.

The FUNCTION is mostly utilized in the store program, which allows arguments to be sent in and a value to be returned. The following is the syntax for the FUNCTION with the SET variable:

SYNTAX:

  CREATE   
[ DEFINER = { CURRENT_USER | user_name } ]   
FUNCTION func_name [ (parameter datatype) ]  
RETURNS return_data_type [ LANGUAGE SQL  
| DETERMINISTIC  
| NOT DETERMINISTIC  
  
BEGIN  
   declaration_section  
   executable_section  
END;   

The following is an example of a MariaDB FUNCTION with the SET example:

EXAMPLE:

DELIMITER //  
CREATE FUNCTION Calculation_of_Heart (new_heart INT)  
RETURNS INT DETERMINISTIC  
BEGIN  
   DECLARE heart_beat INT;  
   SET heart_beat =180            ;  
   label: WHILE heart_beat <= 350 DO  
     SET heart_beat = heart_beart + new_heart;  
   END WHILE label2;  
   RETURN total;  
END; //  
DELIMITER ;  

SELECT Calculation_of_Heart(150);

We have created a new function called Calculation_of_Heart with a new parameter called new_heart datatype as INT, as shown in the previous query. After this, under the BEGIN and END statements, we’ve established a local integer variable called heart_beat and assigned a value of 180. If the heart_beat increases or equals 350, it will be added to the result as a new_value.

So, we invoked the Calculation_of_Heart function with a new value of 150 in the resultset. It will verify the condition to see if the new value of 150 is more than or equal to 350, if so, the new value will become the output value in the result set, otherwise, the value will be 180.

Read: MariaDB Rename Table

MariaDB DROP Function

Here we will learn how to drop a function from the database in MariaDB. And we will be describing its syntax and an example.

In MariaDB, we can easily drop a function from the database. For this, we just need to define the function_name that we want to drop from the database with the DROP function in the beginning. The DROP FUNCTION syntax in MariaDB is as follows:

SYNTAX

DROP FUNCTION [IF EXISTS] FUNC_NAME;

The syntax explanation:

• func_name: It’s the name of the function that we want to drop.
• if exists: The IF EXISTS clause’s principal purpose is to drop function_name from the database if it already exists; otherwise, it will throw an error. We will, however, receive warnings if we attempt to delete function_name from a database that has already been erased.

The following is an example of how to drop a function:

EXAMPLE:

DROP FUNCTION IF EXISTS CALCULATION_OF_HEART;

As we see in the preceding query, we have a drop function called CALCULATION_OF_HEART from the database by using the IF EXISTS clause.

MariaDB create function with parameter

The MariaDB function can accept the parameters, this parameter can be IN for Input and OUT for output.

Let’s create the new function as sumfunc using the below query.

DELIMITER //

CREATE FUNCTION sumfunc (x INT )
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE sum INT;
   SET sum = 0;
   label1: WHILE sum <= 3000 DO
   SET sum = sum + x;
   END WHILE label1;
   RETURN sum;
END; //
DELIMITER ;

Now, we can call the above function using the following code.

SELECT sumfunc(100);

This is how to create a function with parameters in MariaDB.

Read: How to Drop Column from MariaDB Table

MariaDB Function Select Into Variable

Here we’ll discuss how to utilize the SELECT INTO statement to store variables in the MariaDB FUNCTION. We will illustrate this using syntax and an example.

First, let’s have a look at the STATES_OF_USA table by the following query:

SELECT * FROM STATES_OF_USA;

In MariaDB, the SELECT statement retrieves all records from the STATES_OF_USA table.

The following is an example of a MariaDB FUNCTION using the SELECT INTO statement using a variable:

DELIMITER
CREATE OR REPLACE FUNCTION get_STATE_details(in_STATE_id INT)
RETURNS VARCHAR(30)
BEGIN
DECLARE l_STATE_name VARCHAR(30);

SELECT STATE_NAME INTO l_STATE_name
FROM STATES_OF_USA
WHERE STATE_id=in_STATE_id;
RETURN l_STATE_name;

END; $$
DELIMITER;

SELECT get_STATE_details(10);

Using the CREATE OR REPLACE function, we constructed a function named get_STATE_details with an argument called in STATE_id of data type INT. And this function will return a string variable of data type VARCHAR(30) in the above query.

Within the function body, we used the SELECT INTO statement in the BEGIN and END statements to store the value of the STATE_NAME column in the 1_STATE_name column from the STATES_OF_USA database. So, based on the WHERE condition user input value of the STATE_ID column is equal to the in_STATE_id column. This will bring up a list of results.

So, to execute the get_STATE_details function, we used the SELECT query with parameter 10; this specifies that the output would be GEORGIA in the result set. The number of rows in the STATES_OF_USA database will decide the output, which will be dependent on the STATE_ID column.

MariaDB create function return table

In MariaDB, it is not possible to return the table as output, MariaDB can return only the datatypes.

But, it is possible in some other relational databases like SQL Server. See SQL Server function return table.

MariaDB Function If

We will learn how to utilise the IF statement in the MariaDB FUNCTION in this sub-topic, which will be explained with syntax and an example.

The IF-THEN-ELSE expression in MariaDB is used to execute code if its expression is TRUE, and another code if it evaluates to FALSE.

The MariaDB IF expression in the FUNCTION has the following syntax:

SYNTAX:

IF(condition_1 )then
[..statement to be executed when condition_1 is TRUE..]
ELSEIF(condition_2) then
[..statement to be executed when condition_1 is FALSE and condition_1 is TRUE..]
ELSE
[statement to be executed when both condition_1 and condition_2 are false..]

The syntax explanation:

• ELSEIF: When we wish to run a group of statements when condition_2 is TRUE and it’s optional, we can use the ELSEIF condition.
• ELSE: When none of the IF or ELSEIF conditions is evaluated to be TRUE, we can use the ELSE condition to execute a sequence of statements. It’s also optional.

NOTE:

• The IF-THEN-ELSE statement will execute the associated code and cease assessing the conditions once a condition is proven to be TRUE.
• If no condition is met, the IF-THEN-ELSE statement’s ELSE component is executed.
• It’s important to keep in mind that the ELSEIF and ELSE sections are optional.

Here, it is an example of the IF conditions in the MariaDB FUNCTION:

EXAMPLE:

DELIMITER //
CREATE FUNCTION DOLLAR_VALUE (value INT)
RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
   DECLARE market varchar(30);
   IF VALUE < 75 THEN
      SET market = 'BEARISH FOR DOLLAR';
   ELSEIF VALUE >= 75 AND VALUE <= 80 THEN
      SET market = 'NOR BEARISH OR BULLISH';
   ELSE
      SET market = 'BULLISH';
   END IF;
   RETURN market;
END; //
DELIMITER ;

SELECT DOLLAR_VALUE(70);

We built a function named DOLLAR_VALUE with the input VALUE and the data type INT in the preceding query. We constructed an IF-THEN-ELSE statement for the VALUE variable in the BEGIN and END statements.

If the VALUE variable in the IF statement is more than 75, the output will be BEARISH FOR DOLLAR. But if condition_2 is greater than or equal to 75 or less than or equal to 80, the output will be NOR BEARISH OR BULLISH. However, if both condition_1 and condition_2 are false, the outcome will be BULLISH.

So, to acquire the result set, we used the SELECT statement to execute the DOLLAR_VALUE function with a parameter of 70, which implies it will use condition_1 and return BEARISH IS DOLLAR as the output.

MariaDB create function declare

In MariaDB, we can create the function that will contain the DECLARE section where variables are defined. variables are used to hold the value, if the variable is defined as integer then it will store integer values.

Let’s create the function with declare section.

DELIMITER //

CREATE FUNCTION mulsumfunc (x INT,y INT)
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE sum INT;
   SET sum = 0;
   Label1: WHILE sum <= 3000 DO
   SET sum = SUM + x + y;
   END WHILE label1;
   RETURN sum;
END; //
DELIMITER ;

Now call the function with two values.

SELECT mulsumfunc(200,260);

Also, check: How to Add Column in MariaDB

MariaDB create function error 1064

Function error 1064 occurs due to the use of the wrong syntax in MariaDB, Let’s understand with the examples.

Run the code the below code.

DELIMITER //

CREATE FUNCTION mulsumfunc (x ,y INT )
RETURNS INT DETERMINISTIC

BEGIN

   DECLARE sum INT;
   SET sum = 0;
   label1: WHILE sum <= 3000 DO
   SET sum = SUM + x + y;
   END WHILE label1;
   RETURN sum;
END; //
DELIMITER ;

Look at the parameters passed to the function x and y, where x does not have any kind of type like a data type.

As we run the above code in the MariaDB prompt or GUI application like HeidiSQL, we get the error 1064.

As we can see the error occurred due to the wrong syntax or we haven’t followed the rule to write the syntax. The easy solution to this kind of error is to follow the documentation for writing the code when you have some kind of doubts.

Read: How to Create Table in MariaDB

MariaDB create function deterministic

The deterministic function produces only a single result for a list of parameters. If the result produced by the function is affected by the stored server, server variables, random numbers then it is non-deterministic.

Let’s run the code below to how the deterministic function works.

DELIMITER //
CREATE FUNCTION detsumfunc (x INT ,y INT,z INT )
RETURNS INT DETERMINISTIC
BEGIN
   DECLARE sum INT;
   SET sum = 0;
   label1: WHILE sum <= 3000 DO
   SET sum = SUM + x + y + z;
   END WHILE label1;
   RETURN sum;
END; //
DELIMITER ;

SELECT detsumfunc(20,20,20);

Read: How to Grant User Access to a MariaDB Database

MariaDB create function delimiter

In MariaDB, the default delimiter is a semicolon, When stored programs are created using the MariaDB prompt, we will have to differentiate between the outside delimiter and a delimiter inside a BEGIN END block.

To understand the use of delimiter better way, Let’s consider the following example.

CREATE FUNCTION DemoDelimitter() RETURNS INT DETERMINISTIC
BEGIN
 DECLARE x INT;
 SET x = 40;
 RETURN x;
END; 

If we run the above code, MariaDB will consider the end of the statement when it encounters the first semicolon at the end of DECLARE x INT line and the second is at the end of the statement. This one is not a full definition so it will generate an error.

The solution to this kind of error is to use a different delimiter using the DELIMITER command to specify the delimiter of any symbol that should be unique. // Double forward slash is a common delimiter that we use while creating the function.

Let’s use the // delimiter with the above code to fix the problem.

DELIMITER //

CREATE FUNCTION DemoDelimitter() RETURNS INT DETERMINISTIC
BEGIN
  DECLARE x INT;
  SET x = 40;
  RETURN x;
END 

//

DELIMITER ;

When we run the above code with a distinctive delimiter then we don’t get the error.

You may also like to read the following MariaDB tutorials.

• How to Create a Database in MariaDB
• How to create a user in MariaDB
• How To Check MariaDB Version
• MariaDB Vs SQL Server
• MariaDB ERROR 1064
• MariaDB Timestamp
• Replace Function in MariaDB
• MariaDB Substring
• MariaDB Cast

Here, we learned how to create a function in MariaDB with a few examples.

• MariaDB create function example
• MariaDB Function Example with SET variable
• MariaDB Drop Function
• MariaDB Function Select Into Variable
• MariaDB Function If
• MariaDB create function with parameters
• MariaDB create function return table
• MariaDB create function declare
• MariaDB create function error 1064
• MariaDB create function delimiter
• MariaDB create function deterministic