A function is a special type of stored program that can return a value and has only IN parameters.
Function structure
The structure of a function is as follows. Compared to stored procedures, it must include at least one or more RETURNS clauses:
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]
BEGIN -- Start of execution
SQL statement; [ SQL statement; ...]
END; -- End of 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
A function is created using the CREATE FUNCTION statement and must meet the following conditions:
The function must have a return value.
All functions must have at least one
RETURNSstatement.To call a function as an expression, it must be used in conjunction with other statements.
Here is an example:
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