A function can be considered a special kind of stored program. Functions can have return values, and all parameters are of the IN type.
Structure of the function
The structure of a function is as follows. Compared with a stored procedure, a function must include at least one RETURNS clause:
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]
BEGIN -- Start execution
SQL statement; [ SQL statement; ...]
END; -- End the execution.
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
Creating a function
Functions are created using the CREATE FUNCTION statement and must meet the following requirements:
Consider setting the return value for the function.
Every function must have at least one
RETURNSstatement.Functions must be called as expressions and used together with other statements.
An example is as follows:
obclient> CREATE FUNCTION my_func (c1 CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Thank ',c1,'!');
Query OK, 0 rows affected
obclient> SELECT my_func('You');
+----------------+
| my_func('You') |
+----------------+
| Thank You! |
+----------------+
1 row in set