Stored functions

A function is a special type of stored program. A function can return a value, and all parameters are of the IN type.

Function structure

The structure of a function is as follows. Compared with stored procedures, a function must contain 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

You can use the CREATE FUNCTION statement to create a function. The following conditions must be met:

• You must specify the return value of the function.

• All functions must contain at least one RETURNS statement.

• To call a function as an expression, it must be used in combination with other statements.

Here is an example:

CREATE FUNCTION my_func (c1 CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
     RETURN CONCAT('Thank ',c1,'!');
Query OK, 0 rows affected 

SELECT my_func('You');
+----------------+
| my_func('You') |
+----------------+
| Thank You!     |
+----------------+
1 row in set