Functions are standalone SPL programs that are invoked as expressions. When evaluated, a function returns a value that is substituted in the expression in which the function is embedded. Functions can optionally take values from the calling program in the form of input parameters. In addition to returning a value by itself, a function can optionally return additional values to the caller in the form of output parameters. However, we recommend that you do not use output parameters in functions in programming practice.

The CREATE FUNCTION statement defines and names a standalone function that will be stored in the database.

If a schema name is included, the function is created in the specified schema. Otherwise, it is created in the current schema. The name of the new function must not match any existing function with the same input argument types in the same schema. However, functions of different input argument types can share a name. This is called overloading. Overloading of functions is a feature of PolarDB databases compatible with Oracle - overloading of stored, standalone functions is not compatible with Oracle databases.

To update the definition of an existing function, use CREATE OR REPLACE FUNCTION. You cannot change the name or argument types of a function in this way. If you tried, you actually created a new, distinct function. Also, CREATE OR REPLACE FUNCTION does not change the return type of an existing function. To do that, you must drop and recreate the function. Also when using OUT parameters, you cannot change the types of any OUT parameters except by dropping the function.

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 ];
Argument Description
name name is the identifier of the function.
parameters parameters is a list of formal parameters.
data_type data_type is the data type of the value returned by the RETURN statement of the function.
declarations declarations are variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all other variable, cursor, and type declarations.
statements statements are 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. You can specify only one option. VOLATILE is the default behavior.
  • IMMUTABLE specifies that the function cannot modify the database and always reaches the same result when given the same argument values. It does not do database lookups or use information that is not directly present in its argument list in any other way. If you include this clause, any call of the function with all-constant arguments can be immediately replaced with the function value.
  • STABLE specifies that the function cannot modify the database, and that within a single table scan, it will consistently return the same result for the same argument values. However, its result could change across SQL statements. This is the suitable option for functions that depend on database lookups and parameter variables such as the current time zone.
  • VOLATILE specifies that the function value can change even in a single table scan, so no optimizations can be made. Note that any function that has side effects must be classified as a volatile function, even if its result is predictable, to prevent calls from being optimized away.
DETERMINISTIC DETERMINISTIC is a synonym for IMMUTABLE. A DETERMINISTIC function cannot modify the database and always reaches the same result when given the same argument values. It does not do database lookups or use information that is not directly present in its argument list in any other way. If you include this clause, any call of the function with all-constant arguments can be immediately replaced 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

  • CALLED ON NULL INPUT specifies that the procedure is called normally when some of its arguments are NULL. CALLED ON NULL INPUT is the default value. If necessary, the author needs to check for NULL values and respond appropriately.
  • RETURNS NULL ON NULL INPUT or STRICT specifies that the procedure returns NULL if any of its arguments is NULL. If these clauses are specified, the procedure is not executed when there are NULL arguments. A NULL result is assumed automatically.
[ EXTERNAL ] SECURITY DEFINER SECURITY DEFINER specifies that the function will execute with the privileges of the user that created it. SECURITY DEFINER is the default value. The keyword EXTERNAL is allowed for SQL conformance. This is optional.
[ EXTERNAL ] SECURITY INVOKER SECURITY INVOKER specifies that the function will execute with the privileges of the user that calls it. The keyword EXTERNAL is allowed for SQL conformance. This is optional.
AUTHID DEFINER

AUTHID CURRENT_USER

AUTHID DEFINER is a synonym for [EXTERNAL] SECURITY DEFINER. If the AUTHID clause is omitted or if AUTHID DEFINER is specified, the rights of the function owner are used to determine access privileges to database objects.

AUTHID CURRENT_USER is a synonym for [EXTERNAL] SECURITY INVOKER. If AUTHID CURRENT_USER is specified, the rights of the current user who is executing the function are used to determine access privileges.

PARALLEL { UNSAFE | RESTRICTED | SAFE } PARALLEL enables the use of parallel sequential scans (parallel mode). In contrast to a serial sequential scan, a parallel sequential scan uses multiple workers to scan a relation in parallel during a query.
  • When set to UNSAFE, the function cannot be executed in a parallel mode. The presence of such a function in a SQL statement forces a serial execution plan. If the PARALLEL clause is omitted, this is the default setting.
  • When set to RESTRICTED, the function can be executed in a parallel mode, but the execution is restricted to 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.
  • When set to SAFE, the function can be executed in a parallel mode with no restriction.
COST execution_cost execution_cost is a positive number giving the estimated execution cost for the function. Unit: cpu_operator_cost. If the function returns a set, this is the cost per returned row. Larger values cause the planner to try to avoid evaluating the function more often than necessary.
ROWS result_rows result_rows is a positive number giving the estimated number of rows that the planner expects the function to return. This is allowed only when the function is declared to return a set. The default assumption is 1,000 rows.
SET configuration_parameter { TO value | = value | FROMCURRENT } SET causes the specified configuration parameter to be set to the specified value when the function is entered, and then restored to its prior value when the function exits. SET FROM CURRENT saves the current parameter value of the session as the value to be applied when the function is entered.

If a SET clause is attached to a function, the effects of a SET LOCAL statement executed inside the function for the same variable are restricted to the function. The prior configuration parameter value is restored when the function exits. An ordinary SET statement (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL statement, with the effects of such a statement persisting after procedure exit, unless the current transaction is rolled back.

PRAGMA AUTONOMOUS_TRANSACTION PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the function to an autonomous transaction.
Note The STRICT, LEAKPROOF, PARALLEL, COST, ROWS, and SET keywords provide extended functionality for PolarDB databases compatible with Oracle but are not supported by Oracle.

Examples

The following example describes a simple function that takes no parameters:

CREATE OR REPLACE FUNCTION simple_function
    RETURN VARCHAR2
IS
BEGIN
    RETURN 'That''s All Folks!' ;
END simple_function;

The following example describes a function that takes two input parameters. Parameters are discussed in subsequent topics.

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;

The following example describes how to use the AUTHID CURRENT_USER clause and STRICT keyword in a function declaration.

CREATE OR REPLACE FUNCTION dept_salaries(dept_id int) RETURN NUMBER
  STRICT
  AUTHID CURRENT_USER
BEGIN
  RETURN QUERY (SELECT sum(salary) FROM emp WHERE deptno = id);
END;

Include the STRICT keyword to instruct the server to return NULL if any input parameter passed is NULL. If the NULL value is passed, the function will not execute.

The dept_salaries function executes with the privileges of the role that is calling the function. If the current user has insufficient privileges to perform the SELECT statement to query the emp table and display employee salaries, the function will report an error. To instruct the server to use the privileges associated with the role that defined the function, replace the AUTHID CURRENT_USER clause with the AUTHID DEFINER clause.