A function can be regarded as a special stored program. A function can return a value and all parameters in a function are of the IN type.
Function structure
Compared with a stored procedure, a function contains at least one RETURNS clause. The following sample code shows the function structure:
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]
BEGIN -- Start execution
SQL statement; [ SQL statement; ...]
END; -- End 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 }
}
Create a function
You can use the CREATE FUNCTION statement to create a function that meets the following requirements:
A function must be configured with a return value.
A function must contain at least one
RETURNSclause.A function must be used together with other statements to serve as an expression.
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
