The FUNCTION clause specified in the declaration topic defines and names a subfunction local to that block.

The term block refers to the SPL block structure consisting of an optional declaration topic, a mandatory executable section, and an optional exception section. Blocks are the structures for standalone procedures and functions, anonymous blocks, subprograms, triggers, packages, and object type methods.

The phrase the identifier is local to the block means that the identifier (that is, a variable, cursor, type, or subprogram) is declared within the declaration section of that block and is therefore accessible by the SPL code within the executable section and optional exception section of that block.

Subprocedures can only be declared after all other variable, cursor, and type declarations included in the declaration section. ( That is, subprograms must be the last set of declarations.)

PROCEDURE name [ (parameters) ]{ IS | AS }
    [ PRAGMA AUTONOMOUS_TRANSACTION; ]
    [ declarations ]
  BEGIN
    statements
  END [ name ];
Table 1. Arguments
Argument Description
name name is the identifier of the subprocedure.
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.
PRAGMA AUTONOMOUS_TRANSACTION PRAGMA AUTONOMOUS_TRANSACTION is the directive that sets the subfunction as an autonomous transaction.
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 may contain an EXCEPTION section).

Examples

The following example shows the use of a recursive subfunction:

DECLARE
    FUNCTION factorial (
        n           BINARY_INTEGER
    ) RETURN BINARY_INTEGER
    IS
    BEGIN
        IF n = 1 THEN
            RETURN n;
        ELSE
            RETURN n * factorial(n-1);
        END IF;
    END factorial;
BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i || '! = ' || factorial(i));
    END LOOP;
END;

The output from the example is as follows:

1! = 1
2! = 2
3! = 6
4! = 24
5! = 120