Purpose
The CREATE FUNCTION statement is used to create or replace standalone functions or call specifications. A standalone function is a stored function (a subprogram that returns a single value) in the database.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition only provides MySQL-compatible mode.
Notice
A standalone function created by using the CREATE FUNCTION statement is different from a function declared and defined in a PL block or package.
Prerequisites
To create or replace a standalone function in your schema, you must have the CREATE FUNCTION system privilege. To create or replace a standalone function in another user's schema, you must have the CREATE ANY FUNCTION system privilege.
Syntax
The syntax of create_function is as follows:
CREATE [ OR REPLACE ]
FUNCTION plsql_function_source
The syntax of plsql_function_source is as follows:
[ schema. ] function_name
[ ( parameter_declaration [, parameter_declaration]... ) ]
RETURN datatype [ sharing_clause ]
[ invoker_rights_clause
| accessible_by_clause
| DETERMINISTIC
| parallel_enable_clause
| result_cache_clause
| default_collation_clause
| pipelined_clause
]...
{ { AGGREGATE | PIPELINED } USING [ schema. ] implementation_type
| [ PIPELINED ] { IS | AS } { [ declare_section ] body
| call_spec
}
} ;
Semantics
| Syntax | Keyword or syntax node | Description |
|---|---|---|
| create_function | OR REPLACE | Recreates the function (if it exists) and recompiles it. Users who were granted privileges on the function before the recreation can still access the function without needing to obtain the privileges again. If there are any function-based indexes dependent on this function, the database marks these indexes as DISABLED. |
| plsql_function_source | schema | The name of the schema where the function is located. The default value is the current user's schema. |
| plsql_function_source | function_name | The name of the function to be created. |
| plsql_function_source | RETURN datatype | Specifies the data type of the function's return value. The return value can be any data type supported by PL. |
| plsql_function_source | body | The required executable part of the function, and the optional exception handling part. |
| plsql_function_source | declare_section | The optional declaration section of the function. Declarations are local to the function and can be referenced in the body, but they no longer exist after the function has completed execution. |
| plsql_function_source | DETERMINISTIC | Indicates that the function is deterministic, meaning it always returns the same result for the same input parameters. This is important for optimizing query performance. |
| plsql_function_source | parallel_enable_clause | Indicates that the function can be safely executed in parallel queries. When this feature is specified, the function must meet conditions such as being deterministic, not modifying the database state, and not depending on session state. |
Examples
Create a function named get_salary_by_dept.
CREATE OR REPLACE FUNCTION get_salary_by_dept(
v_in_dept_id NUMBER,
v_out_emp_count OUT NUMBER)
RETURN NUMBER
IS
v_sum NUMBER;
BEGIN
SELECT SUM(salary), count(*) INTO v_sum, v_out_emp_count
FROM employees WHERE deptno=v_in_dept_id;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Dept id '||v_in_dept_id||' not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR: '||SQLCODE||': '||SQLERRM);
END get_salary_by_dept;
/
Create a function that can be safely executed in parallel queries.
CREATE OR REPLACE FUNCTION sub_query_result(input VARCHAR)
DETERMINISTIC
PARALLEL_ENABLE
RETURN VARCHAR
IS
result tp_sys_para.para_value%type;
BEGIN
SELECT tp.para_value INTO result
FROM tp_sys_para tp
WHERE tp.para_name_e = input;
RETURN result;
END;
/