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

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. Execution is considerably slower than when the same statements are executed directly as PSQL code

  4. Return values are strictly checked for data type 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 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

[fblangref50-psql-forexec]

FOR SELECT

Loops row-by-row through a query result set

Syntax
[label:]
FOR <select_stmt> [AS CURSOR cursor_name]
  DO <compound_statement>
Table 1. FOR SELECT Statement Parameters
Argument Description

label

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

select_stmt

SELECT statement

cursor_name

Cursor name.It must be unique among cursor names in the PSQL module (stored procedure, stored function, trigger or PSQL block)

compound_statement

A single statement, or statements wrapped in BEGIN…​END, that performs all the processing for this FOR loop

The FOR SELECT statement

  • retrieves each row sequentially from the result set, and executes the statement or block of statements for each 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 contain 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 at the end of the SELECT …​ FROM …​ specification if AS CURSOR is absentIn each iteration of the loop, the field values of 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 BREAK, LEAVE or EXIT statement