Examples Of Sub-Procedures

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

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

`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.

Examples Of Sub-Procedures

Examples of Sub-Procedures

BEGIN …​ END

`BEGIN … END`