USING clause
USING clause specifies one or more variables separated by comas which values will provide the search criteria for the query performed. This clause should be included into the FOREACH statement, if the search criteria are provided by the user - that is if the processed cursor is associated with a prepared statement containing placeholders.
|
Variable list |
A list of one or more variables separated by commas |
If the cursor is associated with a statement without placeholders (?), the USING clause must not be used. The number of variables in the variable list and their data types must correspond to the number and positions of the placeholders in the prepared statement associated with the cursor. For more information about the placeholders see the PREPARE statement.
In the example below, the cursor has been declared for a SELECT statement without the WHERE clause, the search criteria are provided by the USING clause of the FOREACH statement:
PREPARE prepared_stmt FROM "SELECT * FROM company WHERE city = ?"
DECLARE c_company CURSOR FOR prepared_stmt
PROMPT "Enter the city : " FOR city_var
FOREACH c_company USING city_var INTO comp_arr[i].*
LET i = i+1
END FOREACH
The number and data types of the variables used in the USING clause must correspond to the number and data types of the placeholders used in the prepared SELECT statement. If both USING and INTO clauses are present in a FOREACH statement, the INTO clause must follow the USING clause.