You can use the CREATE FUNCTION statement to create or replace an independent function or call specification. An independent function is a function that is stored in the database, also known as a subprogram that returns a single value.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Notice
An independent function created by using the CREATE FUNCTION statement is different from those declared or defined in PL blocks or packages.
Prerequisites
To create or replace an independent function in your own schema, you must have the CREATE FUNCTION system privilege. To create or replace an independent function in the schema of other users, 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 | Re-creates this function (if any) and recompiles it. Before the function is redefined, users granted the access privilege can still access this function without the need to obtain the access privilege again. If any function-based index depends on this function, the database marks this index as DISABLED. |
| plsql_function_source | schema | The name of the schema where the function is located. The default value is your schema. |
| plsql_function_source | function_name | The name of the function to be created. |
| plsql_function_source | RETURN datatype | The data type of the value returned by the function. The return value can be of any data type supported by PL. |
| plsql_function_source | body | The executable part and exception-handling part of the function. The executable part is required and the exception-handling part is optional. |
| plsql_function_source | declare_section | The declarative part of the function. This part is optional. A declaration is local to the function, can be referenced in body, and no longer exists after the function is executed. |
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;
/