Loops

This topic describes different types of loops.

The following types of loops are supported by Metakraftwerk Scripting:

• FOR
• WHILE

For more information about loops and they options see also BREAK, CONTINUE, RETURN.

FOR loop

A FOR loop allows to repeat a sequence of steps with a defined frequency or for each row in a result set.

The following types of FOR loops are supported:

• Counter-based FOR loop
• RESULTSET-based FOR loop

FOR loop: Counter-based

A counter-based FOR loop is executed the defined number of times.

Syntax

FOR counter_var IN [REVERSE] start_value TO end_value (DO | LOOP)
    statement;
    [statement; ... ]
END FOR [label] ;

See "FOR counter-based loop" in Scripting Reference for more information.

In the following example, the addition process is repeated 10 times and the result is returned:

CREATE OR REPLACE PROCEDURE for_with_count()
RETURNS INTEGER
AS
    DECLARE
        count INTEGER DEFAULT 0;
    BEGIN
        FOR i IN 1 TO 10 DO
            count := :count + 1;
        END FOR;
        RETURN :count;
    END;


CALL for_with_count();

+----------------+
| FOR_WITH_COUNT |
+----------------+
|             10 |
+----------------+

Or, using variable for count iterations value:

CREATE OR REPLACE PROCEDURE for_with_count()
RETURNS INTEGER
AS
    DECLARE
        count INTEGER DEFAULT 0;
        iterations INTEGER DEFAULT 10; 
    BEGIN
        FOR i IN 1 TO iterations LOOP
            count := :count + 1;
        END LOOP;
        RETURN :count;
    END;

CALL for_with_count();

+----------------+
| for_with_count |
+----------------+
|             10 |
+----------------+

FOR loops and RESULTSET

A FOR loop based on the RESULTSET iterates over rows of the RESULTSET. Thus, the number of iterations is determined by the number of rows in the RESULTSET.

Syntax

FOR var IN result_set_name DO
  statement;
  [statement; ... ]
END FOR [label];

Arguments

result_set_name: The RESULTSET name, which follows object naming rules and is unique to the current scope.

statement: A single SQL statement (including CALL) or a control-flow statement (for example, a looping or branching statement) or a nested block.

label (optional): label allows to define a jump target for a BREAK or CONTINUE statement.

See Example "Update a column in a table" or Example "Filter data and create history of rows updates" as examples of using a FOR loop with RESULTSETs and 'FOR' in Scripting Reference for more information.

WHILE loop

A WHILE loop iterates while a condition is true. In a WHILE loop, the condition is tested immediately before executing the body of the loop. If the condition is false before the first iteration, then the body of the loop does not execute even once.

A WHILE loop is executed as long as the condition test results in TRUE. To do this, the condition is checked immediately before the loop body is executed. If the condition test result changes to FALSE, the loop is aborted. If the condition test returns FALSE before the first loop is executed, the loop body is never executed.

Syntax

WHILE (condition) (DO | LOOP)
    statement;
    [statement; ... ]
END WHILE [label] ;

See "WHILE" in Scripting Reference for more information.

Example

In this example, the stored procedure counts the number of iterations the WHILE loop has run through until the result of the condition "countdown >=0" is FALSE is reached.

CREATE OR REPLACE PROCEDURE 
        countdown_while(countdown_start INTEGER)
RETURNS INTEGER
AS
DECLARE
    counter INTEGER;     -- iterationcounter.
    countdown INTEGER;   -- countdownstartvalue.
BEGIN
    counter := 0;
    countdown := countdown_start;
        WHILE (countdown >= 0) DO
            countdown := countdown - 1;
            counter := counter + 1;
        END WHILE;
        RETURN counter;
END;

CALL countdown_while(10);

+-----------------+
| COUNTDOWN_WHILE |
+-----------------+
|              11 |
+-----------------+

How to terminate loop or iteration

In some situations it is helpful to terminate a loop or iteration, before the regular end of the loop or iteration. There are several ways to do this:

• the explicite BREAK command interrupts the loop immediately. The EXIT command is a synonym for the BREAK command.
• The CONTINUE command interrupts the current iteration. When the CONTINUE command is encountered, execution of the current iteration jumps to the end of the current iteration, skips commands that lie between the CONTINUE command and END [FOR | WHILE | REPEAT | LOOP] in the current iteration, and starts the next iteration. ITERATE is a synonym for the CONTINUE command.

BREAK command

