CREATE FUNCTION

Purpose

The CREATE FUNCTION statement is used to create a stored function.

Privilege requirements

To use the CREATE FUNCTION statement, you must have the CREATE FUNCTION privilege.

Basic example:

After granting the CREATE ROUTINE privilege to a user, the user can create a FUNCTION.

-- Create a user and grant privileges
obclient> CREATE USER 'mingye'@'localhost' IDENTIFIED BY 'password';
obclient> GRANT CREATE ROUTINE ON mydb.* TO 'mingye'@'localhost';
obclient> GRANT EXECUTE ON mydb.* TO 'mingye'@'localhost';

-- Switch to the target database
obclient> USE mydb;

-- Create a function
obclient> DELIMITER //
obclient> CREATE FUNCTION add_numbers(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    BEGIN
        RETURN a + b;
    END //
obclient> DELIMITER ;

-- Test the function
obclient> SELECT add_numbers(5, 3);

By default, after a PROCEDURE is created, OceanBase Database automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine (stored procedure or function) creator. If the DEFINER clause is specified, the required privileges depend on the value of user.

Example with the DEFINER clause:

-- Create an admin user
obclient> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_password';
obclient> GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

-- Create a regular user
obclient> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user_password';
obclient> GRANT SELECT, EXECUTE ON mydb.* TO 'user1'@'localhost';

-- Create a function using the DEFINER clause
obclient> DELIMITER //
obclient> CREATE DEFINER='admin'@'localhost' FUNCTION multiply_numbers(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    BEGIN
        RETURN a * b;
    END //
obclient> DELIMITER ;

-- Test the function as a regular user
obclient> SELECT multiply_numbers(4, 6);

Syntax

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 name.

To call a stored function, you can reference it in an expression. The function returns a value during expression evaluation.

The DEFINER and SQL SECURITY clauses specify the security context to use when checking access privileges during routine execution.

The IF NOT EXISTS clause prevents the creation of a routine with the same name as an existing routine. If the routine name is the same as that of a built-in SQL function, a syntax error occurs unless you use a space between the routine name and the subsequent parentheses when defining or calling the routine. Therefore, avoid using the names of existing SQL functions when creating your own stored routines.

The proc_parameter parameter list in parentheses must always be present. If no parameters are specified, use empty parentheses () to indicate an empty parameter list. Parameter names are not case-sensitive. All parameters for stored functions are IN parameters.

The RETURNS clause is used only for stored functions to specify the return type. The function body must include a RETURN value statement. If the RETURN statement returns a value of a different type, it is implicitly converted to the appropriate type. For example, if the RETURNS clause specifies an ENUM or SET value, but the RETURN statement returns an integer, the value returned by the function is the string representation of the corresponding ENUM member in the SET collection.

Parameter types and function return types can be declared as any valid data type. If preceded by CHARACTER SET, the COLLATE attribute can be used.

The routine_body consists of valid SQL statements. SQL statements can be simple statements such as SELECT or INSERT, or composite statements written using BEGIN and END. Composite statements can include declarations, loops, and other control structure statements. In practice, stored functions tend to use composite statements unless the 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 (and revoked upon routine termination) to specify the default database for routine execution. If you need to reference objects from a database other than the default database for the routine, you can use the database name to qualify the reference.

If a routine is defined in strict SQL mode but called in non-strict mode, the parameters of the routine are not assigned values in strict mode. If expressions are assigned to a routine in strict SQL mode, the routine must be called in strict mode.

The COMMENT characteristic can be used to describe stored routines. Comment information can be displayed using the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.

The LANGUAGE characteristic indicates the language in which the routine is written. This characteristic is only applicable to SQL routines and is ignored by the server.

A routine is considered "deterministic" if it always produces the same result for the same input parameters. Otherwise, it is considered "non-deterministic". If neither DETERMINISTIC nor NOT DETERMINISTIC is specified in the routine definition, the default is NOT DETERMINISTIC. To declare a function as deterministic, you must explicitly specify DETERMINISTIC.

The following characteristics describe how routines use data:

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

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

• READS SQL DATA indicates that the routine contains statements that read data (such as SELECT) but do not write data.

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

The SQL SECURITY characteristic can be DEFINER or INVOKER, specifying whether the routine is executed using the privileges of the specified account (which must have access privileges to the database associated with the routine) or the privileges of the caller. The default value is DEFINER. The user calling the routine must have the EXECUTE privilege on the routine.

The DEFINER clause specifies the account used to check access privileges during routine execution. If the DEFINER clause is present, the user value should be an account specified as 'user_name'@'host_name' (e.g., 'admin'@'oblocalhost') or obtained using the CURRENT_USER() function. If the DEFINER clause is omitted, the default definer is the user who executed the CREATE FUNCTION statement. In the body of a stored routine defined using the SQL SECURITY DEFINER characteristic, the CURRENT_USER function can be used to return the DEFINER value of the routine.

The server handles data types for routine parameters, local routine variables declared using DECLARE, and function return values as follows:

• Checks for data type mismatches and overflows. Conversions and overflow issues can result in warnings or errors in strict SQL mode.

• Scalar values can only be specified. For example, the SET val=(SELECT 1, 2) statement is invalid.

• For character data types, if the declaration includes CHARACTER SET, the specified character set and its default collation are used. If the COLLATE attribute is used, the specified collation is used instead of the default collation.

  If neither CHARACTER SET nor COLLATE is specified, the database character set and collation applied at the time of routine creation are used. To prevent the server from using the database character set and collation, explicitly specify the CHARACTER SET and COLLATE characteristics for the parameter.

  If the database default character set or collation is changed, the stored routine must be dropped and recreated to apply the new database defaults.

  The character_set_database and collation_database system variables can be used to specify the database character set and collation.

For detailed examples of creating functions, see Stored functions.