Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Procedural SQL (PSQL) Statements → Writing the Body Code
Firebird Firebird Prev: TriggersFirebird 2.5 Language ReferenceUp: Procedural SQL (PSQL) StatementsNext: Trapping and Handling Errors

Writing the Body Code

Table of Contents

Assignment Statements
DECLARE CURSOR
DECLARE VARIABLE
BEGIN ... END
IF ... THEN ... ELSE
WHILE ... DO
LEAVE
EXIT
SUSPEND
EXECUTE STATEMENT
FOR SELECT
FOR EXECUTE STATEMENT
OPEN
FETCH
CLOSE
IN AUTONOMOUS TRANSACTION
POST_EVENT

This section takes a closer look at the procedural SQL language constructs and statements that are available for coding the body of a stored procedure, trigger or anonymous PSQL block.

Colon Marker (:)

The colon marker prefix (:) is used in PSQL to mark a reference to a variable in a DML statement. The colon marker is not required before variable names in other code and it should never be applied to context variables.

Assignment Statements

Used for:  Assigning a value to a variable

Available in:  PSQL

Syntax: 

varname = <value_expr>

Table 7.3. Assignment Statement Parameters

Argument Description
varname Name of a parameter or local variable
value_expr An expression, constant or variable whose value resolves to the same data type as <varname>


PSQL uses the equivalence symbol (=) as its assignment operator. The assignment statement assigns an SQL expression value on the right to the variable on the left of the operator. The expression can be any valid SQL expression: it may contain literals, internal variable names, arithmetic, logical and string operations, calls to internal functions or to external functions (UDFs).

Example using assignment statements: 

CREATE PROCEDURE MYPROC (
    a INTEGER,
    b INTEGER,
    name VARCHAR (30)
)
RETURNS (
    c INTEGER,
    str VARCHAR(100))
AS
BEGIN
  -- assigning a constant
  c = 0;
  str = '';
  SUSPEND;
  -- assigning expression values
  c = a + b;
  str = name || CAST(b AS VARCHAR(10));
  SUSPEND;
  -- assigning expression value
  -- built by a query
  c = (SELECT 1 FROM rdb$database);
  -- assigning a value from a context variable
  str = CURRENT_USER;
  SUSPEND;
END

See also:  DECLARE VARIABLE

DECLARE CURSOR

Table of Contents

Cursor Idiosyncrasies
Examples Using Named Cursors

Used for:  Declaring a named cursor

Available in:  PSQL

Syntax: 

DECLARE [VARIABLE] cursorname CURSOR FOR (<select>) [FOR UPDATE]

Table 7.4. DECLARE CURSOR Statement Parameters

Argument Description
cursorname Cursor name
select SELECT statement


The DECLARE CURSOR ... FOR statement binds a named cursor to the result set obtained in the SELECT statement specified in the FOR clause. In the body code, the cursor can be opened, used to walk 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.

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 checks it 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, even if they originate as input and output parameters. When the cursor is opened, the parameter is assigned the current value of the variable.

[Warning] Attention!

If the value of a PSQL variable used in the SELECT statement changes during the loop, its new value may (but not always) be used for the remaining rows. It is better to avoid having such situations arise unintentionally. If you really need this behaviour, you should test your code carefully to be certain that you know exactly how changes in the variable affect the result.

Note particularly that the behaviour may depend on the query plan, specifically on the indexes being used. No strict rules are in place for situations like this currently, but that could change in future versions of Firebird.

Examples Using Named Cursors

  1. Declaring a named cursor in the 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. 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
        -- It is important that 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:  OPEN, FETCH, CLOSE

DECLARE VARIABLE

Table of Contents

Data Type for Variables

Used for:  Declaring a local variable

Available in:  PSQL

Syntax: 

