Repeats the execution of a statement or statement list while a specified condition evaluates to true.
ANSI Compliance
WHILE is ANSI/ISO SQL:2011-compliant.
Required Privileges
None.
Invocation
Executable.
Stored procedures only.
Syntax
[ label_name : ] WHILE conditional_expression DO statement [...] END WHILE [ label_name ] ;
Syntax Elements
- label_name
- An optional label for the WHILE statement.
- conditional_expression
- A boolean condition used to evaluate whether a statement or statements embedded within the WHILE loop should be executed.
- statement
- A list of statements to be executed.
Usage Notes
- You can qualify WHILE with a label.
- You can specify a LEAVE or ITERATE statement within a WHILE statement.
Example: Using WHILE
WHILE hCounter > 0 DO INSERT INTO transaction (trans_num, account_num) VALUES (hCounter, hAccountNum); SET hCounter = hCounter - 1; END WHILE;
Example: Using WHILE to Set the High Number
WHILE hCounter > 0 DO SELECT highNum INTO maxNum FROM limits WHERE LIMIT_TYPE = ’HIGHNUM’; IF hCounter >= MaxNum THEN LEAVE LOOP1; END IF; INSERT INTO transaction (trans_num, account_num) VALUES (hCounter, :hAccountNum); SET hCounter = hCounter - 1; END WHILE;