MySQL – Stored Function

MySQL Stored Function

The concept of a stored function in MySQL refers to a set of SQL statements that perform some task or operation and return a single value. In MySQL, it is one of the types of stored programs. When creating a stored function, ensure that you have the CREATE ROUTINE database privilege. This function is generally used to encapsulate business rules or formulas that are reusable in stored programs or SQL statements.

There are several differences between the stored function and the procedure in MySQL, including the following:

Only the IN parameter may be specified in the function parameter, whereas the IN, OUT, and INOUT parameters may be specified in the procedure parameter.
In the function header, only one value can be returned by the stored function.
SQL statements may also call the stored function.
A result set may not be produced.

syntax

DELIMITER $$  

CREATE FUNCTION fun_name(fun_parameter(s))  
RETURNS datatype  
[NOT] {Characteristics}  
fun_body;

Parameter Used

fun_name: This is the name of the stored function that you wish to create in a database. In MySQL, it should not be the same as the built-in function name.

fun_parameter: It contains a list of parameters that are used by the function body. There is no provision for specifying IN, OUT, and INOUT parameters.

datatype: The return value of the function is a data type. Any valid MySQL data type should be acceptable.

characteristics: When the characteristics (DETERMINISTIC, NO SQL, or READS SQL DATA) are defined in the declaration, the CREATE FUNCTION statement is accepted.

fun_body: The parameter contains a set of SQL statements that perform the operations. There must be at least one RETURN statement. Upon execution of the return statement, the function will be automatically terminated. Below is the function body: BEGIN — SQL statements END $$ DELIMITER