FirebirdSQL logo
 Data Manipulation (DML) StatementsBuilt-in Scalar Functions 

IN AUTONOMOUS TRANSACTION

Executes a statement or a block of statements in an autonomous transaction

Syntax
IN AUTONOMOUS TRANSACTION DO <compound_statement>
Table 1. IN AUTONOMOUS TRANSACTION Statement Parameter
Argument Description

compound_statement

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

The IN AUTONOMOUS TRANSACTION statement enables execution of a statement or a block of statements in an autonomous transaction.Code running in an autonomous transaction will be committed right after its successful execution, regardless of the status of its parent transaction.This can be used when certain operations must not be rolled back, even if an error occurs in the parent transaction.

An autonomous transaction has the same isolation level as its parent transaction.Any exception that is thrown in the block of the autonomous transaction code will result in the autonomous transaction being rolled back and all changes made will be undone.If the code executes successfully, the autonomous transaction will be committed.

IN AUTONOMOUS TRANSACTION Examples

Using an autonomous transaction in a trigger for the database ON CONNECT event, to log all connection attempts, including those that failed:

CREATE TRIGGER TR_CONNECT ON CONNECT
AS
BEGIN
  -- Logging all attempts to connect to the database
  IN AUTONOMOUS TRANSACTION DO
    INSERT INTO LOG(MSG)
    VALUES ('USER ' || CURRENT_USER || ' CONNECTS.');
  IF (EXISTS(SELECT *
             FROM BLOCKED_USERS
             WHERE USERNAME = CURRENT_USER)) THEN
  BEGIN
    -- Logging that the attempt to connect
    -- to the database failed and sending
    -- a message about the event
    IN AUTONOMOUS TRANSACTION DO
    BEGIN
      INSERT INTO LOG(MSG)
      VALUES ('USER ' || CURRENT_USER || ' REFUSED.');
      POST_EVENT 'CONNECTION ATTEMPT BY BLOCKED USER!';
    END
    -- now calling an exception
    EXCEPTION EX_BADUSER;
  END
END
See also

Transaction Control

POST_EVENT

Posts an event for notification to registered clients on commit

Syntax
POST_EVENT event_name;
Table 1. POST_EVENT Statement Parameter
Argument Description

event_name

Event name (message) limited to 127 bytes

The POST_EVENT statement notifies the event manager about the event, which saves it to an event table.When the transaction is committed, the event manager notifies applications that have registered their interest in the event.

The event name can be a code, or a short message: the choice is open as it is a string of up to 127 bytes.Keep in mind that the application listening for an event must use the exact event name when registering.

The content of the string can be a string literal, a variable or any valid SQL expression that resolves to a string.

POST_EVENT Examples

Notifying the listening applications about inserting a record into the SALES table:

CREATE TRIGGER POST_NEW_ORDER FOR SALES
ACTIVE AFTER INSERT POSITION 0
AS
BEGIN
  POST_EVENT 'new_order';
END

RETURN

Returns a value from a stored function

Syntax
RETURN value;
Table 1. RETURN Statement Parameter
Argument Description

value

Expression with the value to return;Can be any expression type-compatible with the return type of the function

The RETURN statement ends the execution of a function and returns the value of the expression value.

RETURN can only be used in PSQL functions (stored functions and local sub-functions).

RETURN Examples

See CREATE FUNCTION Examples

DECLARE VARIABLE

Declares a local variable

Syntax
DECLARE [VARIABLE] varname
  <domain_or_non_array_type> [NOT NULL] [COLLATE collation]
  [{DEFAULT | = } <initvalue>];

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<initvalue> ::= <literal> | <context_var>
Table 1. DECLARE VARIABLE Statement Parameters
Argument Description

varname

Name of the local variable

collation

Collation

initvalue

Initial value for this variable

literal

Literal of a type compatible with the type of the local variable

context_var

Any context variable whose type is compatible with the type of the local variable

