Allows the repetitive execution of one or more SQL statements in
    a compound statement based on the truth value of a predicate.

1  –  Environment

    You can use the WHILE control statement in a compound statement:

    o  In interactive SQL

    o  Embedded in host language programs to be precompiled

    o  As part of a procedure in an SQL module

    o  In dynamic SQL as a statement to be dynamically executed

2  –  Format

  while-statement=

  -+-----------------------+--> WHILE ---> predicate -----+
   +-> <beginning-label>: -+                              |
  +-------------------------------------------------------+
  +-+-> DO -+-> compound-use-statement -+--> END WHILE -+-+
    |       +-------------<-------------+               | |
    +-> LOOP -+> compound-use-statement -+-> END LOOP --+ |
              +-------------<------------+                |
  +-------------------------<-----------------------------+
  +-+--------------------+------------------------------------>
    +-> <ending-label> --+

3  –  Arguments

3.1  –  beginning-label:

    Assigns a name to a control loop. A beginning label used with the
    LEAVE statement lets you perform a controlled exit from the WHILE
    loop. If you include an ending label, it must be identical to its
    corresponding beginning label. A beginning label must be unique
    within the procedure containing the label.

3.2  –  compound-use-statement

    Identifies the SQL statements allowed in a compound statement
    block. See the Compound Statement for the list of valid
    statements.

3.3  –  DO

    Marks the start of a control loop.

3.4  –  END LOOP ending-label

    Marks the end of a LOOP control loop. If you choose to
    include the optional ending label, it must match exactly its
    corresponding beginning label. An ending label must be unique
    within the procedure in which the label is contained.

    The optional ending-label argument makes multistatement
    procedures easier to read, especially in very complex
    multistatement procedure blocks.

3.5  –  END WHILE ending-label

    Marks the end of a DO control loop. If you choose to include the
    optional ending label, it must match exactly its corresponding
    beginning label. An ending label must be unique within the
    procedure in which the label is contained.

    The optional ending-label argument makes multistatement
    procedures easier to read, especially in very complex
    multistatement procedure blocks.

3.6  –  LOOP

    Marks the start of a control loop.

3.7  –  WHILE predicate

    Specifies a search condition that controls how many times SQL can
    execute a compound statement.

    SQL evaluates the WHILE search condition. If it evaluates to
    TRUE, SQL executes the associated sequence of SQL statements. If
    SQL does not encounter an error exception, control returns to the
    WHILE clause at the top of the loop for subsequent evaluation.
    Each time the search condition evaluates to TRUE, the WHILE-
    DO statement executes the SQL statements embedded within its
    DO . . . END WHILE block. If the search condition evaluates to
    FALSE or UNKNOWN, SQL bypasses the DO . . . END WHILE block and
    passes control to the next statement.

4  –  Examples

    Example 1: Using the While Statement to Count Substrings

    SQL> DECLARE :SUB_STR CHAR;
    SQL> DECLARE :SRC_STR CHAR(50);
    SQL> BEGIN
    cont>   SET :SUB_STR='l';
    cont>   SET :SRC_STR='The rain in Spain falls mainly on the plain';
    cont> END;
    SQL> SET FLAGS 'TRACE';
    SQL> BEGIN
    cont>-- This procedure counts the occurrence of substrings
    cont>   DECLARE :STR_COUNT INTEGER=0;
    cont>   DECLARE :CUR_POS INTEGER = POSITION (:SUB_STR IN :SRC_STR);
    cont>   WHILE :CUR_POS >0 DO
    cont>      SET :STR_COUNT=:STR_COUNT + 1;
    cont>         SET :CUR_POS = POSITION (:SUB_STR IN :SRC_STR FROM :CUR_POS + 1);
    cont>   END WHILE;
    cont>     TRACE 'FOUND ', :STR_COUNT, ' OCCURRENCES OF "', :SUB_STR, '"';
    cont> END;
    ~Xt: Found 4          occurrences of "l"