7.6.4. BEGIN …​ END

Used for

Delineating a block of statements

Available in

PSQL

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 half-statement BEGIN and ends with the other half-statement END. Blocks can be nested to unlimited depth. They may be empty, allowing them to act as stubs, without the need to write dummy statements.

The BEGIN and END statements have no line terminators. 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 some 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.

Example

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 ;^

See also

EXIT, LEAVE, SET TERM