The statement DECLARE [VARIABLE] is used for declaring a local variable.One DECLARE [VARIABLE] statement is required for each local variable.Any number of DECLARE [VARIABLE] statements can be included and in any order.The name of a local variable must be unique among the names of local variables and input and output parameters declared for the module.

A special case of DECLARE [VARIABLE] — declaring cursors — is covered separately in [fblangref50-psql-declare-cursor]

Data Type for Variables

A local variable can be of any SQL type other than an array.

  • A domain name can be specified as the type;the variable will inherit all of its attributes.

  • If the TYPE OF domain clause is used instead, the variable will inherit only the domain’s data type, and, if applicable, its character set and collation attributes.Any default value or constraints such as NOT NULL or CHECK constraints are not inherited.

  • If the TYPE OF COLUMN relation.column option is used to “borrow” from a column in a table or view, the variable will inherit only the column’s data type, and, if applicable, its character set and collation attributes.Any other attributes are ignored.

NOT NULL Constraint

For local variables, you can specify the NOT NULL constraint, disallowing NULL values for the variable.If a domain has been specified as the data type and the domain already has the NOT NULL constraint, the declaration is unnecessary.For other forms, including use of a domain that is nullable, the NOT NULL constraint can be included if needed.

CHARACTER SET and COLLATE clauses

Unless specified, the character set and collation of a string variable will be the database defaults.A CHARACTER SET clause can be specified to handle string data that needs a different character set.A valid collation (COLLATE clause) can also be included, with or without the character set clause.

Initializing a Variable

Local variables are NULL when execution of the module begins.They can be explicitly initialized so that a starting or default value is available when they are first referenced.The initial value can be specified in two ways, DEFAULT <initvalue> and = <initvalue>.The value can be any type-compatible literal or context variable, including NULL.

Tip

Be sure to use this clause for any variables that have a NOT NULL constraint and do not otherwise have a default value available (i.e. inherited from a domain).

Examples of various ways to declare local variables

CREATE OR ALTER PROCEDURE SOME_PROC
AS
  -- Declaring a variable of the INT type
  DECLARE I INT;
  -- Declaring a variable of the INT type that does not allow NULL
  DECLARE VARIABLE J INT NOT NULL;
  -- Declaring a variable of the INT type with the default value of 0
  DECLARE VARIABLE K INT DEFAULT 0;
  -- Declaring a variable of the INT type with the default value of 1
  DECLARE VARIABLE L INT = 1;
  -- Declaring a variable based on the COUNTRYNAME domain
  DECLARE FARM_COUNTRY COUNTRYNAME;
  -- Declaring a variable of the type equal to the COUNTRYNAME domain
  DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
  -- Declaring a variable with the type of the CAPITAL column in the COUNTRY table
  DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
BEGIN
  /* PSQL statements */
END
See also

Data Types and Subtypes, Custom Data Types — Domains, CREATE DOMAIN

DECLARE .. CURSOR

Declares a named cursor

Syntax
DECLARE [VARIABLE] cursor_name
  [[NO] SCROLL] CURSOR
  FOR (<select>);
Table 1. DECLARE …​ CURSOR Statement Parameters
Argument Description

cursor_name

Cursor name

select

SELECT statement

The DECLARE …​ CURSOR …​ FOR statement binds a named cursor to the result set obtained by the SELECT statement specified in the FOR clause.In the body code, the cursor can be opened, used to iterate row-by-row through the result set, and closed.While the cursor is open, the code can perform positioned updates and deletes using the WHERE CURRENT OF in the UPDATE or DELETE statement.

Note

Syntactically, the DECLARE …​ CURSOR statement is a special case of [fblangref50-psql-declare-variable].

Forward-Only and Scrollable Cursors

The cursor can be forward-only (unidirectional) or scrollable.The optional clause SCROLL makes the cursor scrollable, the NO SCROLL clause, forward-only.By default, cursors are forward-only.

