This MySQL article explains how to create and drop functions in MySQL with syntax and examples. Stored functions are defined in a similar manner to stored procedures, except that the command to use is the CREATE FUNCTION command.
And while it isn’t mandatory for a stored procedure to return output to the caller, stored functions must necessarily produce a return value.
DELIMITER // CREATE FUNCTION today() RETURNS VARCHAR(255) BEGIN RETURN DATE_FORMAT(NOW(), '%D %M %Y'); END//
As with the CREATE PROCEDURE command, the CREATE FUNCTION command must be followed by the name of the stored function. The same rules that govern procedure names also apply to function names. Input parameters to the function, if any, appear within parentheses following the function name, together with their data type. The function’s return value (only a single return value is possible) is represented by a mandatory RETURNS clause that follows the parentheses; this RETURNS clause specifies the data type of the return value.
The main body of the function can contain SQL statements, variable definitions, conditional tests, loops, and error handlers. It must also include a RETURN statement, which specifies the value to return to the caller. However, because stored functions cannot return result sets, take care to ensure that your RETURN statement does not return the output of a SELECT (or any other command that returns a result set).
When MySQL encounters a RETURN statement inside a stored function, it halts processing at that point and exits the function with the specified return value.
To invoke a stored function, you don’t need to use the CALL command; instead, use the function name within a SQL statement, as you would for any other built-in function.
To remove a stored function, use the DROP FUNCTION command with the function name as argument:
DROP FUNCTION today();
Create stored functions
To view the body of a specific stored function, use the SHOW CREATE FUNCTION command with the function name as argument.
Because stored functions use a separate RETURNS clause to define their output, all the parameters that appear within the parentheses in the function definition are assumed to be input parameters, and the OUT and INOUT keywords are not required (or supported) for the input argument list. These input parameters can then be manipulated or used for calculations within the function body.
DELIMITER // CREATE FUNCTION get_circle_area(radius INT) RETURNS FLOAT BEGIN RETURN PI() * radius * radius; END//
You can now pass this function the length of a circle’s radius and receive the corresponding circle area.
Stored functions can also manipulate tables, just like stored procedures.
DELIMITER // CREATE FUNCTION add_flight_dep(fid INT, depday INT, deptime TIME) RETURNS INT BEGIN INSERT INTO flightdep (FlightID, DepDay, DepTime) VALUES (fid, depday, deptime); RETURN 1; END//