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 retrieveIn 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