Outdated Version

You are viewing an older version of this section. View current production version.

Control Flow Statements

This topic describes the syntax to define logical control flow in SingleStore (MemSQL) Procedural SQL (MPSQL) for stored procedures and user-defined scalar-valued functions (UDFs). The following control flow statements are described in this topic:

Statement Blocks

Statement blocks optionally declare variables, then enclose one or more statements, and optionally handle exceptions. A statement block acts as a single compound statement.

When executed, the statements in the statement_list portion of the block are processed sequentially. Each statement in a statement_list must be terminated with a semicolon (;).

Statement blocks can be nested. A statement block can appear where a statement appears. Variables cannot be defined in a nested block which override the definition of variables or parameters with the same name defined in an enclosing block.

Syntax

[ DECLARE variable_list ] [ ...n ]
BEGIN
  statement_list
[ EXCEPTION
  when_clause_list ]
END

Arguments

variable_list

A variable definition or series of definitions. A sequence of zero or more DECLARE keywords may appear, each with its own variable_list.

statement_list

Any valid procedural SQL statement or series of statements.

when_clause_list

The when_clause_list defines different possible exception conditions and statements to handle them. See the Exceptions and Exception Handling topic for more details.

Examples

The following example adds one to the input integer and returns the result.

DELIMITER //
CREATE FUNCTION plus_one(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    b += 1;
    RETURN b;
  END //
DELIMITER ;

SELECT plus_one(1);

****
+-------------+
| plus_one(1) |
+-------------+
|           2 |
+-------------+

The example below illustrates nested statement blocks, and the general syntax of the DECLARE statements at the beginning of a block.

DELIMITER //
CREATE OR REPLACE FUNCTION nesting(i INT) RETURNS BIGINT AS
DECLARE x INT = 0;
DECLARE z1 INT; z2 VARCHAR(255);
BEGIN
  DECLARE q INT = 0;
  BEGIN
    IF q = i THEN
      DECLARE xx INT = 7;
      BEGIN
         RETURN q + xx;
      END;
    ELSE
      DECLARE q2 INT = 88;
      BEGIN
        RETURN q2;
      END;
    END IF;
  END;
END //
DELIMITER ;

SELECT nesting(0);

****
+------------+
| nesting(0) |
+------------+
|          7 |
+------------+

SELECT nesting(1);

****
+------------+
| nesting(1) |
+------------+
|         88 |
+------------+

IF … THEN … END IF

Specifies one or more statements to be executed if a given boolean expression evaluates to TRUE. All IF blocks must be terminated with the END IF keyword pair.

Syntax

IF boolean_expression THEN
  statement_list
END IF;

Arguments

boolean_expression

An expression that evaluates to TRUE or FALSE. If the expression evaluates to TRUE, the statement_list enclosed by THEN ... END IF will be executed. If the expression evaluates to FALSE, the statement_list will not be executed.

statement_list

Any valid procedural SQL statement or series of statements.

Example

The following example returns a boolean value (1 for TRUE and 0 for FALSE) if the input integer is odd.

DELIMITER //
CREATE FUNCTION is_odd(a INT) RETURNS BOOL AS
  BEGIN
    IF a % 2 = 0 THEN
      RETURN FALSE;
    END IF;
    RETURN TRUE;
  END //
DELIMITER ;

SELECT is_odd(5);

****
+-----------+
| is_odd(5) |
+-----------+
|         1 |
+-----------+

IF … THEN … ELSE … END IF

Specifies a condition and two alternative lists of statements to execute, depending on whether the condition is true. All IF blocks must be terminated with the END IF keyword pair.

Syntax

IF boolean_expression THEN
  statement_list
ELSE
  statement_list
END IF;

Arguments

boolean_expression

An expression that evaluates to TRUE or FALSE. If the expression evaluates to TRUE, the statement_list enclosed by THEN ... ELSE will be executed. If the expression evaluates to FALSE, the statement_list following ELSE will be executed instead.

statement_list

Any valid procedural SQL statement or series of statements.

Example

The following example returns a boolean value (1 for TRUE and 0 for FALSE) if the input integer is odd.

DELIMITER //
CREATE FUNCTION is_odd(a INT) RETURNS BOOL AS
  DECLARE
    result BOOL;
  BEGIN
    IF a % 2 = 0 THEN
      result = FALSE;
    ELSE
      result = TRUE;
    END IF;
    RETURN result;
  END //
DELIMITER ;

SELECT is_odd(3);

****
+-----------+
| is_odd(3) |
+-----------+
|         1 |
+-----------+

IF … THEN … ELSIF … THEN … END IF

Specifies one or more statements to be executed if an initial boolean expression evaluates to TRUE. If this boolean expression evaluates to FALSE, one or more ELSIF boolean expressions are evaluated. If an ELSIF expression evaluates to TRUE, its statement list will be executed, and no additional ELSIF expressions will be evaluated.

An optional ELSE clause can also be specified after a block of ELSIF statements. If none of the boolean expressions preceding the ELSE clause evaluates to TRUE, then the list of statements in the ELSE clause are executed.

All IF blocks must be terminated with the END IF keyword pair.

Syntax

IF boolean_expression THEN
  statement_list
ELSIF boolean_expression THEN
  statement_list
[...n]
[ELSE
  statement_list
]
END IF;

Arguments

boolean_expression

An expression that evaluates to TRUE or FALSE.

statement_list

Any valid procedural SQL statement or series of statements.

Example

The following example accepts a test score as an integer and outputs a grade letter.

DELIMITER //
CREATE FUNCTION test_grade(test_score INT) RETURNS CHAR(1) AS
  DECLARE
    grade_letter CHAR(1);
  BEGIN
    IF test_score >= 90 THEN
      grade_letter = 'A';
    ELSIF test_score >= 80 THEN
      grade_letter = 'B';
    ELSIF test_score >= 70 THEN
      grade_letter = 'C';
    ELSIF test_score >= 60 THEN
      grade_letter = 'D';
    ELSE
      grade_letter = 'F';
    END IF;
    RETURN grade_letter;
  END //
DELIMITER ;

SELECT test_grade(89);

****
+----------------+
| test_grade(89) |
+----------------+
| B              |
+----------------+

LOOP … END LOOP

Specifies one or more statements to execute repeatedly in an infinite loop. Each iteration of the loop executes the statement list and returns control to the top of the loop. Loop statements can be nested within themselves and other control statements, such as IF ... END IF.

Syntax

LOOP
  statement_list
END LOOP;

Arguments

statement_list

Any valid procedural SQL statement or series of statements.

Example

The following example creates an endless loop that adds one to the input integer. The RETURN statement will never get reached.

DELIMITER //
CREATE FUNCTION endless_loop(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    LOOP
      b += 1;
    END LOOP;
    RETURN b;
  END //
DELIMITER ;

EXIT and EXIT WHEN

Terminates the current iteration of a loop and then exits the loop body, and optionally when a boolean condition is met. EXIT and EXIT WHEN can be used in any loop body, including LOOP, FOR ... LOOP, or WHILE ... LOOP.

Syntax

{ [FOR | WHILE ] expression } LOOP
  [statement_list]
  { EXIT | EXIT WHEN boolean_condition } [loop_label] ;
END LOOP;

Arguments

expression

Any expression associated with the loop declaration, such as a conditional boolean expression.

statement_list

Any valid procedural SQL statement or series of statements.

boolean_expression

An expression that evaluates to TRUE or FALSE. If the expression evaluates to TRUE, the statement_list preceding EXIT will be executed. If the expression evaluates to FALSE, loop will continue iterating until the boolean expression evaluates to TRUE.

loop_label

The optional identifier for a parent loop. If specifying EXIT with a loop label, the label must be set on one of the parent loops.

Examples

IF … THEN … EXIT Example

The following example generates text of the minimum specified length, and uses an EXIT statement in the body of a loop to return the result when the boolean condition is met.

DELIMITER //
CREATE FUNCTION text_generator(min_length INT) RETURNS TEXT AS
  DECLARE
    lorem_text TEXT = "Lorem ipsum dolor sit amet. ";
    result TEXT = lorem_text;
  BEGIN
    LOOP
      IF LENGTH(result) < min_length THEN
        result = CONCAT(result, lorem_text);
      ELSE
        EXIT;
      END IF;
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

SELECT text_generator(50);

****
+----------------------------------------------------------+
| text_generator(50)                                       |
+----------------------------------------------------------+
| Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet.  |
+----------------------------------------------------------+

EXIT WHEN Example

The following example is similar to the LOOP ... IF ... EXIT example and generates text of the minimum specified length. When the length is reached as specified in the WHEN expression, the loop is exited.

DELIMITER //
CREATE FUNCTION text_generator(min_length INT) RETURNS TEXT AS
  DECLARE
    lorem_text TEXT = "Lorem ipsum dolor sit amet. ";
    result TEXT = lorem_text;
  BEGIN
    LOOP
      EXIT WHEN LENGTH(result) >= min_length;
      result = CONCAT(result, lorem_text);
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

SELECT text_generator(50);

****
+----------------------------------------------------------+
| text_generator(50)                                       |
+----------------------------------------------------------+
| Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet.  |
+----------------------------------------------------------+

WHILE … LOOP … END LOOP

Specifies an initial boolean condition and a list of statements. If the expression evaluates to TRUE, the loop will begin execution of the statement list. After each statement is executed, control will be returned to the top of the loop and the boolean expression will be evaluated again. If the expression evaluates to FALSE, the loop will immediately exit.

Syntax

WHILE boolean_expression LOOP
  statement_list
END LOOP;

Arguments

boolean_expression

An expression that evaluates to TRUE or FALSE.

statement_list

Any valid procedural SQL statement or series of statements.

Example

The following example is similar to the LOOP ... IF ... EXIT and LOOP ... EXIT WHEN examples, and generates text of the minimum specified length. While the length remains shorter than the desired length, the loop will continue execution. As soon as the condition is met, the loop immediately exits.

DELIMITER //
CREATE FUNCTION text_generator(min_length INT) RETURNS TEXT AS
  DECLARE
    lorem_text TEXT = "Lorem ipsum dolor sit amet. ";
    result TEXT = lorem_text;
  BEGIN
    WHILE LENGTH(result) < min_length LOOP
      result = CONCAT(result, lorem_text);
    END LOOP;
    RETURN result;
  END //
DELIMITER ;

SELECT text_generator(50);

****
+----------------------------------------------------------+
| text_generator(50)                                       |
+----------------------------------------------------------+
| Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet.  |
+----------------------------------------------------------+

FOR … LOOP … END LOOP

Specifies a loop with a fixed number of iterations using either the size of an array or a range of integer values as the iteration scheme. FOR loops support reverse iteration using the REVERSE keyword and explicit loop strides by specifying an integer value with the BY keyword.

Syntax

Array Range FOR Loop Syntax

FOR counter_variable IN array_expression LOOP
  statement_list
END LOOP;

Integer Range FOR Loop Syntax

FOR counter_variable IN [REVERSE]
      lower_bound .. upper_bound [BY stride_count] LOOP
  statement_list
END LOOP;

Arguments

counter_variable

A variable that is scoped to the body of the FOR loop and must be unique within this scope.

array_expression

An array expression that references an initialized array with a defined length.

lower_bound

An expression that evaluates to an integer value, which represents the lower bound (starting value) of the iteration scheme.

upper_bound

An expression that evaluates to an integer value, which represents the upper bound (ending value) of the iteration scheme.

stride_count

An expression that evaluates to an integer value, which increments the current iteration count by the specified amount after the statement list in the loop body has been executed.

statement_list

Any valid procedural SQL statement or series of statements.

Remarks

When using a FOR loop on a variable that references an array, the loop creates a read-only copy of the array and uses it as the iteration scheme. Therefore, if both the loop declaration and the loop body reference the same array, the loop’s iteration scheme is not affected by any modifications to the array. Instead, the modifications are applied outside the scope of the loop. Consider the following example:

DELIMITER //
CREATE FUNCTION array_modification() RETURNS VARCHAR(255) AS
  DECLARE
    myarray ARRAY(INT) = [1, 2, 3];
    loop_count INT = 0;
  BEGIN
    FOR i IN myarray LOOP
      myarray = [1, 2, 3, 4];
      loop_count += 1;
    END LOOP;
    RETURN CONCAT("loop_count = ", loop_count,
                    ", myarray size = ", LENGTH(myarray));
  END //
DELIMITER ;

This example declares an myarray with a size of 3, then loops over the array. In the loop body, the myarray value is changed. However, the loop executes only 3 times. After the loop exits, myarray is updated to the new value with a length of 4. The results are shown below:

SELECT array_modification();

****
+----------------------------------+
| array_modification()             |
+----------------------------------+
| loop_count = 3, myarray size = 4 |
+----------------------------------+

Examples

FOR Loop on Array Range Example

The following example declares an array and then loops over the array to count its size:

DELIMITER //
CREATE FUNCTION count_array() RETURNS INT AS
  DECLARE
    myarray ARRAY(INT) = [1, 2, 3];
    count INT = 0;
  BEGIN
    FOR i IN myarray LOOP
      count += 1;
    END LOOP;
    RETURN count;
  END //
DELIMITER ;

SELECT count_array();

****
+---------------+
| count_array() |
+---------------+
|             3 |
+---------------+

FOR Loop on Integer Range Example

The following example uses a FOR loop to computes the factorial of the input integer and returns it.

DELIMITER //
CREATE FUNCTION factorial(num INT) RETURNS INT AS
  DECLARE
    factor INT = 1;
  BEGIN
    FOR i IN 1 .. num LOOP
      factor = factor * i;
    END LOOP;
    RETURN factor;
  END //
DELIMITER ;

SELECT factorial(5);

****
+--------------+
| factorial(5) |
+--------------+
|          120 |
+--------------+

Reverse FOR Loop Example

The following example uses a reversed FOR loop to count down from the specified input number and returns a text string. Note that the larger number must precede the smaller number in the loop declaration when REVERSE is specified.

DELIMITER //
CREATE FUNCTION countdown(num INT) RETURNS VARCHAR(255) AS
  DECLARE
    output VARCHAR(255) = "Countdown: ";
  BEGIN
    FOR i IN REVERSE num .. 1 LOOP
      output = CONCAT(output, i, ", ");
    END LOOP;
    output = CONCAT(output, "Launch!");
    RETURN output;
  END //
DELIMITER ;

SELECT countdown(10);

****
+---------------------------------------------------+
| countdown(10)                                     |
+---------------------------------------------------+
| Countdown: 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, Launch! |
+---------------------------------------------------+

FOR Loop with Stride Length

The following example uses a reverse loop with a stride length of 2 to count down from the input integer and output each even number.

DELIMITER //
CREATE OR REPLACE FUNCTION countdown_evens(num INT) RETURNS VARCHAR(255) AS
  DECLARE
    output VARCHAR(255) = "Countdown Evens: ";
    start INT = num;
  BEGIN
    IF num % 2 = 1 THEN
      start = start - 1;
    END IF;
    output = CONCAT(output, start);
    start = start - 2;
    FOR i IN REVERSE start .. 2 BY 2 LOOP
      output = CONCAT(output, ", ", i);
    END LOOP;
    RETURN output;
  END //
DELIMITER ;

SELECT countdown_evens(11);

****
+---------------------------------+
| countdown_evens(11)             |
+---------------------------------+
| Countdown Evens: 10, 8, 6, 4, 2 |
+---------------------------------+

Loop Labels

Loops can be labeled using unique identifier syntax, which makes it easier to identify a specific loop in a series of nested loops. A loop label must be declared before the LOOP keyword, but optionally be labeled after the terminating END LOOP keyword pair.

Loop labels can be used to terminate any loop within the scope of a nested loop by using an EXIT statement. For example, a nested loop can terminate one or more parent loops in addition to itself, effectively short-circuiting any desired parent loops. Loop labels can also be used with the CONTINUE statement for conditional loops to return control to the initial conditional expression. See the examples below for more information.

Syntax

<<label_name>>
{ WHILE boolean_expression LOOP | LOOP }
  statement_list
END LOOP [label_name];

Arguments

label_name

An identifier for the loop, enclosed by double angle brackets (<< and >>) for the initial declaration. Loop labels must be unique within the scope of the function body, and cannot conflict with any other identifiers such as input parameters, variables, and so on.

boolean_expression

An expression that evaluates to TRUE or FALSE.

statement_list

Any valid procedural SQL statement or series of statements.

Examples

Loops Labels with EXIT

The following example demonstrates loop labels for EXIT statements by creating a nested loop where the outer loop is named parent and the inner loop is named child.

DELIMITER //
CREATE FUNCTION parent_child_loop(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    <<parent>>
    LOOP
      <<child>>
      LOOP
        EXIT parent WHEN a >= 100;
        b += 1;
      END LOOP child;
      RETURN 200;
    END LOOP parent;
    RETURN b;
  END //
DELIMITER ;

When this function is executed, the outer loop’s RETURN statement is never reached. Instead, the inner loop’s EXIT parent statement is executed when the input value meets or exceeds 100:

SELECT parent_child_loop(1);

****
+----------------------+
| parent_child_loop(1) |
+----------------------+
|                  100 |
+----------------------+

Loop Labels with CONTINUE

The following example demonstrates loop labels for CONTINUE statements by creating a single WHILE loop.

DELIMITER //
CREATE FUNCTION continue_loop_label(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    <<myloop>>
    WHILE b < 10 LOOP
      b += 1;
      CONTINUE myloop;
      EXIT myloop;
    END LOOP myloop;
    RETURN b;
  END //
DELIMITER ;

When this function is executed, the EXIT myloop statement is never reached because the preceding CONTINUE myloop statement forces return of control to the loop’s initial conditional statement: WHILE a < 10 LOOP.

SELECT continue_loop_label(1);

****
+------------------------+
| continue_loop_label(1) |
+------------------------+
|                     10 |
+------------------------+

CONTINUE and CONTINUE WHEN

Forces return of control to the top of a loop’s conditional expression or specifies its own conditional expression where, if satisfied, returns control to the top of the loop. CONTINUE syntax can be used in the following ways:

CONTINUE loop_label in a labeled WHILE boolean_expression LOOP, where the CONTINUE statement returns control to the top of the WHILE loop.
CONTINUE WHEN boolean_expression in a LOOP, where any statements following the CONTINUE statement will not be executed. Instead, control will be returned to the top of the loop.

See the examples below for more information.

Syntax

[<<label_name>>]
{ WHILE boolean_expression LOOP | LOOP }
  statement_list
  { CONTINUE WHEN boolean_expression | CONTINUE label_name }
END LOOP [label_name];

Arguments

label_name

boolean_expression

An expression that evaluates to TRUE or FALSE.

statement_list

Any valid procedural SQL statement or series of statements.

Examples

CONTINUE for Labeled Loop

The following example demonstrates a CONTINUE statement used inside of a labeled WHILE loop.

DELIMITER //
CREATE FUNCTION continue_labeled_example(a INT) RETURNS INT AS
  DECLARE
    b INT = a;
  BEGIN
    <<myloop>>
    WHILE b < 10 LOOP
      b += 1;
      CONTINUE myloop;
      EXIT myloop;
    END LOOP myloop;
    RETURN b;
  END //
DELIMITER ;

SELECT continue_labeled_example(1);

****
+-----------------------------+
| continue_labeled_example(1) |
+-----------------------------+
|                          10 |
+-----------------------------+

CONTINUE WHEN for a Non-Labeled Loop

The following example demonstrates loop labels for CONTINUE statements by creating a single loop.

DELIMITER //
CREATE OR REPLACE FUNCTION continue_when_example() RETURNS INT AS
  DECLARE
    a INT = 0;
    b INT = 0;
  BEGIN
    LOOP
      a += 1;
      CONTINUE WHEN a < 10;
      b += 1;
      EXIT WHEN b > 20;
    END LOOP;
    RETURN a;
  END //
DELIMITER ;

In this example, the CONTINUE WHEN statement forces return of control to the top of the loop until the value of a is greater than or equal to 10. After a's value reaches 10, the b += 1 statement will be executed until the exit condition is met.

SELECT continue_when_example();

****
+-------------------------+
| continue_when_example() |
+-------------------------+
|                      30 |
+-------------------------+

Was this Article Helpful?

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.

Control Flow Statements

Statement Blocks

Syntax

Arguments

Examples

IF … THEN … END IF

Syntax

Arguments

Example

IF … THEN … ELSE … END IF

Syntax

Arguments

Example

IF … THEN … ELSIF … THEN … END IF

Syntax

Arguments

Example

LOOP … END LOOP

Syntax

Arguments

Example

EXIT and EXIT WHEN

Syntax

Arguments

Examples

WHILE … LOOP … END LOOP

Syntax

Arguments

Example

FOR … LOOP … END LOOP

Syntax

Arguments

Remarks

Examples

Loop Labels

Syntax

Arguments

Examples

CONTINUE and CONTINUE WHEN

Syntax

Arguments

Examples

Questions?

Sorry About That