CREATE FUNCTION

Purpose

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

Privilege requirements

You must have the CREATE FUNCTION privilege to use the CREATE FUNCTION statement.

By default, after you create a function, 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.

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

Parameters

By default, a stored function is associated with the default database. To associate a stored function with a specified database, specify the name by using database_name.sp_name.

You can call a stored function by referencing it in an expression. The function returns a value during the evaluation of the expression.

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 name of a stored routine is the same as that of an internal SQL function, a syntax error will be returned unless you use a space between the name and the subsequent parentheses when you define or call the routine. Therefore, avoid using the names of existing SQL functions for your own stored routines.

The func_parameter parameter list in parentheses must always exist. If no parameters are specified, use empty parentheses () to indicate an empty parameter list. Parameter names are case-insensitive. For a stored function, each parameter is an IN parameter, and you do not need to explicitly specify the IN keyword.

The RETURNS clause specifies the return type of a stored function. The function body must contain a RETURN value statement. If the RETURN statement returns a value of a different type, the value is forcibly 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 from the function is the string corresponding to the ENUM member in the SET member set.

You can declare the parameter types and the return type of a function as any valid data type. If you specify CHARACTER SET, you can also use the COLLATE attribute.

The routine_body consists of valid SQL statements. An SQL statement can be a simple statement such as SELECT or INSERT, or a compound statement created by using BEGIN and END. A compound statement can contain declarations, loops, and other control structure statements. In practice, stored functions tend to use compound statements unless the body consists of only a single RETURN statement.

You cannot use the USE statement in stored routines. When you call a routine, the system implicitly executes USE database_name (and then revokes the privilege when the routine terminates) to specify the default database for the routine. If you want to reference other database objects in addition to the default database of the routine, you can use the database name to qualify the reference.

If you define a routine in strict SQL mode, but call it in non-strict mode, the parameters of the routine are not assigned values in strict mode. If you assign an expression to a routine in strict SQL mode, you must call the routine in strict mode.

You can use the COMMENT clause to describe a stored routine. You can use the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements to display the comments.

The LANGUAGE clause specifies the language in which the routine is written. This clause applies only to SQL routines. The server ignores this clause.

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 the DETERMINISTIC nor the NOT DETERMINISTIC clause is specified, the default is NOT DETERMINISTIC. To declare a function as deterministic, you must explicitly specify the DETERMINISTIC clause.

The following table describes the characteristics of data access for routines.

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

• NO SQL: the routine does not contain SQL statements.

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

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

The SQL SECURITY clause can be DEFINER or INVOKER. If you specify DEFINER, the routine is executed with the privileges of the specified account (which must have access privileges to the database associated with the routine). If you specify INVOKER, the routine is executed with the privileges of the caller. The default value is DEFINER. The user who calls the routine must have the EXECUTE privilege on the routine.

The DEFINER clause specifies the account to use when checking the access privileges of the routine during execution. If you specify this clause, the user value must be an account specified as 'user_name'@'host_name' (such as 'admin'@'oblocalhost') or an account obtained by using the CURRENT_USER() function. If you omit this clause, the default definer is the user who executes the CREATE FUNCTION statement. In the body of a stored routine defined by using the SQL SECURITY DEFINER clause, you can use the CURRENT_USER function to return the DEFINER value of the routine.

The server handles the data types of routine parameters, local routine variables created by using the DECLARE statement, and function return values as follows:

• Checks for data type mismatches and overflows. Data type mismatches and overflows will generate warnings or errors in strict SQL mode.

• Scalar values only. 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 when the routine was created are used. To prevent the server from using the database character set and collation, explicitly specify the CHARACTER SET and COLLATE attributes for the parameters.

  If you want to change the default character set or collation of the database, you must drop and recreate the stored routine to apply the new database default values.

  You can use the character_set_database and collation_database system variables to specify the database character set and collation.

Examples

Example 1: Create a simple calculation function.

obclient> DELIMITER //

obclient> CREATE FUNCTION calculate_total_price(unit_price DECIMAL(10,2), quantity INT)
    RETURNS DECIMAL(10,2)
    DETERMINISTIC
    BEGIN
        RETURN unit_price * quantity;
    END //

obclient> DELIMITER ;

The query result is as follows:

obclient> SELECT calculate_total_price(99.99, 3);

+-------------------------------+
| calculate_total_price(99.99, 3) |
+-------------------------------+
|                        299.97 |
+-------------------------------+
1 row in set

Example 2: Create a function with conditional judgment.

obclient> DELIMITER //

obclient> CREATE FUNCTION get_discount_level(order_amount DECIMAL(10,2))
    RETURNS VARCHAR(20)
    DETERMINISTIC
    BEGIN
        IF order_amount >= 1000 THEN
            RETURN 'VIP';
        ELSEIF order_amount >= 500 THEN
            RETURN 'Gold';
        ELSE
            RETURN 'Standard';
        END IF;
    END //

obclient> DELIMITER ;

The query result is as follows:

obclient> SELECT get_discount_level(1500);

+------------------------+
| get_discount_level(1500) |
+------------------------+
| VIP                    |
+------------------------+
1 row in set

```sql
obclient> SELECT get_discount_level(600);

+------------------------+
| get_discount_level(600) |
+------------------------+
| Gold                   |
+------------------------+
1 row in set

Example 3: Create a function using the DEFINER clause.

obclient> DELIMITER //

obclient> CREATE DEFINER='admin'@'localhost' FUNCTION calculate_bonus(base_salary DECIMAL(10,2), performance_rate DECIMAL(3,2))
    RETURNS DECIMAL(10,2)
    DETERMINISTIC
    COMMENT 'Calculate employee bonus based on base salary and performance'
    BEGIN
        RETURN base_salary * performance_rate;
    END //

obclient> DELIMITER ;

Example 4: Create a function without parameters.

obclient> DELIMITER //

obclient> CREATE FUNCTION get_current_year()
    RETURNS INT
    DETERMINISTIC
    BEGIN
        RETURN YEAR(CURDATE());
    END //

obclient> DELIMITER ;

The query result is as follows:

obclient> SELECT get_current_year();

+-------------------+
| get_current_year() |
+-------------------+
|              2025 |
+-------------------+
1 row in set

Example 5: Create a function that uses a database table.

obclient> CREATE TABLE employee_info(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary DECIMAL(10,2)
);

obclient> INSERT INTO employee_info VALUES 
    (1, 'Alice', 8000.00),
    (2, 'Bob', 12000.00),
    (3, 'Charlie', 9500.00);

obclient> DELIMITER //

obclient> CREATE FUNCTION get_employee_count()
    RETURNS INT
    READS SQL DATA
    BEGIN
        DECLARE emp_count INT;
        SELECT COUNT(*) INTO emp_count FROM employee_info;
        RETURN emp_count;
    END //

obclient> DELIMITER ;

The query result is as follows:

obclient> SELECT get_employee_count();

+----------------------+
| get_employee_count() |
+----------------------+
|                    3 |
+----------------------+
1 row in set

References

• Stored functions
• ALTER FUNCTION
• DROP FUNCTION
• SHOW CREATE FUNCTION