for
Loop through each result row of a select statement
Syntax
for_stmt:
[ label_name:] FOR [for_loop_var_name AS] [cursor_name CURSOR FOR]
select_stmt
DO
statement_list
END FOR [ label_name]
Description
The FOR statement provides a convenient mechanism for easily looping through an automatic fetch of each row of the specified SELECT statement and executing the statements contained in statement_list. If the FOR has a label_name then the END FOR must have the same label_name: as well.
The FOR statement translates into the following statements.
[for_loop_var_name:] begin
declare variable for select_stmt result column 1;
declare variable for select_stmt result column 2;
...
declare variable for select_stmt result column n;
declare cursor cursor_name for select_stmt;
open cursor_name;
label_name: loop
fetch cursor_name into col1_var, col2_var, ..., coln_var;
if not found then
leave label_name;
end if;
statement_list;
end loop [label];
close cursor_name;
end;
The FETCH results can be referenced in the statements in statement_list by simply specifying the column names. They do not have to be qualified by the for_loop_var name.
Any of the statements contained in statement_list can reference the result row column values through the variable with the same name. The statement_list cannot contain a COMMIT or ROLLBACK statement or an OPEN, CLOSE or FETCH that specifies cursor_name.
If a label_name, for_loop_var or cursor_name is not specified, one will automatically be generated by SQL.
Example
create procedure authors_decade(in yr smallint)begin declare decade, yrborn smallint; declare fullname char(35); declare author_found boolean default false; set decade = (yr / 10) * 10; find_author: for select full_name, yr_born from author do if yr_born between decade and decade + 9 then select full_name, yr_born; set author_found = true; end if; end for find_author; if author_found = false then select "No author found born in the decade:", decade; end if;end;
See Also