The BREAK (or EXIT) command interrupts the loop immediately. This interrupts both the current iteration and all subsequent iterations.

In addition, you can use the label concept to define to which END [FOR | WHILE | REPEAT | LOOP] should be "skipped" at after the BREAK [label] command is encountered.

See "BREAK" in Scripting Reference for more information.

CONTINUE command

CONTINUE (or ITERATE) skips the rest of the statements in the iteration of a loop and starts the next iteration of the loop.

The label concept to define to which END [FOR | WHILE | REPEAT | LOOP] should be "skipped" at after the CONTINUE [label] command is encountered works with CONTINUE in the same way as with BREAK [label].

See "CONTINUE" in Scripting Reference for more information.

BREAK, CONTINUE and "label" example

In this example, two stored procedures are created, each containing 2 nested loops.

in the stored procedure run_iter(), the BREAK command, although located in the inner loop, ensures that the execution of the outer loop is aborted as soon as the value of count_1 reaches the procedure input value for after_iteration_break.

This is accomplished by specifying the label end_of_ext_loop in END FOR end_of_ext_loop; and 'jump' to this part with BREAK end_of_ext_loop;.

The CONTINUE command ensures that as soon as count_1 reaches the value after_iterations_continue, count_2 is no longer increased.

Variable count_3 counts the number of executions of the outer loop and will not exceed the value 1 in this example, because before it happens, the execution of the outer loop is aborted by BREAK end_of_ext_loop; in the inner loop.

For comparison, see the following function run_iter_2, in which no label was specified for BREAK. This ensures that only the inner loop is aborted and the outer loop can run through 3 times, as defined.

The line that makes the difference between the stored procedures run_iter and run_iter_2 is highlighted in yellow.

CREATE OR REPLACE PROCEDURE my_folder.run_iter(after_iterations_break INTEGER, after_iterations_continue INTEGER)
RETURNS STRING
AS
DECLARE
    count_1 INTEGER DEFAULT 0;     
    count_2 INTEGER DEFAULT 0;
    count_3 INTEGER DEFAULT 0;
    iter_nr INTEGER DEFAULT 0;  
BEGIN
    FOR i IN 1 TO 3 DO
        count_1 := 0;
        count_2 := 0;
        iter_nr := 0;
        count_3 := count_3 + 1;
        FOR i IN 1 TO 10 DO 
            iter_nr := iter_nr + 1;
            count_1 := count_1 + 1;
            IF (iter_nr >= after_iterations_break) THEN 
              BREAK end_of_ext_loop;  
            ELSEIF (iter_nr > after_iterations_continue) THEN 
              CONTINUE;
            END IF;
            count_2 := count_2 + 1; 
        END FOR;
    END FOR end_of_ext_loop;
    RETURN 'Return: ' || count_1::string || ' ' || count_2::string || ' ' || count_3::string;
END;

CALL my_folder.run_iter(9,4);

As you can see, the outer loop runs only 1 time, the inner loop runs 9 times, count_2-variable was only increased 4 times due to CONTINUE command in ELSEIF (iter_nr > after_iterations_continue) THEN branch.

+---------------+
| RUN_ITER      |
+---------------+
| Return: 9 4 1 |
+---------------+

CREATE OR REPLACE PROCEDURE my_folder.run_iter_2(after_iterations_break INTEGER, after_iterations_continue INTEGER)
RETURNS STRING
AS
DECLARE
    count_1 INTEGER DEFAULT 0;     
    count_2 INTEGER DEFAULT 0;
    count_3 INTEGER DEFAULT 0;
    iter_nr INTEGER DEFAULT 0;  
BEGIN
    FOR i IN 1 TO 3 DO 
        count_1 := 0;
        count_2 := 0;
        iter_nr := 0;
        count_3 := count_3 + 1;
        FOR i IN 1 TO 10 DO 
            iter_nr := iter_nr + 1;
            count_1 := count_1 + 1;
            IF (iter_nr >= after_iterations_break) THEN 
              BREAK;                                          
            ELSEIF (iter_nr > after_iterations_continue) THEN 
              CONTINUE;
            END IF;
            count_2 := count_2 + 1; 
        END FOR;
    END FOR end_of_ext_loop;
    RETURN 'Return: ' || count_1::string || ' ' || count_2::string || ' ' || count_3::string;
END;

CALL my_folder.run_iter_2(9,4);

In this case, the outer loop runs three times.

+---------------+
| RUN_ITER_2    |
+---------------+
| Return: 9 4 3 |
+---------------+