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.

BEGIN …​ END Examples

A sample procedure from the employee.fdb database, showing simple usage of BEGIN …​ END blocks:
SET TERM ^;
CREATE OR ALTER PROCEDURE DEPT_BUDGET (
  DNO CHAR(3))
RETURNS (
  TOT DECIMAL(12,2))
AS
  DECLARE VARIABLE SUMB DECIMAL(12,2);
  DECLARE VARIABLE RDNO CHAR(3);
  DECLARE VARIABLE CNT  INTEGER;
BEGIN
  TOT = 0;

  SELECT BUDGET
  FROM DEPARTMENT
  WHERE DEPT_NO = :DNO
  INTO :TOT;

  SELECT COUNT(BUDGET)
  FROM DEPARTMENT
  WHERE HEAD_DEPT = :DNO
  INTO :CNT;

  IF (CNT = 0) THEN
    SUSPEND;

  FOR SELECT DEPT_NO
    FROM DEPARTMENT
    WHERE HEAD_DEPT = :DNO
    INTO :RDNO
  DO
  BEGIN
    EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
      RETURNING_VALUES :SUMB;
    TOT = TOT + SUMB;
  END

  SUSPEND;
END^
SET TERM ;^

IF …​ THEN …​ ELSE

Conditional branching

Syntax
IF (<condition>)
  THEN <compound_statement>
  [ELSE <compound_statement>]
Table 1. IF …​ THEN …​ ELSE Parameters
Argument Description

condition

A logical condition returning TRUE, FALSE or UNKNOWN

compound_statement

A single statement, or statements wrapped in BEGIN …​ END

The conditional branch statement IF …​ THEN is used to branch the execution process in a PSQL module.The condition is always enclosed in parentheses.If the condition returns the value TRUE, execution branches to the statement or the block of statements after the keyword THEN.If an ELSE is present, and the condition returns FALSE or UNKNOWN, execution branches to the statement or the block of statements after it.

Multi-Branch Decisions

PSQL does not provide more advanced multi-branch jumps, such as CASE or SWITCH.However, it is possible to chain IF …​ THEN …​ ELSE statements, see the example section below.Alternatively, the CASE statement from DSQL is available in PSQL and is able to satisfy at least some use cases in the manner of a switch:

CASE <test_expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> ...]
  [ELSE <defaultresult>]
END
Example in PSQL
...
C = CASE
      WHEN A=2 THEN 1
      WHEN A=1 THEN 3
      ELSE 0
    END;
...