Forward-only cursors can — as the name implies — only move forward in the dataset.Forward-only cursors only support the FETCH [NEXT FROM] statement, other fetch options raise an error.Scrollable cursors allow you to move not only forward in the dataset, but also back, as well as N positions relative to the current position.

Warning

Scrollable cursors are materialized as a temporary dataset, as such, they consume additional memory or disk space, so use them only when you really need them.

Cursor Idiosyncrasies

  • The optional FOR UPDATE clause can be included in the SELECT statement, but its absence does not prevent successful execution of a positioned update or delete

  • Care should be taken to ensure that the names of declared cursors do not conflict with any names used subsequently in statements for AS CURSOR clauses

  • If the cursor is needed only to walk the result set, it is nearly always easier and less error-prone to use a FOR SELECT statement with the AS CURSOR clause.Declared cursors must be explicitly opened, used to fetch data, and closed.The context variable ROW_COUNT has to be checked after each fetch and, if its value is zero, the loop has to be terminated.A FOR SELECT statement does this automatically.

    Nevertheless, declared cursors provide a high level of control over sequential events and allow several cursors to be managed in parallel.

  • The SELECT statement may contain parameters. For instance:

    SELECT NAME || :SFX FROM NAMES WHERE NUMBER = :NUM

    Each parameter has to have been declared beforehand as a PSQL variable, or as input or output parameters.When the cursor is opened, the parameter is assigned the current value of the variable.

Warning
Unstable Variables and Cursors

If the value of the PSQL variable used in the SELECT statement of the cursor changes during the execution of the loop, then its new value may — but not always — be used when selecting the next rows.It is better to avoid such situations.If you really need this behaviour, then you should thoroughly test your code and make sure you understand how changes to the variable affect the query results.

Note particularly that the behaviour may depend on the query plan, specifically on the indexes being used.Currently, there are no strict rules for this behaviour, and this may change in future versions of Firebird.

Examples Using Named Cursors

  1. Declaring a named cursor in a trigger.

    CREATE OR ALTER TRIGGER TBU_STOCK
  BEFORE UPDATE ON STOCK
AS
  DECLARE C_COUNTRY CURSOR FOR (
    SELECT
      COUNTRY,
      CAPITAL
    FROM COUNTRY
  );
BEGIN
  /* PSQL statements */
END

  2. Declaring a scrollable cursor

    EXECUTE BLOCK
  RETURNS (
    N INT,
    RNAME CHAR(63))
