Using FOR SELECT … DO Statements
From InterBase
Go Up to The Procedure Body
To retrieve multiple rows in a procedure, use the FOR SELECT … DO
statement. The syntax of FOR SELECT
is:
FOR
<select_expr>
DO
<compound_statement>;
FOR SELECT
differs from a standard SELECT
as follows:
- It is a loop statement that retrieves the row specified in the <select_expr> and performs the statement or block following DO for each row retrieved.
- The
INTO
clause in the
<select_expr> is required and must come last. This syntax allows FOR … SELECT
to use the SQL UNION
clause, if needed.
For example, the following statement from a procedure selects department numbers into the local variable, RDNO
, which is then used as an input parameter to the DEPT_BUDGET
procedure:
FOR SELECT DEPT_NO
FROM DEPARTMENT
WHERE HEAD_DEPT = :DNO
INTO :RDNO
DO
BEGIN
EXECUTE PROCEDURE DEPT_BUDGET :RDNO RETURNS :SUMB;
TOT = TOT + SUMB;
END
… ;