Loops

Valentina SQL offers the following kinds of loops:

• LOOP
• WHILE
• REPEAT … UNTIL

And you can use keywords ITERATE and LEAVE to control the loop iteration.

loop_nstd_clause
    :   LOOP
            blocked_statement_list
        END LOOP
 
repeat_nstd_clause
    :   REPEAT
            blocked_statement_list
        UNTIL search_condition
        END REPEAT
 
while_nstd_clause
    :   WHILE search_condition DO
            blocked_statement_list
        END WHILE
 
 
iterate_nstd_clause
    :   ITERATE 
 
 
leave_nstd_clause
    :   LEAVE

LOOP implements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements. The statements within the loop are repeated until the loop is exited; usually, this is accomplished with a LEAVE statement.

The statement list within a REPEAT statement is repeated until the search_condition is true. Thus, a REPEAT always enters the loop at least once. statement_list consists of one or more statements.

Example:

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
    DECLARE v1 INT DEFAULT 0;
 
    REPEAT 
       SET v1 = v1 + 1; 
       UNTIL v1 > p1 
    END REPEAT
END

The statement list within a WHILE statement is repeated as long as the search_condition is TRUE. statement_list consists of one or more statements.

Example:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
 
  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE
END;

ITERATE can appear only within loops (LOOP, REPEAT, WHILE) statements. ITERATE means “do the loop again“. Analog of C/C++ 'continue'.

Example:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN 
        ITERATE
    ELSE
        LEAVE
    END IF
  END LOOP
  SET @x = p1;
END;

LEAVE can appear only within loops (LOOP, REPEAT, WHILE) statements. Analog of C/C++ 'break'.