This topic describes how to create a subfunction.

The FUNCTION clause specified in the function declaration specifies and names a subfunction that is local to that block.

The term block refers to the SPL block structure that consists of an optional declaration section, a mandatory executable section, and an optional exception section. Blocks are the structures for standalone stored 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 (a variable, cursor, type, or subprogram) is declared in the declaration section of that block. Therefore, the identifier is accessible by the SPL code in the executable section and the optional exception section of that block.

Subfunctions can be declared after all the other variable, cursor, and type declarations that are included in only the declaration section. This indicates that subprograms must be the last set of declarations. 

PROCEDURE name [ (parameters) ]{ IS | AS }
    [ PRAGMA AUTONOMOUS_TRANSACTION; ]
    [ declarations ]
  BEGIN
    statements
  END [ name ];
Table 1. Parameters
Parameter Description
name The identifier of the subfunction.
parameters A list of formal parameters.
data_type The data type of the value that is returned by the RETURN statement of the function.
PRAGMA AUTONOMOUS_TRANSACTION The instruction that specifies the subfunction as an autonomous transaction.
declarations The variable, cursor, type, or subprogram declarations. If subprogram declarations are included, they must be declared after all the other variable, cursor, and type declarations.
statements The SPL program statements. The BEGIN - END block can contain an EXCEPTION section.

Examples

The following example shows how to use 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 following example shows the output of the subfunction:

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