FOREACH Statement

Iterates over each row in a table or table expression, assigning the column values to the specified variables and executing the provided statements for each row.

The loop may be terminated early by using the BREAK statement. The current iteration of the loop can be abandoned and the next iteration of the loop started by using the CONTINUE statement.

Syntax

Parameters

• variable-name: variable name
  The name of a variable to receive column values from each row. The variable name must begin with an at sign (@), dollar sign ($), or colon (:). Variables are automatically declared if they do not exist; the FOREACH statement acts as a variable declaration if needed. Multiple variables can be specified, separated by commas.
• table-name: table name or expression
  The name of a table, view, or table expression to iterate over. Each row from this table will be processed in order.
• statement: statement
  The statements to execute for each row of the table.  If more than one statement is desired, the BEGIN and END keywords must be used.
  

Remarks

• Variables are assigned values in the order they appear in the variable list, corresponding to the table's columns.
• If fewer variables are specified than there are columns in the table, the extra columns are ignored.
• If more variables are specified than there are columns in the table, the extra variables are set to NULL.
• Variables are automatically declared if they do not already exist.
• The table expression can be a table name, view name, or any valid SQL table expression.

Examples

-- Iterate over a simple table with multiple columns
DROP TABLE IF EXISTS employees;
CREATE TEMP TABLE employees (id INTEGER, name TEXT, salary INTEGER);
INSERT INTO employees VALUES (1, 'Alice', 50000);
INSERT INTO employees VALUES (2, 'Bob', 60000);

FOREACH (@id, @name, @salary) IN employees BEGIN
    PRINT CONCAT(@name, ' (ID: ', @id, ') earns $', @salary);
END