<< FOR EXECUTE STATEMENT ... DO | FB 2.5 Language Reference | IN AUTONOMOUS TRANSACTION >>
<< FOR EXECUTE STATEMENT ... DO | FB 2.1 Language Reference | LEAVE >>
<< FOR EXECUTE STATEMENT ... DO | FB 2.0 Language Reference | LEAVE >>

FOR SELECT INTO ... DO

Available in: PSQL

Description

Executes a SELECT statement and retrieves the result set. In each iteration of the loop, the field values of the current row are copied into local variables. Adding an AS CURSOR clause enables positioned deletion or update of the current row. FOR SELECT statements may be nested.

Syntax

 FOR <select-stmt>
   INTO <var> [, <var> ...]
   [AS CURSOR name]
 DO
   <psql-stmt>

 <select-stmt>   ::= A valid SELECT statement.
 <var>           ::= A PSQL variable name, optionally preceded by ":"
 <psql-stmt>     ::= A single statement or a block of PSQL code.

• The SELECT statement may contain named SQL parameters, like in select name || :sfx from names where number = :num. Each parameter must be a PSQL variable that has been declared previously (this includes any in/out params of the PSQL module).
• Caution! If the value of a PSQL variable that is used in the SELECT statement changes during execution of the loop, the statement may (but will not always) be re-evaluated for the remaining rows. In general, this situation should be avoided. If you really need this behaviour, test your code thoroughly and make sure you know how variable changes affect the outcome. Also be advised that the behaviour may depend on the query plan, in particular the use of indices. As it is currently not strictly defined, it may change in some future version of Firebird.

Examples

 create procedure shownums 
   returns (aa int, bb int, sm int, df int)
 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

 create procedure relfields
   returns (relation char(32), pos int, field char(32))
 as
 begin
   for select rdb$relation_name from rdb$relations
     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 = ' "'; -- for nicer output
       suspend;
     end
   end
 end

back to top of page

AS CURSOR clause

Available in: PSQL

Added in: IB

Description

The optional AS CURSOR clause creates a named cursor that can be referenced (after WHERE CURRENT OF) within the FOR SELECT loop in order to update or delete the current row. This feature was already added in InterBase, but not mentioned in the Language Reference.

Example

 create procedure deltown (towntodelete varchar(24))
   returns (town varchar(24), pop int)
 as
 begin
   for select town, pop from towns into :town, :pop as cursor tcur do
   begin
   if (town = towntodelete)
     then delete from towns where current of tcur;
     else suspend;
   end
 end

Notes:

• A FOR UPDATE clause is allowed in the SELECT statement, but not required for a positioned update or delete to succeed.
• Make sure that cursor names defined here do not clash with any names created earlier on in DECLARE CURSOR statements.
• AS CURSOR is not supported in FOR EXECUTE STATEMENT loops, even if the statement to execute is a suitable SELECT query.

back to top of page
<< FOR EXECUTE STATEMENT ... DO | FB 2.5 Language Reference | IN AUTONOMOUS TRANSACTION >>
<< FOR EXECUTE STATEMENT ... DO | FB 2.1 Language Reference | LEAVE >>
<< FOR EXECUTE STATEMENT ... DO | FB 2.0 Language Reference | LEAVE >>