DECLARE [VARIABLE] <varname>
  {<datatype> | <domain> | TYPE OF {<domain> | COLUMN <rel.col>}
    [NOT NULL]  [CHARACTER SET <charset>] [COLLATE <collation>]
    [{DEFAULT | = } <initvalue>];

<datatype> ::=
    {SMALLINT | INTEGER | BIGINT}
  | {FLOAT | DOUBLE PRECISION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING]
    [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset]
  | BLOB [(seglen [, subtype_num])]

<initvalue> ::= <literal> | <context_var>

Table 7.5. DECLARE VARIABLE Statement Parameters

Argument Description
varname Name of the local variable
datatype An SQL data type
domain The name of an existing domain in this database
rel.col Relation name (table or view) in this database and the name of a column in that relation
precision Precision. From 1 to 18
scale Scale. From 0 to 18, it must be less than or equal to precision
size The maximum size of a string in characters
subtype_num BLOB subtype number
subtype_name BLOB subtype mnemonic name
seglen Segment size, not greater than 65,535
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
charset Character set
collation Collation sequence


The statement DECLARE [VARIABLE] is used for declaring a local variable. The keyword VARIABLE can be omitted. 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.

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 and 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:  The variable can be constrained NOT NULL if required. If a domain has been specified as the data type and already carries the NOT NULL constraint, it will not be necessary. With the other forms, including use of a domain that is nullable, the NOT NULL attribute should be included if needed.

CHARACTER SET and COLLATE clauses:  Unless specified, the character set and collation sequence of a string variable will be the database defaults. A CHARACTER SET clause can be included, if required, to handle string data that is going to be in a different character set. A valid collation sequence (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 initialized so that a starting or default value is available when they are first referenced. The DEFAULT <initvalue> form can be used, or just the assignment operator, "=": = <initvalue>. The value can be any type-compatible literal or context variable.

[Important] Important

Be sure to use this clause for any variables that are constrained to be NOT NULL and do not otherwise have a default value available.

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

BEGIN ... END

Used for:  Delineating a block of statements

Available in:  PSQL

Syntax: 

<block> ::=
BEGIN
  <compound_statement>
  [<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

IF ... THEN ... ELSE

Used for:  Conditional jumps

Available in:  PSQL

Syntax: 

IF (<condition>)
	THEN <single_statement> ; | BEGIN <compound_statement> END
[ELSE <single_statement> ; | BEGIN <compound_statement> END]

Table 7.6. IF ... THEN ... ELSE Parameters

Argument Description
condition A logical condition returning TRUE, FALSE or UNKNOWN
single_statement A single statement terminated with a semicolon
compound_statement Two or more statements wrapped in BEGIN ... END


The conditional jump statement IF ... THEN is used to branch the execution process in a PSQL module. The condition is always enclosed in parentheses. If it 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 Jumps

PSQL does not provide multi-branch jumps, such as CASE or SWITCH. Nevertheless, the CASE search 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;
...

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

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

See also:  WHILE ... DO, CASE

WHILE ... DO

Used for:  Looping constructs

Available in:  PSQL

Syntax: 

WHILE <condition> DO
<single_statement> ; | BEGIN <compound_statement> END

Table 7.7. WHILE ... DO Parameters

Argument Description
condition A logical condition returning TRUE, FALSE or UNKNOWN
single_statement A single statement terminated with a semicolon
compound_statement Two or more statements wrapped in BEGIN ... END


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

Example:  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:  IF ... THEN ... ELSE, LEAVE, EXIT, FOR SELECT, FOR EXECUTE STATEMENT

LEAVE

Used for:  Terminating a loop

Available in:  PSQL

Syntax: 

[label:]
<loop>
BEGIN
  ...
  LEAVE [label];
  ...
END
<loop_stmt> ::=
    FOR <select_stmt> INTO <var_list> DO
  | FOR EXECUTE STATEMENT ... INTO <var_list> DO
  | WHILE (<condition>)} DO

Table 7.8. LEAVE Statement Parameters

Argument Description
label Label
select_stmt SELECT statement
condition A logical condition returning TRUE, FALSE or UNKNOWN


A LEAVE statement immediately terminates the inner loop of a WHILE or FOR looping statement. The LABEL parameter is optional.

LEAVE can cause an exit from outer loops as well. Code continues to be executed from the first statement after the termination of the outer loop block.

Examples: 

  1. Leaving a loop if an error occurs on an insert into the NUMBERS table. The code continues to be executed from the line C = 0. 
    ...
WHILE (B < 10) DO
BEGIN
    INSERT INTO NUMBERS(B)
    VALUES (:B);
    B = B + 1;
    WHEN ANY DO
    BEGIN
        EXECUTE PROCEDURE LOG_ERROR (
             CURRENT_TIMESTAMP,
             'ERROR IN B LOOP');
        LEAVE;
    END
END
C = 0;
...
  2. A example using labels in the LEAVE statement. LEAVE LOOPA terminates the outer loop and LEAVE LOOPB terminates the inner loop. Note that the plain LEAVE statement would be enough to terminate the inner loop. 
    ...
STMT1 = 'SELECT NAME FROM FARMS';
LOOPA:
FOR EXECUTE STATEMENT :STMT1
INTO :FARM DO
BEGIN
  STMT2 = 'SELECT NAME ' || 'FROM ANIMALS WHERE FARM = ''';
  LOOPB:
  FOR EXECUTE STATEMENT :STMT2 || :FARM || ''''
  INTO :ANIMAL DO
  BEGIN
    IF (ANIMAL = 'FLUFFY') THEN
      LEAVE LOOPB;
    ELSE IF (ANIMAL = FARM) THEN
      LEAVE LOOPA;
    ELSE
      SUSPEND;
  END
END
...

See also:  EXIT

EXIT

Used for:  Terminating module execution

Available in:  PSQL

Syntax: 

EXIT;

The EXIT statement causes execution of the procedure or trigger to jump to the final END statement from any point in the code, thus terminating the program.

Example: Using the EXIT statement in a selectable procedure: 

CREATE PROCEDURE GEN_100
RETURNS (
  I INTEGER
)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END

See also:  LEAVE, SUSPEND

SUSPEND

Used for:  Passing output to the buffer and suspending execution while waiting for caller to fetch it

Available in:  PSQL

Syntax: 

SUSPEND;

The SUSPEND statement is used in a selectable stored procedure to pass the values of output parameters to a buffer and suspend execution. Execution remains suspended until the calling application fetches the contents of the buffer. Execution resumes from the statement directly after the SUSPEND statement. In practice, this is likely to be a new iteration of a looping process.

[Important] Important Notes

  1. Applications using interfaces that wrap the API perform the fetches from selectable procedures transparently.

  2. When a SUSPEND statement is executed in an executable stored procedure, it is the same as executing the EXIT statement, resulting in immediate termination of the procedure.

  3. SUSPEND “breaks” the atomicity of the block in which it is located. If an error occurs in a selectable procedure, statements executed after the final SUSPEND statement will be rolled back. Statements that executed before the final SUSPEND statement will not be rolled back unless the transaction is rolled back.

Example: Using the SUSPEND statement in a selectable procedure: 

CREATE PROCEDURE GEN_100
RETURNS (
  I INTEGER
)
AS
BEGIN
  I = 1;
  WHILE (1=1) DO
  BEGIN
    SUSPEND;
    IF (I=100) THEN
      EXIT;
    I = I + 1;
  END
END

See also:  EXIT

EXECUTE STATEMENT

Table of Contents

Parameterized Statements
WITH {AUTONOMOUS | COMMON} TRANSACTION
WITH CALLER PRIVILEGES
ON EXTERNAL [DATA SOURCE]
AS USER, PASSWORD and ROLE
Caveats with EXECUTE STATEMENT

Used for:  Executing dynamically created SQL statements

Available in:  PSQL

Syntax: 

<execute_statement> ::= EXECUTE STATEMENT <argument>
  [<option> …]
  [INTO <variables>]

<argument> ::= paramless_stmt
            | (paramless_stmt)
            | (<stmt_with_params>) (<param_values>)

<param_values> ::= <named_values> | <positional_values>

<named_values> ::= paramname := value_expr
   [, paramname := value_expr ...]

<positional_values> ::= value_expr [, value_expr ...]

<option> ::= WITH {AUTONOMOUS | COMMON} TRANSACTION
           | WITH CALLER PRIVILEGES
           | AS USER user
           | PASSWORD password
           | ROLE role
           | ON EXTERNAL [DATA SOURCE] <connect_string>

<connect_string> ::= [<hostspec>] {filepath | db_alias}

<hostspec> ::= <tcpip_hostspec> | <NamedPipes_hostspec>

<tcpip_hostspec> ::= hostname:

<NamePipes_hostspec> ::= \\hostname\

<variables> ::= [:]varname [, [:]varname ...]

Table 7.9. EXECUTE STATEMENT Statement Parameters

Argument Description
paramless_stmt Literal string or variable containing a non-parameterized SQL query
stmt_with_params Literal string or variable containing a parameterized SQL query
paramname SQL query parameter name
value_expr SQL expression resolving to a value
user User name. It can be a string, CURRENT_USER or a string variable
password Password. It can be a string or a string variable
role Role. It can be a string, CURRENT_ROLE or a string variable
connection_string Connection string. It can be a string or a string variable
filepath Path to the primary database file
db_alias Database alias
hostname Computer name or IP address
varname Variable


The statement EXECUTE STATEMENT takes a string parameter and executes it as if it were a DSQL statement. If the statement returns data, it can be passed to local variables by way of an INTO clause.

Parameterized Statements

You can use parameters—either named or positional— in the DSQL statement string. Each parameter must be assigned a value.

Special Rules for Parameterized Statements

  1. Named and positional parameters cannot be mixed in one query

  2. If the statement has parameters, they must be enclosed in parentheses when EXECUTE STATEMENT is called, regardless of whether they come directly as strings, as variable names or as expressions

  3. Each named parameter must be prefixed by a colon (:) in the statement string itself, but not when the parameter is assigned a value

  4. Positional parameters must be assigned their values in the same order as they appear in the query text

  5. The assignment operator for parameters is the special operator ":=", similar to the assignment operator in Pascal

  6. Each named parameter can be used in the statement more than once, but its value must be assigned only once Editor note: I suspect something got lost in translation here. Needs confirmation or correction.

  7. With positional parameters, the number of assigned values must match the number of parameter placeholders (question marks) in the statement exactly

Examples:  With named parameters: 

...
DECLARE license_num VARCHAR(15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license
   FROM cars
   WHERE driver = :driver AND location = :loc';
BEGIN
  ...
  SELECT connstr
  FROM databases
  WHERE cust_id = :id
  INTO connect_string;
  ...
  FOR
    SELECT id
    FROM drivers
    INTO current_driver
  DO
  BEGIN
    FOR
      SELECT location
      FROM driver_locations
      WHERE driver_id = :current_driver
      INTO current_location
    DO
    BEGIN
      ...
      EXECUTE STATEMENT (stmt)
        (driver := current_driver,
         loc := current_location)
      ON EXTERNAL connect_string
      INTO license_num;
      ...

The same code with positional parameters: 

DECLARE license_num VARCHAR (15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license
   FROM cars
   WHERE driver = ? AND location = ?';
BEGIN
  ...
  SELECT connstr
  FROM databases
  WHERE cust_id = :id
  into connect_string;
  ...
  FOR SELECT id
      FROM drivers
      INTO current_driver
  DO
  BEGIN
    FOR
      SELECT location
      FROM driver_locations
      WHERE driver_id = :current_driver
      INTO current_location
    DO
    BEGIN
      ...
      EXECUTE STATEMENT (stmt)
        (current_driver, current_location)
      ON EXTERNAL connect_string
      INTO license_num;
      ...

WITH {AUTONOMOUS | COMMON} TRANSACTION

Traditionally, the executed SQL statement always ran within the current transaction, and this is still the default. WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction. It will be committed if the statement runs to completion without errors and rolled back otherwise. WITH COMMON TRANSACTION uses the current transaction if possible.

If the statement must run in a separate connection, an already started transaction within that connection is used, if available. Otherwise, a new transaction is started with the same parameters as the current transaction. Any new transactions started under the “COMMON” regime are committed or rolled back with the current transaction.

WITH CALLER PRIVILEGES

By default, the SQL statement is executed with the privileges of the current user. Specifying WITH CALLER PRIVILEGES adds to this the privileges of the calling procedure or trigger, just as if the statement were executed directly by the routine. WITH WITH CALLER PRIVILEGES has no effect if the ON EXTERNAL clause is also present.

ON EXTERNAL [DATA SOURCE]

With ON EXTERNAL [DATA SOURCE], the SQL statement is executed in a separate connection to the same or another database, possibly even on another server. If the connect string is NULL or '' (empty string), the entire ON EXTERNAL [DATA SOURCE] clause is considered absent and the statement is executed against the current database.

Connection Pooling
  • External connections made by statements WITH COMMON TRANSACTION (the default) will remain open until the current transaction ends. They can be reused by subsequent calls to EXECUTE STATEMENT, but only if the connect string is exactly the same, including case
  • External connections made by statements WITH AUTONOMOUS TRANSACTION are closed as soon as the statement has been executed
  • Notice that statements WITH AUTONOMOUS TRANSACTION can and will re-use connections that were opened earlier by statements WITH COMMON TRANSACTION. If this happens, the reused connection will be left open after the statement has been executed. (It must be, because it has at least one un-committed transaction!)
Transaction Pooling
  • If WITH COMMON TRANSACTION is in effect, transactions will be reused as much as possible. They will be committed or rolled back together with the current transaction
  • If WITH AUTONOMOUS TRANSACTION is specified, a fresh transaction will always be started for the statement. This transaction will be committed or rolled back immediately after the statement's execution
Exception Handling

Exception handling: When ON EXTERNAL is used, the extra connection is always made via a so-called external provider, even if the connection is to the current database. One of the consequences is that exceptions cannot be caught in the usual way. Every exception caused by the statement is wrapped in either an eds_connection or an eds_statement error. In order to catch them in your PSQL code, you have to use WHEN GDSCODE eds_connection, WHEN GDSCODE eds_statement or WHEN ANY.

[Note] Note

Without ON EXTERNAL, exceptions are caught in the usual way, even if an extra connection is made to the current database.

Miscellaneous Notes
  • The character set used for the external connection is the same as that for the current connection
  • Two-phase commits are not supported

AS USER, PASSWORD and ROLE

The optional AS USER, PASSWORD and ROLE clauses allow specificaton of which user will execute the SQL statement and with which role. The method of user log-in and whether a separate connection is open depend on the presence and values of the ON EXTERNAL [DATA SOURCE], AS USER, PASSWORD and ROLE clauses:

  • If ON EXTERNAL is present, a new connection is always opened, and:
    • If at least one of AS USER, PASSWORD and ROLE is present, native authentication is attempted with the given parameter values (locally or remotely, depending on the connect string). No defaults are used for missing parameters
    • If all three are absent and the connect string contains no hostname, then the new connection is established on the local host with the same user and role as the current connection. The term 'local' means 'on the same machine as the server' here. This is not necessarily the location of the client
    • If all three are absent and the connect string contains a hostname, then trusted authentication is attempted on the remote host (again, 'remote' from the perspective of the server). If this succeeds, the remote operating system will provide the user name (usually the operating system account under which the Firebird process runs)
  • If ON EXTERNAL is absent:
    • If at least one of AS USER, PASSWORD and ROLE is present, a new connection to the current database is opened with the suppled parameter values. No defaults are used for missing parameters
    • If all three are absent, the statement is executed within the current connection

[Note] Notice

If a parameter value is NULL or '' (empty string), the entire parameter is considered absent. Additionally, AS USER is considered absent if its value is equal to CURRENT_USER, and ROLE if it is the same as CURRENT_ROLE.

Caveats with EXECUTE STATEMENT

  1. There is no way to validate the syntax of the enclosed statement
  2. There are no dependency checks to discover whether tables or columns have been dropped
  3. Even though the performance in loops has been significantly improved in Firebird 2.5, execution is still considerably slower than when the same statements are launched directly
  4. Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error

All in all, this feature is meant to be used very cautiously and you should always take the caveats into account. If you can achieve the same result with PSQL and/or DSQL, it will almost always be preferable.

See also:  FOR EXECUTE STATEMENT

FOR SELECT

Table of Contents

The Undeclared Cursor

Used for:  Looping row-by-row through a selected result set

Available in:  PSQL

Syntax: 

FOR <select_stmt> [AS CURSOR cursorname]
DO {<single_statement> | BEGIN <compound_statement> END}

Table 7.10. FOR SELECT Statement Parameters

Argument Description
select_stmt SELECT statement
cursorname Cursor name. It must be unique among cursor names in the PSQL module (stored procedure, trigger or PSQL block)
single_statement A single statement, terminated with a colon, that performs all the processing for this FOR loop
compound_statement A block of statements wrapped in BEGIN...END, that performs all the processing for this FOR loop


A FOR SELECT statement

  • retrieves each row sequentially from the result set and executes the statement or block of statements on the row. In each iteration of the loop, the field values of the current row are copied into pre-declared variables.

    Including the AS CURSOR clause enables positioned deletes and updates to be performed—see notes below

  • can embed other FOR SELECT statements
  • can carry named parameters that must be previously declared in the DECLARE VARIABLE statement or exist as input or output parameters of the procedure
  • requires an INTO clause that is located at the end of the SELECT ... FROM ... specification. In each iteration of the loop, the field values in the current row are copied to the list of variables specified in the INTO clause. The loop repeats until all rows are retrieved, after which it terminates
  • can be terminated before all rows are retrieved by using a LEAVE statement

The Undeclared Cursor

The optional AS CURSOR clause surfaces the set in the FOR SELECT structure as an undeclared, named cursor that can be operated on using the WHERE CURRENT OF clause inside the statement or block following the DO command, in order to delete or update the current row before execution moves to the next iteration.

Other points to take into account regarding undeclared cursors:

  1. the OPEN, FETCH and CLOSE statements cannot be applied to a cursor surfaced by the AS CURSOR clause
  2. the cursor name argument associated with an AS CURSOR clause must not clash with any names created by DECLARE VARIABLE or DECLARE CURSOR statements at the top of the body code, nor with any other cursors surfaced by an AS CURSOR clause
  3. The optional FOR UPDATE clause in the SELECT statement is not required for a positioned update

Examples using FOR SELECT: 

  1. A simple loop through query results: 
    CREATE PROCEDURE SHOWNUMS
RETURNS (
  AA INTEGER,
  BB INTEGER,
  SM INTEGER,
  DF INTEGER)
AS
BEGIN
  FOR SELECT DISTINCT A, B
      FROM NUMBERS
    ORDER BY A, B
    INTO AA, BB
  DO
  BEGIN
    SM = AA + BB;
    DF = AA - BB;
    SUSPEND;
  END
END
  2. Nested FOR SELECT loop: 
    CREATE PROCEDURE RELFIELDS
RETURNS (
  RELATION CHAR(32),
  POS INTEGER,
  FIELD CHAR(32))
AS
BEGIN
  FOR SELECT RDB$RELATION_NAME
      FROM RDB$RELATIONS
      ORDER BY 1
      INTO :RELATION
  DO
  BEGIN
    FOR SELECT
          RDB$FIELD_POSITION + 1,
          RDB$FIELD_NAME
        FROM RDB$RELATION_FIELDS
        WHERE
          RDB$RELATION_NAME = :RELATION
        ORDER BY RDB$FIELD_POSITION
        INTO :POS, :FIELD
    DO
    BEGIN
      IF (POS = 2) THEN
        RELATION = ' "';

      SUSPEND;
    END
  END
END
  3. Using the AS CURSOR clause to surface a cursor for the positioned delete of a record: 
    CREATE PROCEDURE DELTOWN (
  TOWNTODELETE VARCHAR(24))
RETURNS (
  TOWN VARCHAR(24),
  POP INTEGER)
AS
BEGIN
  FOR SELECT TOWN, POP
      FROM TOWNS
      INTO :TOWN, :POP AS CURSOR TCUR
  DO
  BEGIN
    IF (:TOWN = :TOWNTODELETE) THEN
      -- Positional delete
      DELETE FROM TOWNS
      WHERE CURRENT OF TCUR;
    ELSE
      SUSPEND;
  END
END

See also:  DECLARE CURSOR, LEAVE, SELECT, UPDATE, DELETE

FOR EXECUTE STATEMENT

Used for:  Executing dynamically created SQL statements that return a row set

Available in:  PSQL

Syntax: 

FOR <execute_statement> DO {<single_statement> | BEGIN <compound_statement> END}

Table 7.11. FOR EXECUTE STATEMENT Statement Parameters

Argument Description
execute_stmt An EXECUTE STATEMENT string
single_statement A single statement, terminated with a colon, that performs all the processing for this FOR loop
compound_statement A block of statements wrapped in BEGIN...END, that performs all the processing for this FOR loop


The statement FOR EXECUTE STATEMENT is used, in a manner analogous to FOR SELECT, to loop through the result set of a dynamically executed query that returns multiple rows.

Example:  Executing a dynamically constructed SELECT query that returns a data set: 

CREATE PROCEDURE DynamicSampleThree (
   Q_FIELD_NAME VARCHAR(100),
   Q_TABLE_NAME VARCHAR(100)
) RETURNS(
  LINE VARCHAR(32000)
)
AS
  DECLARE VARIABLE P_ONE_LINE VARCHAR(100);
BEGIN
  LINE = '';
  FOR
    EXECUTE STATEMENT
      'SELECT T1.' || :Q_FIELD_NAME ||
      ' FROM ' || :Q_TABLE_NAME || ' T1 '
    INTO :P_ONE_LINE
  DO
    IF (:P_ONE_LINE IS NOT NULL) THEN
      LINE = :LINE || :P_ONE_LINE || ' ';
  SUSPEND;
END

See also:  EXECUTE STATEMENT

OPEN

Used for:  Opening a declared cursor

Available in:  PSQL

Syntax: 

OPEN cursorname;

Table 7.12. OPEN Statement Parameter

Argument Description
cursorname Cursor name. A cursor with this name must be previously declared with a DECLARE CURSOR statement


An OPEN statement opens a previously declared cursor, executes the SELECT statement declared for it and makes the first record the result data set ready to fetch. OPEN can be applied only to cursors previously declared in a DECLARE VARIABLE statement.

[Note] Note

If the SELECT statement declared for the cursor has parameters, they must be declared as local variables or exist as input or output parameters before the cursor is declared. When the cursor is opened, the parameter is assigned the current value of the variable.

Examples: 

  1. Using the OPEN statement: 
    SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(31)
)
AS
  DECLARE C CURSOR FOR (
    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

SET TERM ;^
  2. A collection of scripts for creating views using a PSQL block with 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;
-- named cursor
DECLARE VARIABLE CUR_R      CURSOR FOR (
    SELECT
        RDB$RELATION_NAME,
        RDB$VIEW_SOURCE
    FROM
        RDB$RELATIONS
    WHERE
        RDB$VIEW_SOURCE IS NOT NULL);
-- named cursor with local variable
DECLARE CUR_F      CURSOR FOR (
    SELECT
        RDB$FIELD_NAME
    FROM
        RDB$RELATION_FIELDS
    WHERE
          -- Important! The variable shall 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
        -- variable value of RELATION initialized 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:  DECLARE CURSOR, FETCH, CLOSE

FETCH

Used for:  Fetching successive records from a data set retrieved by a cursor

Available in:  PSQL

Syntax: 

FETCH cursorname INTO [:]varname [, [:]varname ...];

Table 7.13. FETCH Statement Parameters

Argument Description
cursorname Cursor name. A cursor with this name must be previously declared with a DECLARE CURSOR statement and opened by an OPEN statement.
varname Variable name


A FETCH statement fetches the first and successive rows from the result set of the cursor and assigns the column values to PSQL variables. The FETCH statement can be used only with a cursor declared with the DECLARE CURSOR statement.

The INTO clause gets data from the current row of the cursor and loads them into PSQL variables.

For checking whether all of the the data set rows have been fetched, the context variable ROW_COUNT returns the number of rows fetched by the statement. It is positive until all rows have been checked. A ROW_COUNT of 1 indicates that the next fetch will be the last.

Example:  Using the FETCH statement: 

SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(31)
)
AS
  DECLARE C CURSOR FOR (
    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

SET TERM ;^

See also:  DECLARE CURSOR, OPEN, CLOSE

CLOSE

Used for:  Closing a declared cursor

Available in:  PSQL

Syntax: 

CLOSE cursorname;

Table 7.14. CLOSE Statement Parameter

Argument Description
cursorname Cursor name. A cursor with this name must be previously declared with a DECLARE CURSOR statement and opened by an OPEN statement


A CLOSE statement closes an open cursor. Any cursors that are still open will be automatically closed after the module code completes execution. Only a cursor that was declared with DECLARE CURSOR can be closed with a CLOSE statement.

Example:  Using the CLOSE statement: 

SET TERM ^;

CREATE OR ALTER PROCEDURE GET_RELATIONS_NAMES
RETURNS (
  RNAME CHAR(31)
)
AS
  DECLARE C CURSOR FOR (
    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS);
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :RNAME;
    IF (ROW_COUNT = 0) THEN
      LEAVE;
    SUSPEND;
  END
  CLOSE C;
END^

See also:  DECLARE CURSOR, OPEN, FETCH

IN AUTONOMOUS TRANSACTION

Used for:  Executing a statement or a block of statements in an autonomous transaction

Available in:  PSQL

Syntax: 

IN AUTONOMOUS TRANSACTION DO <compound_statement>

Table 7.15. IN AUTONOMOUS TRANSACTION Statement Parameter

Argument Description
compound_statement A statement or a block of statements


An 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. It might be needed 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 made changes being cancelled. If the code executes successfully, the autonomous transaction will be committed.

Example:  Using an autonomous transaction in a trigger for the database ON CONNECT event, in order 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 (CURRENT_USER IN (SELECT
                           USERNAME
                       FROM
                           BLOCKED_USERS)) 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:  Transsaction Control

POST_EVENT

Used for:  Notifying listening clients about database events in a module

Available in:  PSQL

Syntax: 

POST_EVENT event_name;

Table 7.16. 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 are signalling their interest in the event.

The event name can be some sort of code or a short message: the choice is open as it is just a string up to 127 bytes.

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

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

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

Prev: TriggersFirebird 2.5 Language ReferenceUp: Procedural SQL (PSQL) StatementsNext: Trapping and Handling Errors
Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Procedural SQL (PSQL) Statements → Writing the Body Code