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
(B)0[m[1mwhile-statement= [m [1m [m [1mqwqqqqqqqqqqqqqqqqqqqqqqqwqq> [1;4mWHILE[m[1m qqq> predicate qqqqqk [m [1m mq> <beginning-label>: qj x [m [1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqwq> [1;4mDO[m[1m qwq> compound-use-statement qwqq> [1;4mEND[m[1m [1;4mWHILE[m[1m qwqk [m [1m x mqqqqqqqqqqqqq<qqqqqqqqqqqqqj x x [m [1m mq> [1;4mLOOP[m[1m qw> compound-use-statement qwq> [1;4mEND[m[1m [1;4mLOOP[m[1m qqj x [m [1m mqqqqqqqqqqqqq<qqqqqqqqqqqqj x [m [1mlqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m [1mmqwqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> [m [1m mq> <ending-label> qqj [m
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"