AS
  - Declaring a scrollable cursor
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER (ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  / * PSQL statements * /
END

  3. A collection of scripts for creating views with a PSQL block using named cursors.

    EXECUTE BLOCK
RETURNS (
  SCRIPT BLOB SUB_TYPE TEXT)
AS
  DECLARE VARIABLE FIELDS VARCHAR(8191);
  DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
  DECLARE VARIABLE RELATION RDB$RELATION_NAME;
  DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
  DECLARE VARIABLE CUR_R CURSOR FOR (
    SELECT
      RDB$RELATION_NAME,
      RDB$VIEW_SOURCE
    FROM
      RDB$RELATIONS
    WHERE
      RDB$VIEW_SOURCE IS NOT NULL);
  -- Declaring a named cursor where
  -- a local variable is used
  DECLARE CUR_F CURSOR FOR (
    SELECT
      RDB$FIELD_NAME
    FROM
      RDB$RELATION_FIELDS
    WHERE
      -- the variable must be declared earlier
      RDB$RELATION_NAME = :RELATION);
BEGIN
  OPEN CUR_R;
  WHILE (1 = 1) DO
  BEGIN
    FETCH CUR_R
    INTO :RELATION, :SOURCE;
    IF (ROW_COUNT = 0) THEN
      LEAVE;

    FIELDS = NULL;
    -- The CUR_F cursor will use the value
    -- of the RELATION variable initiated above
    OPEN CUR_F;
    WHILE (1 = 1) DO
    BEGIN
      FETCH CUR_F
      INTO :FIELD_NAME;
      IF (ROW_COUNT = 0) THEN
        LEAVE;
      IF (FIELDS IS NULL) THEN
        FIELDS = TRIM(FIELD_NAME);
      ELSE
        FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
    END
    CLOSE CUR_F;

    SCRIPT = 'CREATE VIEW ' || RELATION;

    IF (FIELDS IS NOT NULL) THEN
      SCRIPT = SCRIPT || ' (' || FIELDS || ')';

    SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
    SCRIPT = SCRIPT || SOURCE;

    SUSPEND;
  END
  CLOSE CUR_R;
END
See also

[fblangref50-psql-open], [fblangref50-psql-fetch], [fblangref50-psql-close]

DECLARE FUNCTION

Declares a sub-function

Syntax
<subfunc-forward> ::= <subfunc-header>;

<subfunc-def> ::= <subfunc-header> <psql-module-body>

<subfunc-header>  ::=
  DECLARE FUNCTION subfuncname [ ( [ <in_params> ] ) ]
  RETURNS <domain_or_non_array_type> [COLLATE collation]
  [DETERMINISTIC]

<in_params> ::=
  !! See CREATE FUNCTION Syntax !!

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of Module Body !!
Table 1. DECLARE FUNCTION Statement Parameters
Argument Description

subfuncname

Sub-function name

collation

Collation name

The DECLARE FUNCTION statement declares a sub-function.A sub-function is only visible to the PSQL module that defined the sub-function.

A sub-function can use variables, but not cursors, from its parent module.It can access other routines from its parent modules, including recursive calls to itself.

Sub-functions have a number of restrictions:

  • A sub-function cannot be nested in another subroutine.Subroutines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and PSQL blocks).This restriction is not enforced by the syntax, but attempts to create nested sub-functions will raise an error “feature is not supported” with detail message “nested sub function”.

  • Currently, a sub-function has no direct access to use cursors from its parent module.

A sub-function can be forward declared to resolve mutual dependencies between subroutines, and must be followed by its actual definition.When a sub-function is forward declared and has parameters with default values, the default values should only be specified in the forward declaration, and should not be repeated in subfunc_def.

Note

Declaring a sub-function with the same name as a stored function will hide that stored function from your module.It will not be possible to call that stored function.
Note

Contrary to DECLARE [VARIABLE], a DECLARE FUNCTION is not terminated by a semicolon.The END of its main BEGIN …​ END block is considered its terminator.

Examples of Sub-Functions

  1. Sub-function within a stored function

    CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
  RETURNS INTEGER
AS
- Subfunction
  DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
    RETURNS INTEGER
  AS
  BEGIN
    RETURN n1 + n2;
  END
BEGIN
  RETURN SUBFUNC (n1, n2);
END

  2. Recursive function call

    execute block returns (i integer, o integer)
as
    -- Recursive function without forward declaration.
    declare function fibonacci(n integer) returns integer
    as
    begin
      if (n = 0 or n = 1) then
       return n;
     else
       return fibonacci(n - 1) + fibonacci(n - 2);
    end
begin
  i = 0;

  while (i < 10)
  do
  begin
    o = fibonacci(i);
    suspend;
    i = i + 1;
  end
end
See also

[fblangref50-psql-declproc], CREATE FUNCTION

DECLARE PROCEDURE

Declares a sub-procedure

Syntax
<subproc-forward> ::= <subproc-header>;

<subproc-def> ::= <subproc-header> <psql-module-body>

<subproc-header>  ::=
DECLARE subprocname [ ( [ <in_params> ] ) ]
  [RETURNS (<out_params>)]

<in_params> ::=
  !! See CREATE PROCEDURE Syntax !!

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<psql-module-body> ::=
  !! See Syntax of Module Body !!
Table 1. DECLARE PROCEDURE Statement Parameters
Argument Description

