CREATE FUNCTION

You can use the CREATE FUNCTION statement to create a stored function.

The syntax is as follows:

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] 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

By default, stored functions are associated with the default database. To associate a stored function with a specified database, use database_name.sp_name to specify the database name.

To call a stored function, reference it in an expression. This function will return a value during expression evaluation.

To use the CREATE FUNCTION statement, you must have the CREATE ROUTINE privilege. By default, OceanBase Database automatically grants the ALTER ROUTINE and EXECUTE privileges to the creator of a stored routine such as a stored procedure or a stored function. If the DEFINER clause is used, the required privileges depend on the value of user.

The DEFINER and SQL SECURITY clauses specify the security context used for checking the access privileges when the routine is executed.

IF NOT EXISTS avoids creating a routine that already exists. If the name of a stored routine is the same as that of a built-in SQL function, a syntax error occurs. In this case, you can add a space between the routine name and the parentheses when you define or call the routine. Therefore, do not create your stored routine by using the name of an existing SQL function.

The proc_parameter parameter list must always exist in the parentheses. If no parameter exists, use empty parentheses () to indicate an empty parameter list. The parameter names are case-insensitive. All parameters of a stored function are IN parameters.

The RETURNS clause applies only to stored functions. It specifies the return type of a stored function. The function body must contain the RETURN value statement. If the RETURN statement returns a value of a different type, this value will be forcibly converted into the specified type. For example, if a function specifies an ENUM value or a SET value in the RETURNS clause but the RETURN statement returns an integer, the value returned by the function is a string of the ENUM member in the SET member set.

The parameter type and function return type can be declared as any valid data type. If CHARACTER SET exists, the COLLATE attribute can be used.

routine_body consists of a valid SQL statement. The SQL statement can be a simple statement such as SELECT or INSERT, or a compound statement written by using BEGIN and END. A compound statement can contain declarations, loops, and other control constructs. A stored function tends to use a compound statement unless the function body consists of a single RETURN statement.

The USE statement cannot be used in stored routines. When a routine is called, the USE database_name statement is implicitly executed to specify the default database for executing the routine. This statement will be revoked when the routine ends. To reference objects in a database other than the default database of the routine, you can use the database name.

If you define a routine in strict SQL mode but call it in non-strict mode, values will not be assigned to parameters of the routine in strict mode. If an expression passed to the routine is assigned in strict SQL mode, the routine must be called in strict mode.

The COMMENT attribute can be used to describe a stored routine. You can execute the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement to show the comments.

The LANGUAGE attribute indicates the language used to write the routine. This attribute applies only to SQL routines and is ignored by servers.

A routine is considered deterministic if it always generates the same result for the same input parameters, and indeterministic if otherwise. If the routine definition does not specify the DETERMINISTIC or NOT DETERMINISTIC keyword, the default value is NOT DETERMINISTIC. To declare a function as deterministic, you must explicitly specify DETERMINISTIC.

The following characteristics provide information about how the routine uses data:

• CONTAINS SQL indicates that the routine does not contain data read/write statements. This is the default value. For example, SET@x=1 or DO RELEASE_LOCK('abc') will be executed but it does not read or write data.

• NO SQL indicates that the routine does not contain SQL statements.

• READS SQL DATA indicates that the routine contains data read statements such as SELECT but does not contain data write statements.

• MODIFIES SQL DATA indicates that the routine contains statements that may write data, such as INSERT or DELETE.

SQL SECURITY can be set to DEFINER or INVOKER to specify whether the routine is executed by using the privileges of the specified account that has the privileges to access the database associated with the routine, or by using the privileges of the invoker. The default value is DEFINER. To call a routine, you must have the EXECUTE privilege on the routine.

The DEFINER clause specifies the account used to check the access privileges on the routine when the routine is executed. If the DEFINER clause exists, you must set user to an account in the 'user_name'@'host_name' format, such as 'admin'@'oblocalhost', or to the account obtained by using the CURRENT_USER() function. By default, if the DEFINER clause is omitted, the definer is the user who executes the CREATE FUNCTION statement. In the body of a stored routine that is defined by using SQL SECURITY DEFINER, you can use the CURRENT_USER function to return the DEFINER value of the stored routine.

A server processes the data types of routine parameters, local variables created by using the DECLARE statement, and return values of functions as follows:

• Check for data type mismatch and overflow. Conversion and overflow will cause warnings or errors in strict SQL mode.

• Specify only scalar values. For example, the SET val=(SELECT 1, 2) statement is invalid.

• Use the specified character set and its default collation for the character data types if the declaration contains CHARACTER SET. If the COLLATE attribute is used, use the specified collation instead of the default collation.

  If CHARACTER SET and COLLATE are not used, use the character set and collation of the database specified when the routine was created. To prevent the server from using the character set and collation of the database, explicitly specify the CHARACTER SET and COLLATE attributes.

  To change the default database character set or collation, delete the stored routine and create one again to apply the new default values of the database.

  The character_set_database and collation_database system variables specify the character set and collation for the database.

For examples of creating stored functions, see Stored functions.