FirebirdSQL logo

WHILE …​ DO Examples

A procedure calculating the sum of numbers from 1 to I shows how the looping construct is used.

CREATE PROCEDURE SUM_INT (I INTEGER)
RETURNS (S INTEGER)
AS
BEGIN
  s = 0;
  WHILE (i > 0) DO
  BEGIN
    s = s + i;
    i = i - 1;
  END
END

Executing the procedure in isql:

EXECUTE PROCEDURE SUM_INT(4);

the result is:

S
==========
10

Trapping and Handling Errors

Firebird has a useful lexicon of PSQL statements and resources for trapping errors in modules and for handling them.Firebird uses built-in exceptions that are raised for errors occurring when working DML and DDL statements.

In PSQL code, exceptions are handled by means of the WHEN statement.Handling an exception in the code involves either fixing the problem in situ, or stepping past it;either solution allows execution to continue without returning an exception message to the client.

An exception results in execution being terminated in the current block.Instead of passing the execution to the END statement, the procedure moves outward through levels of nested blocks, starting from the block where the exception is caught, searching for the code of the handler that “knows” about this exception.It stops searching when it finds the first WHEN statement that can handle this exception.