Control Structures
IF Statement
An IF
statement evaluates a condition and executes one of two statements depending on the result of the evaluation. You can nest IF
statements to create complex branching logic. A dependent ELSE
statement will be executed only if the IF
statement evaluates to false.
Usage
IF (criteria)
block
[ELSE
block]
Example
BEGIN
DECLARE STRING var1 = 'South America';
IF (var1 = 'North America')
BEGIN
...
END
ELSE
BEGIN
...
END
END
NULL
values should be considered in the criteria of an IF
statement. IS NULL
can be used to detect the presence of a NULL
value.
Loop Statement
A LOOP
statement is an iterative control construct that is used to cursor through a result set.
Usage
[label :] LOOP ON (<select statement>) AS <cursorname>
block
Syntax
- The label must not be the same as any other label used in statements containing this one.
Example
BEGIN
LOOP ON (SELECT id FROM "SYSADMIN.ScheduleJobs") AS JOB
BEGIN
CALL SYSADMIN.createSchedule(jobId => job.id, type => 'once', intervl => 0, startDelay => 0, enabled => true);
END
END;;
While Statement
A WHILE
statement is an iterative control construct that is used to execute a block repeatedly whenever a specified condition is met.
Usage
[label :] WHILE <criteria>
block
Syntax
- The label must not be the same as any other label used in statements containing this one.
Example
BEGIN
DECLARE integer i = 0;
WHILE (i < 5) BEGIN
SELECT i; i = i + 1;
END
END ;;
Continue Statement
A CONTINUE
statement is used inside a LOOP
or WHILE
construct to continue with the next loop by skipping over the rest of the statements in the loop. It must be used inside a LOOP
or WHILE
statement.
Usage
CONTINUE [label];
Syntax
- If the label is specified, it must exist on a containing
LOOP
orWHILE
statement; - If no label is specified, the statement will affect the closest containing
LOOP
orWHILE
statement.
Example
BEGIN
DECLARE integer i = 0;
DECLARE string str = 0;
WHILE (i < 5)
BEGIN
i = i + 1;
IF (i in (2, 3))
CONTINUE;
str = str || i;
END
SELECT str;
END ;;
Break Statement
A BREAK
statement is used inside a LOOP
or WHILE
construct to break from the loop. It must be used inside a LOOP
or WHILE
statement.
Usage
BREAK [label];
Syntax
- If the label is specified, it must exist on a containing
LOOP
orWHILE
statement; - If no label is specified, the statement will affect the closest containing
LOOP
orWHILE
statement.
Example
BEGIN
DECLARE integer i = 0;
WHILE (i < 5) BEGIN
IF(i = 3)
BREAK;
SELECT i;
i = i + 1;
END
END ;;
Leave Statement
A LEAVE
statement is used inside a compound, LOOP
, or WHILE
constructs to leave to the specified level.
Usage
LEAVE label;
Syntax
- The label must exist on a containing compound statement,
LOOP
, orWHILE
statement.
Example
BEGIN
DECLARE string str = 0;
LABEL_A: BEGIN
BEGIN
str = 'a';
LEAVE LABEL_A;
END
str = 'b';
END
SELECT str;
END ;;