FirebirdSQL logo

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.

Examples of Sub-Procedures

  1. Subroutines in EXECUTE BLOCK

    EXECUTE BLOCK
      RETURNS (name VARCHAR(63))
    AS
      -- Sub-procedure returning a list of tables
      DECLARE PROCEDURE get_tables
        RETURNS (table_name VARCHAR(63))
      AS
      BEGIN
        FOR SELECT RDB$RELATION_NAME
          FROM RDB$RELATIONS
          WHERE RDB$VIEW_BLR IS NULL
          INTO table_name
        DO SUSPEND;
      END
      -- Sub-procedure returning a list of views
      DECLARE PROCEDURE get_views
        RETURNS (view_name VARCHAR(63))
      AS
      BEGIN
        FOR SELECT RDB$RELATION_NAME
          FROM RDB$RELATIONS
          WHERE RDB$VIEW_BLR IS NOT NULL
          INTO view_name
        DO SUSPEND;
      END
    BEGIN
      FOR SELECT table_name
        FROM get_tables
        UNION ALL
        SELECT view_name
        FROM get_views
        INTO name
      DO SUSPEND;
    END
  2. With forward declaration and parameter with default value

    execute block returns (o integer)
    as
        -- Forward declaration of P1.
        declare procedure p1(i integer = 1) returns (o integer);
    
        -- Forward declaration of P2.
        declare procedure p2(i integer) returns (o integer);
    
        -- Implementation of P1 should not re-declare parameter default value.
        declare procedure p1(i integer) returns (o integer)
        as
        begin
            execute procedure p2(i) returning_values o;
        end
    
        declare procedure p2(i integer) returns (o integer)
        as
        begin
            o = i;
        end
    begin
        execute procedure p1 returning_values o;
        suspend;
    end