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

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

The Undeclared Cursor

The optional AS CURSOR clause surfaces the result set of 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, to delete or update the current row before execution moves to the next row.In addition, it is possible to use the cursor name as a record variable (similar to OLD and NEW in triggers), allowing access to the columns of the result set (i.e. cursor_name.columnname).

Rules for Cursor Variables

  • When accessing a cursor variable in a DML statement, the colon prefix can be added before the cursor name (i.e. :cursor_name.columnname) for disambiguation, similar to variables.

    The cursor variable can be referenced without colon prefix, but in that case, depending on the scope of the contexts in the statement, the name may resolve in the statement context instead of to the cursor (e.g. you select from a table with the same name as the cursor).

  • Cursor variables are read-only

  • In a FOR SELECT statement without an AS CURSOR clause, you must use the INTO clause.If an AS CURSOR clause is specified, the INTO clause is allowed, but optional;you can access the fields through the cursor instead.

  • Reading from a cursor variable returns the current field values.This means that an UPDATE statement (with a WHERE CURRENT OF clause) will update not only the table, but also the fields in the cursor variable for subsequent reads.Executing a DELETE statement (with a WHERE CURRENT OF clause) will set all fields in the cursor variable to NULL for subsequent reads

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 module body, 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