Purpose
The CREATE FUNCTION statement is used to create or replace standalone functions or call specifications. A standalone function is a stored function (subprogram that returns a single value) in the database.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
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
You must have the CREATE FUNCTION system privilege to create or replace a standalone function in the current schema. You must have the CREATE ANY FUNCTION system privilege to create or replace a standalone function in another user's schema.
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 the function privilege before the function is redefined can still access the function without re-granting the privilege. If any function-based indexes depend on the function, the database marks the indexes as DISABLED. |
| plsql_function_source | schema | The name of the schema in which 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 | 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 executable part required for the function, and the exception handling part (optional). |
| plsql_function_source | declare_section | The optional declaration part of the function. Declarations are local to the function and can be referenced in the body, but they no longer exist after the function completes 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;
/