MySQL Stored routines (procedures and functions) Create a Function
Example
The following (trivial) example function simply returns the constant INT value 12.
DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
RETURN 12;
END;
||
DELIMITER ;
The first line defines what the delimiter character(DELIMITER ||) is to be changed to, this is needed to be set before a function is created otherwise if left it at its default ; then the first ; that is found in the function body will be taken as the end of the CREATE statement, which is usually not what is desired.
After the CREATE FUNCTION has run you should set the delimiter back to its default of ; as is seen after the function code in the above example (DELIMITER ;).
Execution this function is as follows:
SELECT functionname();
+----------------+
| functionname() |
+----------------+
| 12 |
+----------------+
A slightly more complex (but still trivial) example takes a parameter and adds a constant to it:
DELIMITER $$
CREATE FUNCTION add_2 ( my_arg INT )
RETURNS INT
BEGIN
RETURN (my_arg + 2);
END;
$$
DELIMITER ;
SELECT add_2(12);
+-----------+
| add_2(12) |
+-----------+
| 14 |
+-----------+
Note the use of a different argument to the DELIMITER directive. You can actually use any character sequence that does not appear in the CREATE statement body, but the usual practice is to use a doubled non-alphanumeric character such as \\, || or $$.
It is good practice to always change the parameter before and after a function, procedure or trigger creation or update as some GUI's don't require the delimiter to change whereas running queries via the command line always require the delimiter to be set.