subprocname

Sub-procedure name

collation

Collation name

The DECLARE PROCEDURE statement declares a sub-procedure.A sub-procedure is only visible to the PSQL module that defined the sub-procedure.

A sub-procedure can use variables, but not cursors, from its parent module.It can access other routines from its parent modules.

Sub-procedures have a number of restrictions:

  • A sub-procedure cannot be nested in another subroutine.Subroutines are only supported in top-level PSQL modules (stored procedures, stored functions, triggers and PSQL blocks).This restriction is not enforced by the syntax, but attempts to create nested sub-procedures will raise an error “feature is not supported” with detail message “nested sub procedure”.

  • Currently, the sub-procedure has no direct access to use cursors from its parent module.

A sub-procedure can be forward declared to resolve mutual dependencies between subroutines, and must be followed by its actual definition.When a sub-procedure is forward declared and has parameters with default values, the default values should only be specified in the forward declaration, and should not be repeated in subproc_def.

Note

Declaring a sub-procedure with the same name as a stored procedure, table or view will hide that stored procedure, table or view from your module.It will not be possible to call that stored procedure, table or view.
Note

Contrary to DECLARE [VARIABLE], a DECLARE PROCEDURE is not terminated by a semicolon.The END of its main BEGIN …​ END block is considered its terminator.

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
See also

[fblangref50-psql-declfunc], CREATE PROCEDURE

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

[fblangref50-psql-exit], 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;
...

IF Examples

  1. An example using the IF statement.Assume that the variables FIRST, LINE2 and LAST were declared earlier.

    ...
IF (FIRST IS NOT NULL) THEN
  LINE2 = FIRST || ' ' || LAST;
ELSE
  LINE2 = LAST;
...

  2. Given IF …​ THEN …​ ELSE is a statement, it is possible to chain them together.Assume that the INT_VALUE and STRING_VALUE variables were declared earlier.

    IF (INT_VALUE = 1) THEN
  STRING_VALUE = 'one';
ELSE IF (INT_VALUE = 2) THEN
  STRING_VALUE = 'two';
ELSE IF (INT_VALUE = 3) THEN
  STRING_VALUE = 'three';
ELSE
  STRING_VALUE = 'too much';

    This specific example can be replaced with a simple CASE or the DECODE function.

See also

[fblangref50-psql-while], CASE

WHILE …​ DO

Looping construct

Syntax
[label:]
WHILE (<condition>) DO
  <compound_statement>
Table 1. WHILE …​ DO Parameters
Argument Description

label

Optional label for LEAVE and CONTINUE.Follows the rules for identifiers.

condition

A logical condition returning TRUE, FALSE or UNKNOWN

compound_statement

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

A WHILE statement implements the looping construct in PSQL.The statement or the block of statements will be executed as long as the condition returns TRUE.Loops can be nested to any depth.

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
See also

[fblangref50-psql-ifthen], [fblangref50-psql-break], [fblangref50-psql-leave], [fblangref50-psql-continue], [fblangref50-psql-exit], [fblangref50-psql-forselect], [fblangref50-psql-forexec]

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.

System Exceptions

An exception is a message that is generated when an error occurs.

All exceptions handled by Firebird have predefined numeric values for context variables (symbols) and text messages associated with them.Error messages are output in English by default.Localized Firebird builds are available, where error messages are translated into other languages.

Complete listings of the system exceptions can be found in [fblangref50-appx02-errorcodes]:

Custom Exceptions

Custom exceptions can be declared in the database as persistent objects and called in PSQL code to signal specific errors;for example, to enforce certain business rules.A custom exception consists of an identifier, and a default message of 1021 bytes.For details, see CREATE EXCEPTION.

EXCEPTION

Throws a user-defined exception or rethrows an exception

Syntax
EXCEPTION [
    exception_name
    [ custom_message
    | USING (<value_list>)]
  ]

