DECLARE PROCEDURE

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

`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

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

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