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.
FUNCTION name [ (parameters) ]{ IS | AS }
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ declarations ]
BEGIN
statements
END [ name ];
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