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.
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
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: