User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

Repeat the execution of a statement list once for each row in a cursor.

Syntax

     [ statement-label : ]
          . . .FOR for-loop-name AS cursor-name
              . . . CURSOR FOR statement
              . . .[ FOR UPDATE | FOR READ ONLY ]
              . . . DO statement-list
          . . . END FOR [ statement-label ]

Usage

Procedures, triggers, and batches only.

Permissions

None.

Side effects

None.

See also

• DECLARE CURSOR statement
• FETCH statement
• LEAVE statement
• LOOP statement

Description

The FOR statement is a control statement that allows you to 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 will be the data type of the expressions in the select list. The names will be the select list item aliases where they exist; otherwise, they will be the name 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 will be 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.

Example

     FOR names AS curs CURSOR FOR
     SELECT emp_lname
     FROM employee
     DO
         CALL search_for_name( emp_lname );
     END FOR;