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

Preamble

• When creating functions or stored procedures in MySQL, it is crucial to understand the role of delimiters.
• The correct use of delimiters is essential to avoid syntax errors that can occur due to the presence of multiple SQL statements 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 statements within the BEGIN...END block. Since the semicolon (;) is also used to terminate these internal statements, MySQL might interpret the first semicolon as the end of the function definition, resulting in a syntax error. To avoid this issue, you must change the delimiter during the function definition.

Create a simple function using custom delimiters

Before defining the function, you must tell MySQL that you will use a different delimiter. In the example below, $$ is used as the new delimiter:

DELIMITER $$

With the new delimiter in place, you can now define your function. The CREATE FUNCTION includes the body of the function, where you can use internal SQL statements separated by semicolons without any issues:

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 data type that the function returns.
• LANGUAGE SQL : specifies that the language used for the function is SQL.
• BEGIN ... END: encapsulates the function code. Inside, the semicolon is used to separate SQL statements.
• RETURN 'Hello World'; : SQL statement that returns the string Hello World.

After defining the function, reset the delimiter to its default state (the semicolon). This allows you to continue executing the usual SQL instructions in your subsequent scripts:

DELIMITER ;