<value_list> ::= <val> [, <val> ...]
Table 1. EXCEPTION Statement Parameters
Argument Description

exception_name

Exception name

custom_message

Alternative message text to be returned to the caller interface when an exception is thrown.Maximum length of the text message is 1,021 bytes

val

Value expression that replaces parameter slots in the exception message text

The EXCEPTION statement with exception_name throws the user-defined exception with the specified name.An alternative message text of up to 1,021 bytes can optionally override the exception’s default message text.

The default exception message can contain slots for parameters that can be filled when throwing an exception.To pass parameter values to an exception, use the USING clause.Considering, in left-to-right order, each parameter passed in the exception-raising statement as “the Nth”, with N starting at 1:

  • If the Nth parameter is not passed, its slot is not replaced

  • If a NULL parameter is passed, the slot will be replaced with the string “*** null ***”

  • If more parameters are passed than are defined in the exception message, the surplus ones are ignored

  • The maximum number of parameters is 9

  • The maximum message length, including parameter values, is 1053 bytes

Note

The status vector is generated this code combination isc_except, <exception number>, isc_formatted_exception, <formatted exception message>, <exception parameters>.

The error code used (isc_formatted_exception) was introduced in Firebird 3.0, so the client must be at least version 3.0, or at least use the firebird.msg from version 3.0 or higher, to translate the status vector to a string.
Warning

If the message contains a parameter slot number that is greater than 9, the second and subsequent digits will be treated as literal text.For example @10 will be interpreted as slot 1 followed by a literal ‘0’.

As an example:

CREATE EXCEPTION ex1
  'something wrong in @1@2@3@4@5@6@7@8@9@10@11';
SET TERM ^;
EXECUTE BLOCK AS
BEGIN
  EXCEPTION ex1 USING ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i');
END^

This will produce the following output

Statement failed, SQLSTATE = HY000
exception 1
-EX1
-something wrong in abcdefghia0a1

Exceptions can be handled in a [fblangref50-psql-when] statement.If an exception is not handled in a module, then the effects of the actions executed inside this module are cancelled, and the caller program receives the exception (either the default text, or the custom text).

Within the exception-handling block — and only within it — the caught exception can be re-thrown by executing the EXCEPTION statement without parameters.If located outside the block, the re-thrown EXCEPTION call has no effect.

Custom exceptions are stored in the system table RDB$EXCEPTIONS.

EXCEPTION Examples

  1. Throwing an exception upon a condition in the SHIP_ORDER stored procedure:

    CREATE OR ALTER PROCEDURE SHIP_ORDER (
  PO_NUM CHAR(8))
AS
  DECLARE VARIABLE ord_stat  CHAR(7);
  DECLARE VARIABLE hold_stat CHAR(1);
  DECLARE VARIABLE cust_no   INTEGER;
  DECLARE VARIABLE any_po    CHAR(8);
BEGIN
  SELECT
    s.order_status,
    c.on_hold,
    c.cust_no
  FROM
    sales s, customer c
  WHERE
    po_number = :po_num AND
    s.cust_no = c.cust_no
  INTO :ord_stat,
       :hold_stat,
       :cust_no;

  IF (ord_stat = 'shipped') THEN
    EXCEPTION order_already_shipped;
  /* Other statements */
END

  2. Throwing an exception upon a condition and replacing the original message with an alternative message:

    CREATE OR ALTER PROCEDURE SHIP_ORDER (
  PO_NUM CHAR(8))
AS
  DECLARE VARIABLE ord_stat  CHAR(7);
  DECLARE VARIABLE hold_stat CHAR(1);
  DECLARE VARIABLE cust_no   INTEGER;
  DECLARE VARIABLE any_po    CHAR(8);
BEGIN
  SELECT
    s.order_status,
    c.on_hold,
    c.cust_no
  FROM
    sales s, customer c
  WHERE
    po_number = :po_num AND
    s.cust_no = c.cust_no
  INTO :ord_stat,
       :hold_stat,
       :cust_no;

  IF (ord_stat = 'shipped') THEN
    EXCEPTION order_already_shipped
      'Order status is "' || ord_stat || '"';
  /* Other statements */
