Subprograms

Where subprograms can live

Declaration order constraint

A subprogram can only access variables that are declared before it in the DECLARE section.

Declare a subprogram

Use the following syntax to declare a subfunction inside a PL/SQL block:

DECLARE -- Outer anonymous block, or a global procedure

  FUNCTION name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { RETURNS | RETURN } rettype
  { AS | IS }
  [ DECLARE ] -- Declaration section of the subfunction
    ...
  BEGIN -- Body of the subfunction
    ...
    RETURN ...
  END;

BEGIN -- Outer block body
  ...
END;

To declare a subprocedure instead of a subfunction, replace FUNCTION with PROCEDURE and omit the RETURN clause — procedures don't return values.

Call a subprogram

The following example declares and calls a subfunction:

DECLARE
  a INT;
  FUNCTION local_func RETURN INT -- Declares the subfunction
  IS
  BEGIN
    RETURN 10;
  END;
BEGIN
  a := local_func(); -- Calls the subfunction
  RAISE NOTICE 'a: %', a;
END;

Invocation resolution and overloading

Functions can be overloaded — two functions can share the same name if their parameter lists differ. When a subprogram is called, the system resolves which function to run using the following logic:

1. Search the current (innermost) scope for a function with the same name.

2. If at least one match is found in that scope, stop searching outer scopes and select the best-matching overload.

3. If no match is found in the current scope, move to the next enclosing scope and repeat.

4. If a match is found but no overload fits the call's arguments, report an error.

For nested subprograms, the system searches scope layers. For global functions, candidates are first retrieved from the system table before the most matching parameter list is selected.

Once the system finds candidates in a scope, it never searches further outward — even if none of the candidates match.

Example: outer procedure found correctly

DECLARE
    PROCEDURE local_proc1 IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure1';
    END;

    PROCEDURE local_proc2 IS
        proc2_str VARCHAR(50);
        FUNCTION local_func RETURN VARCHAR IS
            func_str VARCHAR(50) := 'raise inner local_func';
        BEGIN
            local_proc1(); -- Finds outer local_proc1 (no local_proc1 in this scope)
            RETURN func_str;
        END;
    BEGIN
        proc2_str := local_func();
        RAISE NOTICE '%', proc2_str;
    END;
BEGIN
    local_proc2();
END;

Output:

NOTICE:  call outer local_procedure1
NOTICE:  raise inner local_func

Example: argument mismatch causes an error

When local_proc1 is found in the outer scope but called with a mismatched argument, the system reports an error instead of searching further:

DECLARE
    PROCEDURE local_proc1 IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure1';
    END;

    PROCEDURE local_proc2 IS
        proc2_str VARCHAR(50);
        FUNCTION local_func RETURN VARCHAR IS
            func_str VARCHAR(50) := 'raise inner local_func';
        BEGIN
            local_proc1(1); -- local_proc1 found but doesn't accept an argument
            RETURN func_str;
        END;
    BEGIN
        proc2_str := local_func();
        RAISE NOTICE '%', proc2_str;
    END;
BEGIN
    local_proc2();
END;

Output:

ERROR:  wrong number or types of arguments in call to local function local_proc1

Forward declaration

When two subprograms in the same block need to call each other, declare the first one before defining it — this is called a forward declaration. Define all forward-declared subprograms in the same block; leaving a forward declaration without a body causes an error.

Example: missing body

DECLARE
    FUNCTION func_test(id INT) RETURN INT; -- Forward declaration only, no body
BEGIN
END;

Output:

ERROR:  subroutine body must be defined for forward-declared function "func_test"

Example: mutual recursion with forward declaration

DECLARE
  PROCEDURE proc1(number1 NUMBER); -- Forward declaration of proc1

  PROCEDURE proc2(number2 NUMBER) IS -- Declares and defines proc2
  BEGIN
    proc1(number2); -- Calls proc1 (declared above, defined below)
  END;

  PROCEDURE proc1(number1 NUMBER) IS -- Definition of proc1
  BEGIN
    proc2(number1); -- Calls proc2
  END;

BEGIN
  NULL;
END;

Make sure mutually recursive subprograms have correct exit conditions. Exceeding the call stack limit causes a runtime error.

Variable scope

A subprogram can read and modify any variable, cursor, or exception declared before the subprogram declaration. Variables declared after the subprogram declaration are not visible inside it.

A subprogram can access all identifiers declared in the outer blocks, provided those identifiers are declared before the subprogram's own declaration.

Example: variable visibility

DECLARE
    a INT := 1;          -- Declared before local_proc; visible inside it
    PROCEDURE local_proc IS
    BEGIN
        RAISE NOTICE 'call outer local_procedure';
        RAISE NOTICE 'inner raise a: %', a;
        a := 10;         -- Modifies the outer variable
    END;
    b INT;               -- Declared after local_proc; not visible inside it
BEGIN
    RAISE NOTICE 'outer raise a: %', a;
    local_proc();
    RAISE NOTICE 'outer raise a: %', a;
END;

Output:

NOTICE:  outer raise a: 1
NOTICE:  call outer local_procedure
NOTICE:  inner raise a: 1
NOTICE:  outer raise a: 10

Accessing a variable declared after the subprogram causes a syntax error:

DECLARE
    a INT := 1;
    PROCEDURE local_proc IS
    BEGIN
        RAISE NOTICE 'inner raise b: %', b; -- b is not visible here
    END;
    b INT;
BEGIN
    local_proc();
END;

Output:

ERROR:  "b" is not a known variable