Functions

A function can be regarded as a special subprogram. 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:

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

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 }
}

routine_body:
    Valid SQL routine statement

Benefits

A function has basically the same advantages as a subprogram. However, a function returns a value. In this way, a function can be used as an expression. You can define a feature as a function, and then use the function in an expression. This enables more flexible and richer usage of functions and better adapts to programming habits.

Create a function

You must use a CREATE statement to create a function with the following conditions met:

• A function must be configured with a return value.

• A function must contain at least one RETURNS clause.

  obclient> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
       RETURN CONCAT('Hello, ',s,'!');/
  Query OK, 0 rows affected
  
  obclient> SELECT hello('world');/
  +----------------+
  | hello('world') |
  +----------------+
  | Hello, world!  |
  +----------------+
  1 row in set
  

• A function must be used together with other statements to serve as an expression.