END

  3. Using a parameterized exception:

    CREATE EXCEPTION EX_BAD_SP_NAME
  'Name of procedures must start with' '@ 1' ':' '@ 2' '' ;
...
CREATE TRIGGER TRG_SP_CREATE BEFORE CREATE PROCEDURE
AS
  DECLARE SP_NAME VARCHAR(255);
BEGIN
  SP_NAME = RDB$GET_CONTEXT ('DDL_TRIGGER' , 'OBJECT_NAME');
  IF (SP_NAME NOT STARTING 'SP_') THEN
    EXCEPTION EX_BAD_SP_NAME USING ('SP_', SP_NAME);
END

  4. Logging an error and re-throwing it in the WHEN block:

    CREATE PROCEDURE ADD_COUNTRY (
  ACountryName COUNTRYNAME,
  ACurrency VARCHAR(10))
AS
BEGIN
  INSERT INTO country (country,
                       currency)
  VALUES (:ACountryName,
          :ACurrency);
  WHEN ANY DO
  BEGIN
    -- write an error in log
    IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERROR_LOG (PSQL_MODULE,
                             GDS_CODE,
                             SQL_CODE,
                             SQL_STATE)
      VALUES ('ADD_COUNTRY',
              GDSCODE,
              SQLCODE,
              SQLSTATE);
    -- Re-throw exception
    EXCEPTION;
  END
END
See also

CREATE EXCEPTION, [fblangref50-psql-when]

WHEN …​ DO

Catches an exception for error handling

Syntax
WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>

<error> ::=
  { EXCEPTION exception_name
  | SQLCODE number
  | GDSCODE errcode
  | SQLSTATE sqlstate_code }
Table 1. WHEN …​ DO Statement Parameters
Argument Description

exception_name

Exception name

number

SQLCODE error code

errcode

Symbolic GDSCODE error name

sqlstate_code

String literal with the SQLSTATE error code

compound_statement

A single statement, or a block of statements

The WHEN …​ DO statement handles Firebird errors and user-defined exceptions.The statement catches all errors and user-defined exceptions listed after the keyword WHEN keyword.If WHEN is followed by the keyword ANY, the statement catches any error or user-defined exception, even if they have already been handled in a WHEN block located higher up.

The WHEN …​ DO statements must be located at the end of a block of statements, before the block’s END statement, and after any other statement.

The keyword DO is followed by a single statement, or statements wrapped in a BEGIN …​ END block, that handles the exception.The SQLCODE, GDSCODE, and SQLSTATE context variables are available in the context of this statement or block.Use the RDB$ERROR function to obtain the SQLCODE, GDSCODE, SQLSTATE, custom exception name and exception message.The EXCEPTION statement, without parameters, can also be used in this context to re-throw the error or exception.

Targeting GDSCODE

The argument for the WHEN GDSCODE clause is the symbolic name associated with the internally-defined exception, such as grant_obj_notfound for GDS error 335544551.

In a statement or block of statements of the DO clause, a GDSCODE context variable, containing the numeric code, becomes available.That numeric code is required if you want to compare a GDSCODE exception with a targeted error.To compare it with a specific error, you need to use a numeric values, for example 335544551 for grant_obj_notfound.

Similar context variables are available for SQLCODE and SQLSTATE.

The WHEN …​ DO statement or block is only executed when one of the events targeted by its conditions occurs at run-time.If the WHEN …​ DO statement is executed, even if it does nothing, execution will continue as if no error occurred: the error or user-defined exception neither terminates nor rolls back the operations of the trigger or stored procedure.

However, if the WHEN …​ DO statement or block does nothing to handle or resolve the error, the DML statement (SELECT, INSERT, UPDATE, DELETE, MERGE) that caused the error will be rolled back and none of the statements below it in the same block of statements are executed.

