Inlined SQL functions and compiled SQL functions

There are two implementation types for SQL functions: inlined SQL functions and compiled SQL functions.

Inlined SQL functions typically produce the best performance. However, they do not support some SQL PL language features and data types that compiled SQL functions support. These features include:

the CASE statement
the REPEAT statement
Cursor processing
Dynamic SQL
Condition handlers

SQL functions that are declared in SQL PL modules are always compiled.

PL/SQL functions are always compiled. While inlined functions can be referenced in PL/SQL code, they cannot be declared using PL/SQL. They must be declared using SQL PL.

For all other cases, the syntax of the CREATE FUNCTION statement determines whether an SQL function is inlined or compiled.

An SQL PL function with an atomic body is an inlined function. An SQL function is atomic if it contains a single RETURN statement, or consists of a compound statement that begins with the keywords BEGIN ATOMIC.
An SQL PL function that is non-atomic is a compiled function. An SQL PL function is non-atomic if it contains a compound statement that begins with the keywords BEGIN or BEGIN NOT ATOMIC.

Examples

Inlined functions

create function F1 (N integer)
returns integer
deterministic
no external action
contains sql
return N * 10
@

create function F2 (N integer)
returns integer
deterministic
no external action
contains sql
begin atomic
  if N < 5 then
    return N * 10
  else
    return N * 20
  end if;
end
@

Compiled functions

create function F3 (N integer)
returns integer
deterministic
no external action
contains sql
begin
  if N < 5 then
    return N * 10
  else
    return N * 20
  end if;
end
@

create function F4 (N integer)
returns integer
deterministic
no external action
contains sql
begin not atomic
  if N < 5 then
    return N * 10
  else
    return N * 20
  end if;
end 
@