Create a MySQL function on Cloud Server

This guide details the use of DELIMITER to create MySQL functions on Cloud Server Infomaniak.

Preamble

• When creating functions or procedures stored in MySQL, it is crucial to understand the role of delimiters.
• The correct use of delimiters is essential to avoid syntax errors that may occur due to the presence of several SQL instructions in a single function or procedure definition.

Understanding the Delimiter

A delimiter is a character or sequence of characters used to separate SQL instructions in a script. By default, MySQL uses the semicolon (;) as a delimiter. However, when creating functions, stored procedures or triggers that contain multiple SQL instructions, it is necessary to temporarily change the delimiter to avoid syntax errors.

When you create a function, procedure or trigger, you often need to use multiple SQL instructions inside the block BEGIN...END The semicolon (;) being also used to complete these internal instructions, MySQL could interpret the first semicolon as the end of the function definition, which would result in a syntax error. To circumvent this problem, you need to change the delimiter during the function definition.

Create a simple function using custom delimiters

Before setting the function, you must tell MySQL that you are going to use another delimiter. In the example below, $$ is used as a new delimiter:

DELIMITER $$

With the new delimiter in place, you can now set your function. The CREATE FUNCTION includes the body of the function, where you can use internal SQL instructions separated by semicoloned dots without this causing problems:

CREATE FUNCTION hello_world()
RETURNS TEXT
LANGUAGE SQL
BEGIN
RETURN 'Hello World';
END;
$$

In this example:

• CREATE FUNCTION hello_world() : declares the beginning of the definition of the function hello_world.
• RETURNS TEXT : specifies the type of data that the function returns.
• LANGUAGE SQL : indicates that the language used for the function is SQL.
• BEGIN ... END : encapsulates the function code. Inside, the semicolon is used to separate SQL instructions.
• RETURN 'Hello World'; : SQL statement that returns the string Hello World.

After setting the function, return the delimiter to its default state (the semicolon). This allows you to continue to run the usual SQL instructions in your following scripts:

DELIMITER ;