FirebirdSQL logo

Examples of Sub-Functions

  1. Sub-function within a stored function

    CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
      RETURNS INTEGER
    AS
    - Subfunction
      DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
        RETURNS INTEGER
      AS
      BEGIN
        RETURN n1 + n2;
      END
    BEGIN
      RETURN SUBFUNC (n1, n2);
    END
  2. Recursive function call

    execute block returns (i integer, o integer)
    as
        -- Recursive function without forward declaration.
        declare function fibonacci(n integer) returns integer
        as
        begin
          if (n = 0 or n = 1) then
           return n;
         else
           return fibonacci(n - 1) + fibonacci(n - 2);
        end
    begin
      i = 0;
    
      while (i < 10)
      do
      begin
        o = fibonacci(i);
        suspend;
        i = i + 1;
      end
    end

DECLARE PROCEDURE

Declares a sub-procedure

Syntax
<subproc-forward> ::= <subproc-header>;

<subproc-def> ::= <subproc-header> <psql-module-body>

<subproc-header>  ::=
DECLARE subprocname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]

<in_params> ::=
  !! See CREATE PROCEDURE Syntax !!

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of Module Body !!
Table 1. DECLARE PROCEDURE Statement Parameters
Argument Description

subprocname

Sub-procedure name

collation

Collation name

The DECLARE PROCEDURE statement declares a sub-procedure.A sub-procedure is only visible to the PSQL module that defined the sub-procedure.

A sub-procedure can use variables, but not cursors, from its parent module.It can access other routines from its parent modules.

Sub-procedures have a number of restrictions:

  • A sub-procedure cannot be nested in another subroutine.Subroutines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and PSQL blocks).This restriction is not enforced by the syntax, but attempts to create nested sub-procedures will raise an error “feature is not supported” with detail message “nested sub procedure”.

  • Currently, the sub-procedure has no direct access to use cursors from its parent module.

A sub-procedure can be forward declared to resolve mutual dependencies between subroutines, and must be followed by its actual definition.When a sub-procedure is forward declared and has parameters with default values, the default values should only be specified in the forward declaration, and should not be repeated in subproc_def.

Note

Declaring a sub-procedure with the same name as a stored procedure, table or view will hide that stored procedure, table or view from your module.It will not be possible to call that stored procedure, table or view.

Note

Contrary to DECLARE [VARIABLE], a DECLARE PROCEDURE is not terminated by a semicolon.The END of its main BEGIN …​ END block is considered its terminator.