FirebirdSQL logo

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

BEGIN …​ END

Delimits a block of statements

Syntax
<block> ::=
  BEGIN
    [<compound_statement> ...]
  END

<compound_statement> ::= {<block> | <statement>}

The BEGIN …​ END construct is a two-part statement that wraps a block of statements that are executed as one unit of code.Each block starts with the keyword BEGIN and ends with the keyword END.Blocks can be nested a maximum depth of 512 nested blocks.A block can be empty, allowing them to act as stubs, without the need to write dummy statements.

The BEGIN …​ END itself should not be followed by a statement terminator (semicolon).However, when defining or altering a PSQL module in the isql utility, that application requires that the last END statement be followed by its own terminator character, that was previously switched — using SET TERM — to a string other than a semicolon.That terminator is not part of the PSQL syntax.

The final, or outermost, END statement in a trigger terminates the trigger.What the final END statement does in a stored procedure depends on the type of procedure:

  • In a selectable procedure, the final END statement returns control to the caller, returning SQLCODE 100, indicating that there are no more rows to retrieve

  • In an executable procedure, the final END statement returns control to the caller, along with the current values of any output parameters defined.