A function is a standalone SPL program invoked as an expression. When evaluated, it returns a value that is substituted into the expression where it is embedded. Functions can accept input parameters from the calling program and, optionally, return additional values through output parameters—though using output parameters in functions is not recommended in practice.
CREATE FUNCTION defines and names a standalone function that is stored in the database.
If a schema name is included, the function is created in the specified schema.
If no schema name is provided, the function is created in the current schema.
The function name must not match any existing function with the same input argument types in the same schema. Functions with different input argument types can share a name—this is called overloading.
Overloading of functions is a feature of . Overloading of stored, standalone functions is not compatible with Oracle databases.
Update the definition of an existing function
To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. This form has the following constraints:
You cannot change the name or argument types of the function. Attempting to do so creates a new, distinct function instead.
You cannot change the return type. To change the return type, drop and recreate the function.
When using
OUTparameters, you cannot change the types of anyOUTparameters except by dropping the function.
Syntax
CREATE [ OR REPLACE ] FUNCTION name [ (parameters) ]
RETURN data_type
[
IMMUTABLE
| STABLE
| VOLATILE
| DETERMINISTIC
| [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT
| RETURNS NULL ON NULL INPUT
| STRICT
| [ EXTERNAL ] SECURITY INVOKER
| [ EXTERNAL ] SECURITY DEFINER
| AUTHID DEFINER
| AUTHID CURRENT_USER
| PARALLEL { UNSAFE | RESTRICTED | SAFE }
| COST execution_cost
| ROWS result_rows
| SET configuration_parameter
{ TO value | = value | FROM CURRENT }
...]
{ IS | AS }
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declarations ]
BEGIN
statements
END [ name ];Arguments
`name`
The identifier of the function.
`parameters`
A list of formal parameters.
`data_type`
The data type of the value returned by the RETURN statement.
`declarations`
Variable, cursor, type, or subprogram declarations. Subprogram declarations must appear after all other variable, cursor, and type declarations.
`statements`
SPL program statements. The BEGIN–END block can contain an EXCEPTION section.
`IMMUTABLE` | `STABLE` | `VOLATILE`
These attributes inform the query optimizer about the behavior of the function. Specify only one. VOLATILE is the default.
`IMMUTABLE` — The function cannot modify the database and always returns the same result for the same argument values. It performs no database lookups and relies solely on its argument list. Calls with all-constant arguments can be replaced immediately with the function value.
`STABLE` — The function cannot modify the database and returns the same result for the same argument values within a single table scan. The result can change across SQL statements. Use this for functions that depend on database lookups or session variables such as the current time zone.
`VOLATILE` — The function value can change even within a single table scan, so no optimizations are applied. Any function with side effects must be classified as
VOLATILE, even if its result is predictable.
`DETERMINISTIC`
A synonym for IMMUTABLE. The function cannot modify the database and always returns the same result for the same argument values, with no database lookups outside its argument list. Calls with all-constant arguments can be replaced immediately with the function value.
`[NOT] LEAKPROOF`
LEAKPROOF has no side effects and reveals no information about the values used to call the function.
`CALLED ON NULL INPUT` | `RETURNS NULL ON NULL INPUT` | `STRICT`
These options control how the function handles NULL arguments.
`CALLED ON NULL INPUT` (default) — The function is called normally even when some arguments are
NULL. Check forNULLvalues explicitly inside the function body if needed.`RETURNS NULL ON NULL INPUT` / `STRICT` — The function returns
NULLautomatically if any argument isNULL. The function body is not executed.
`[EXTERNAL] SECURITY DEFINER`
The function executes with the privileges of the user who created it. This is the default. The EXTERNAL keyword is accepted for SQL standard conformance but has no effect.
`[EXTERNAL] SECURITY INVOKER`
The function executes with the privileges of the user who calls it. The EXTERNAL keyword is accepted for SQL standard conformance but has no effect.
`AUTHID DEFINER`
A synonym for [EXTERNAL] SECURITY DEFINER. When the AUTHID clause is omitted or set to AUTHID DEFINER, access privileges are determined by the rights of the function owner.
`AUTHID CURRENT_USER`
A synonym for [EXTERNAL] SECURITY INVOKER. Access privileges are determined by the rights of the current user executing the function.
`PARALLEL { UNSAFE | RESTRICTED | SAFE }`
Controls whether the function can be used in parallel sequential scans.
`UNSAFE` (default) — The function cannot execute in parallel mode. Its presence forces a serial execution plan.
`RESTRICTED` — The function can execute in parallel mode, but only on the parallel group leader. If the qualification for any particular relation has anything that is parallel restricted, that relation will not be chosen for parallelism.
`SAFE` — The function can execute in parallel mode with no restrictions.
`COST execution_cost`
A positive number giving the estimated execution cost in units of cpu_operator_cost. For set-returning functions, this is the cost per returned row. Higher values make the planner avoid evaluating the function more than necessary.
`ROWS result_rows`
A positive number giving the estimated number of rows the function returns. Applies only to set-returning functions. The default is 1,000 rows.
`SET configuration_parameter { TO value | = value | FROM CURRENT }`
Sets a configuration parameter to the specified value when the function is entered, then restores it to its prior value when the function exits.
SET FROM CURRENTsaves the current parameter value of the session as the value to be applied when the function is entered.A
SET LOCALstatement inside the function for the same variable is restricted to the function; the prior value is restored on exit.An ordinary
SETstatement (withoutLOCAL) inside the function overrides theSETclause, and its effect persists after the function exits unless the current transaction is rolled back.
`PRAGMA AUTONOMOUS_TRANSACTION`
Sets the function to run as an autonomous transaction.
Examples
Simple function with no parameters
CREATE OR REPLACE FUNCTION simple_function
RETURN VARCHAR2
IS
BEGIN
RETURN 'That''s All Folks!';
END simple_function;Function with input parameters
The emp_comp function accepts salary and commission as inputs and returns total annual compensation. The NVL call substitutes 0 for a NULL commission.
CREATE OR REPLACE FUNCTION emp_comp (
p_sal NUMBER,
p_comm NUMBER
) RETURN NUMBER
IS
BEGIN
RETURN (p_sal + NVL(p_comm, 0)) * 24;
END emp_comp;Function using STRICT and AUTHID CURRENT_USER
This example combines STRICT null-input handling with AUTHID CURRENT_USER privilege control.
CREATE OR REPLACE FUNCTION dept_salaries(
dept_id int
) RETURN NUMBER
STRICT
AUTHID CURRENT_USER
AS
BEGIN
RETURN (SELECT sum(sal) FROM emp WHERE deptno = dept_id);
END;STRICT instructs the server to return NULL without executing the function if any input argument is NULL.
AUTHID CURRENT_USER means dept_salaries runs with the privileges of the calling role. If that role lacks SELECT permission on the emp table, the function raises an error. To run with the defining role's privileges instead, replace AUTHID CURRENT_USER with AUTHID DEFINER.
Compatibility
The following keywords are available in PolarDB for PostgreSQL (Compatible with Oracle) as extensions and are not supported by Oracle:
STRICTLEAKPROOFPARALLELCOSTROWSSET