Purpose
The CREATE FUNCTION statement is used to create a stored function.
Privilege requirements
The CREATE FUNCTION statement requires the CREATE FUNCTION privilege.
By default, after a FUNCTION is created, OceanBase AI Database automatically grants the ALTER ROUTINE and EXECUTE privileges to the creator of the routine (stored procedure or function). If a DEFINER clause is present, 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
Parameter |
Description |
|---|---|
| DEFINER | Specifies the function's definer (creator). If omitted, the default is the executor. CREATE FUNCTION The user who executed the statement. You can specify it as 'user_name'@'host_name' or CURRENT_USER. |
| IF NOT EXISTS | If the function already exists, no error is reported. |
| sp_name | The name of the stored function to be created. You can use database_name.sp_name Specify the database. |
| func_parameter | List of function parameters. The parameters of a stored function can only be IN Type, which does not need to be explicitly specified. IN The keyword. The parameter format is param_name type. |
| RETURNS type | Specifies the return type of the function. The function body must contain at least one RETURN value statement. |
| characteristic | Function features, which can include: - COMMENT 'string': Function comments- LANGUAGE SQL: The specified language. This parameter is required only for SQL statements and can be omitted.- [NOT] DETERMINISTIC: Specifies whether the function is deterministic.- CONTAINS SQL \| NO SQL \| READS SQL DATA \| MODIFIES SQL DATA: specifies the data access characteristics of the function.- SQL SECURITY { DEFINER \| INVOKER }: Specifies the execution privilege mode. |
| routine_body | The function body contains the specific implementation logic of the function. It must contain at least one RETURN value statement. |
By default, a stored function is associated with the current database. To associate a stored function with a specified database, use database_name.sp_name to specify the name.
To call a stored function, reference it in an expression. The function returns a value during expression evaluation.
The DEFINER and SQL SECURITY clauses specify the security context used to check access privileges when the routine is executed.
IF NOT EXISTS prevents creating a routine with an existing name. If a stored name is the same as a built-in SQL function name, a syntax error occurs, unless a space is used between the name and the following parentheses when defining or invoking the routine later. Therefore, avoid creating your own stored routines with names that are the same as existing SQL functions.
The func_parameter parameter list within parentheses must always be present. If no parameters are provided, use empty parentheses () to indicate an empty parameter list. Parameter names here are case-insensitive. Each parameter of a stored function is an IN parameter; the IN keyword does not need to be explicitly specified.
The RETURNS clause is used only for stored functions to specify the function's return type. The function body must include a RETURN value statement. If a RETURN statement returns values of different types, those values are coerced to an appropriate type. For example, if a function specifies an ENUM or SET value in the RETURNS clause 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 of SET members.
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. An SQL statement can be a simple statement, such as SELECT or INSERT, or a compound statement written 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 a single RETURN statement.
The USE statement is not allowed in stored routines. When a routine is called, USE database_name is implicitly executed (and undone when the routine terminates), specifying the default database for the routine's execution. If you need to reference database objects outside the routine's default database, you can qualify them with the database name.
If a routine is defined in strict SQL mode but called in non-strict mode, the parameters of the routine will not be assigned values in strict mode. If expressions are allocated to be passed to a routine in strict SQL mode, the routine should be called in strict mode.
The COMMENT clause can be used to describe stored routines. You can use the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements to view the comment information.
The LANGUAGE clause specifies the language used to write the routine. It applies only to SQL routines and is ignored by the server.
If a routine always produces the same result for the same input parameters, it is considered "deterministic"; 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 table describes the characteristics of data used by routines.
CONTAINS SQL: The sample program does not contain statements for reading or writing data. This is the default value. For example, statements such asSET@x=1orDO RELEASE_LOCK('abc')are executed but neither read nor write data.NO SQLindicates that the sample program does not contain SQL statements.The
READS SQL DATAtable example statement contains data reading statements (such asSELECT) but no data writing statements.The
MODIFIES SQL DATAclause example statements contain data-writing statements (such asINSERTorDELETE).
SQL SECURITY can be set to DEFINER or INVOKER to specify whether the routine executes with the privileges of the specified account (which must have access permissions to the database associated with the routine) or with the privileges of the caller. The default value is DEFINER. The user calling the routine must have the EXECUTE privilege on that routine.
The DEFINER clause specifies the account used when checking access privileges for a stored routine during execution. If the DEFINER clause is present, the user value should be an account specified as 'user_name'@'host_name' (for example, 'admin'@'oblocalhost') or the account obtained by using the CURRENT_USER() function. If the DEFINER clause is omitted, the default definer is the user who executes the CREATE FUNCTION statement. In the body of a stored routine defined with the SQL SECURITY DEFINER feature, you can use the CURRENT_USER function to return the DEFINER value of the routine.
The server handles data types for routine parameters, local routine variables created using DECLARE, or function return values as follows:
Check for data type mismatches and overflow. Conversion and overflow issues can cause alerts or errors in strict SQL mode.
Only scalar values can be assigned. For example, the statement
SET val=(SELECT 1, 2)is invalid.For character data types, if the declaration includes
CHARACTER SET, the specified character set and its default collation are used. If theCOLLATEattribute is used, the specified collation is used instead of the default collation.If
CHARACTER SETandCOLLATEare not 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 theCHARACTER SETandCOLLATEattributes for these parameters.To change the database default character set or collation, you must drop and recreate the storage routines to apply the new database defaults.
The
character_set_databaseandcollation_databasesystem variables can be used 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
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 database tables.
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