fastread homefastrread library fastread menu

MariaDB : Functions

Tutorial by:      Date: 2016-04-18 05:51:10

❰ Previous Next ❱

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.

 

❰ Previous Next ❱


MariaDB

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website