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

2  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> --+

 

2  Arguments
 

3  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  compound-use-statement

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

3  DO

   Marks the start of a control loop.
 

3  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  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  LOOP

   Marks the start of a control loop.
 

3  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.
 

2  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"