FOR Statement
Repeats the execution of a statement list once for each row in a cursor.
[ <statement-label>: ] FOR <for-loop-name> AS <cursor-name> [ cursor-type ] CURSOR { FOR <statement> ... [ { FOR { UPDATE cursor-concurrency | FOR READ ONLY } ] | USING variable-name } DO <statement-list> END FOR [ <statement-label> ] cursor-type - (back to Syntax) NO SCROLL | DYNAMIC SCROLL | SCROLL | INSENSITIVE | SENSITIVE cursor-concurrency - (back to Syntax) BY { VALUES | TIMESTAMP | LOCK } variable-name - (back to Syntax) <identifier>
- a cursor declared NO SCROLL is restricted to moving forward through the result set using FETCH NEXT and FETCH RELATIVE 0 seek operations. As rows cannot be returned to once the cursor leaves the row, there are no sensitivity restrictions on the cursor. When a NO SCROLL cursor is requested, the database server supplies the most efficient kind of cursor, which is an asensitive cursor.
- DYNAMIC SCROLL is the default cursor type. DYNAMIC SCROLL cursors can use all formats of the FETCH statement. When a DYNAMIC SCROLL cursor is requested, the database server supplies an asensitive cursor. When using cursors there is always a trade-off between efficiency and consistency. Asensitive cursors provide efficient performance at the expense of consistency.
- a cursor declared SCROLL can use all formats of the FETCH statement. When a SCROLL cursor is requested, the database server supplies a value-sensitive cursor. The database server must execute value-sensitive cursors in such a way that result set membership is guaranteed. DYNAMIC SCROLL cursors are more efficient and should be used unless the consistent behavior of SCROLL cursors is required.
- a cursor declared INSENSITIVE has its values and membership fixed over its lifetime. The result set of the SELECT statement is materialized when the cursor is opened. FETCHING from an INSENSITIVE cursor does not see the effect of any other INSERT, UPDATE, MERGE, PUT, or DELETE statement from any connection, including the connection that opened the cursor.
- a cursor declared SENSITIVE is sensitive to changes to membership or values of the result set.
- this code fragment illustrates the use of the
FOR loop:
FOR names AS curs CURSOR FOR SELECT Surname FROM Employees DO CALL search_for_name( Surname ); END FOR;
FOR is a control statement that lets you execute a list of SQL statements once for each row in a cursor.
The FOR statement is equivalent to a compound statement with a DECLARE for the cursor and a DECLARE of a variable for each column in the result set of the cursor, followed by a loop that fetches one row from the cursor into the local variables and executes <statement-list> once for each row in the cursor.
The name and data type of the local variables that are declared are derived from the <statement> used in the cursor. With a SELECT statement, the data type is the data type of the expressions in the select list. The names are the select list item aliases where they exist; otherwise, they are the names of the columns. Any select list item that is not a simple column reference must have an alias. With a CALL statement, the names and data types are taken from the RESULT clause in the procedure definition.
The LEAVE statement can be used to resume execution at the first statement after the END FOR. If the ending <statement-label> is specified, it must match the beginning <statement-label>.
- SQL—ISO/ANSI SQL compliant.
- SAP Database products—Not supported in SAP ASE.