WHILE statement (Repeat statements)

Function

The WHILE statement executes repeatedly a set of SQL statements.

Format

[starting-label:]
WHILE search-condition DO
 SQL-procedure-statement;[SQL-procedure-statement;]...
END [WHILE] [termination-label]

Operands

Specifies the starting label for the WHILE statement.

Specifies the condition under which the SQL procedure statements are to be executed repeatedly.

Specifies the SQL procedure statements that are to be executed repeatedly.

Specifies the end of the WHILE statement. Specify a statement label as a termination label.

The WHILE operand has the same effect whether or it is specified.

Common rules

  1. When a termination label is being specified, care must be taken that it is a statement label that has the same name as the starting label.
  2. The scope of a statement label is from the beginning to the end of the WHILE statement. A statement label identical to the statement label of other statements contained in the WHILE statement or identical to a loop variable name cannot be specified. However, if there is a handler declaration in the SQL procedure statement, the scope of the statement label excludes the handler declaration. The following shows examples in which statement labels of the same name can and cannot be specified:

    AAA: WHILE X < 100 DO
     BEGIN  ...........................................1
       DECLARE CN1 CONDITION FOR SQLCODE VALUE -800;
       DECLARE EXIT HANDLER FOR CN1
       AAA: BEGIN  .........................................2
           :
       END AAA;
       AAA: BEGIN  .........................................3
         DECLARE CN2 CONDITION FOR SQLCODE VALUE -800;
         DECLARE EXIT HANDLER FOR CN2
           :
       END AAA;
       SET X=X+1;
     END
    END WHILE AAA

    Explanation
    Although the statement label in line 2 is identical to the label in line 1, it can be specified because it is in a handler declaration.
    The statement label in line 3 is identical to that in line 1; it cannot be specified because it is not in a handler declaration.
  3. HiRDB evaluates the search condition, executes the SQL statements if the result is TRUE, and executes the SQL statements repeatedly until the search condition becomes FALSE or indefinite.
  4. SQL procedure statements are executed in the order in which they are specified. If an error occurs during the execution of an SQL procedure statement, any subsequent SQL procedure statements will not be executed, and the execution of the WHILE statement is also terminated.
  5. A subquery cannot be specified in a search condition.

Note

WHILE statements can be specified in an SQL routine.