Important

  1. If the error is not caused by one of the DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE), the entire block of statements will be rolled back, not only the one that caused an error.Any operations in the WHEN …​ DO statement will be rolled back as well.The same limitation applies to the EXECUTE PROCEDURE statement.Read an interesting discussion of the phenomenon in Firebird Tracker ticket firebird#4803.

  2. In selectable stored procedures, output rows that were already passed to the client in previous iterations of a FOR SELECT …​ DO …​ SUSPEND loop remain available to the client if an exception is thrown subsequently in the process of retrieving rows.

Scope of a WHEN …​ DO Statement

A WHEN …​ DO statement catches errors and exceptions in the current block of statements.It also catches exceptions from nested blocks, if those exceptions have not been handled in those blocks.

All changes made before the statement that caused the error are visible to a WHEN …​ DO statement.However, if you try to log them in an autonomous transaction, those changes are unavailable, because the transaction where the changes took place is not committed at the point when the autonomous transaction is started.Example 4, below, demonstrates this behaviour.

Tip

When handling exceptions, it is sometimes desirable to handle the exception by writing a log message to mark the fault and having execution continue past the faulty record.Logs can be written to regular tables, but there is a problem with that: the log records will “disappear” if an unhandled error causes the module to stop executing, and a rollback is performed.Use of external tables can be useful here, as data written to them is transaction-independent.The date inserted into a linked external file will still be there, regardless of whether the overall process succeeds or not.

Examples using WHEN…​DO

  1. Replacing the standard error with a custom one:

    CREATE EXCEPTION COUNTRY_EXIST '';
SET TERM ^;
CREATE PROCEDURE ADD_COUNTRY (
  ACountryName COUNTRYNAME,
  ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country, currency)
    VALUES (:ACountryName, :ACurrency);

  WHEN SQLCODE -803 DO
    EXCEPTION COUNTRY_EXIST 'Country already exists!';
END^
SET TERM ^;

  2. Logging an error and re-throwing it in the WHEN block:

    CREATE PROCEDURE ADD_COUNTRY (
  ACountryName COUNTRYNAME,
  ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country,
                       currency)
  VALUES (:ACountryName,
          :ACurrency);
  WHEN ANY DO
  BEGIN
    -- write an error in log
    IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERROR_LOG (PSQL_MODULE,
                             GDS_CODE,
                             SQL_CODE,
                             SQL_STATE,
                             MESSAGE)
      VALUES ('ADD_COUNTRY',
              GDSCODE,
              SQLCODE,
              SQLSTATE,
              RDB$ERROR(MESSAGE));
    -- Re-throw exception
    EXCEPTION;
  END
END

  3. Handling several errors in one WHEN block

    ...
WHEN GDSCODE GRANT_OBJ_NOTFOUND,
	 GDSCODE GRANT_FLD_NOTFOUND,
	 GDSCODE GRANT_NOPRIV,
	 GDSCODE GRANT_NOPRIV_ON_BASE
DO
BEGIN
  EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE,
    RDB$ERROR(MESSAGE);
  EXIT;
END
...

  4. Catching errors using the SQLSTATE code

    EXECUTE BLOCK
AS
  DECLARE VARIABLE I INT;
BEGIN
  BEGIN
    I = 1/0;
    WHEN SQLSTATE '22003' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Numeric value out of range.';
    WHEN SQLSTATE '22012' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Division by zero.';
    WHEN SQLSTATE '23000' DO
      EXCEPTION E_CUSTOM_EXCEPTION
       'Integrity constraint violation.';
  END
END
See also

[fblangref50-psql-exception], CREATE EXCEPTION, SQLCODE and GDSCODE Error Codes and Message Texts and SQLSTATE Codes and Message Texts, GDSCODE, SQLCODE, SQLSTATE, RDB$ERROR()