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 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
- Conditional Control
- Iterative Control
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 ;
memsql> 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 ;
memsql> SELECT nesting(0);
+------------+
| nesting(0) |
+------------+
| 7 |
+------------+
memsql> 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 ;
memsql> 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 ;
memsql> 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 ;
memsql> 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 ;
memsql> 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 ;
memsql> 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 ;
memsql> 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:
memsql> 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 ;
memsql> 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 ;
memsql> 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 ;
memsql> 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 ;
memsql> 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:
memsql> 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
.
memsql> 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 labeledWHILE boolean_expression LOOP
, where theCONTINUE
statement returns control to the top of theWHILE
loop.CONTINUE WHEN boolean_expression
in aLOOP
, where any statements following theCONTINUE
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
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
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 ;
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
.
memsql> 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.
memsql> SELECT continue_when_example();
+-------------------------+
| continue_when_example() |
+-------------------------+
| 30 |
+-------------------------+