Netezza NZPLSQL Control Structures: IF, LOOP, WHILE, FOR and EXIT

  • Post author:
  • Post last modified:August 21, 2019
  • Post category:Netezza
  • Reading time:4 mins read

Netezza NZPLSQL control structures are a useful and important part of the NZPLSQL language. You can use Netezza NZPLSQL control structures to perform some critical decisions based on data and manipulate SQL data in a flexible and powerful way. You can use the Netezza control structures only in procedures.

Netezza NZPLSQL control structures

There are two Netezza NZPLSQL control structures: Conditional control and Iterative control

Read:

Netezza Conditional Control

You can use the IF statement to perform particular task based on the certain conditions. There are three forms of IF statements available in Netezza:

  • IF-THEN
  • IF-THEN-ELSE
  • IF-THEN-ELSE IF

Every NZPLSQL IF statement should have the corresponding END IF statements. In case there is nested IF then there should be two END IF, one for main IF and other one for nested IF.

Netezza IF-THEN Statements

In any procedural language, IF-THEN statements are simplest form of IF statements. All given statements between THEN and END IF are executed if the condition is true, otherwise statements following END IF are executed. For example:

IF v_id IS NOT NULL THEN
 UPDATE patients SET mobile= v_mobile where id = v_id;
END IF;

Netezza IF-THEN-ELSE statements

This conditional control works same as in other programming language. IF-THEN-ELSE statements has an ELSE branch to evaluate in cases when the IF-THEN condition evaluates to FALSE results. You can write the statements to run in the ELSE part. For example:

IF v_id IS NOT NULL THEN
 UPDATE patients SET mobile= v_mobile where id = v_id;
 return ‘t’;
ELSE
 return ‘f’;
END IF;

Netezza IF-THEN-ELSE IF statements

This condition is the nesting of IF in the ELSE part of IF-THEN-ELSE statements. In these kind of statements, you need another END IF for the nested IF statements.

IF v_flag = ‘1’ THEN
 Flag := ‘TRUE’;
ELSE IF v_flag = ‘0’ THEN
Flag := ‘FALSE’;
 END IF;
END IF;

There is other simple for of above statement is below:

IF v_flag = ‘1’ THEN
 Flag := ‘TRUE’;
ELSIF v_flag = ‘0’ THEN
Flag := ‘FALSE’;
ELSE
 Flag := ‘NOT FOUND’;
END IF;

This conditional statement offers some flexibility and eases the coding process when you need to check many alternatives in one statement.

Netezza Iterative Control

With the help of iterative control statements, you can control the flow of execution in your NZPLSQL statements. Netezza supports LOOP, WHILE, FOR and EXIT iterative control statements.

Netezza LOOP Statements

The LOOP statement defines an unconditional loop that repeats until terminated by an EXIT or a RETURN statement.

Syntax:

[<<label>>]
LOOP
statements
END LOOP;

You can optionally label the LOOP, this label can be used by EXIT statement to terminate the LOOP.

Netezza EXIT Statements

The EXIT statement terminates the LOOP.

Syntax:

EXIT [ label ] [ WHEN expression ];

If you miss to provide the label, innermost LOOP will be terminated.

For Example:

LOOP
-- Statements
EXIT WHEN count > 0;
END LOOP;

Netezza WHILE Loop statement

With the use of Netezza WHILE statement, you can loop through a sequence of statements until the evaluation of the condition expression is true.

Syntax:

WHILE expression LOOP
statements
END LOOP;

You can optionally provide the label WHILE statements.

For Example:

WHILE v_id > 0 AND v_id < 1000 LOOP
-- Statements
END LOOP;

Netezza FOR Loop statement

With the help of FOR statements, you can loop over the range of integer values.

Syntax:

[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
Statements
END LOOP;

The variable name is automatically created as type integer and scope is limited to the loop. The two expressions for the lower and upper bound of the range are evaluated only when entering the loop. The iteration step is always 1.

For Example:

FOR i IN 1 .. 10 LOOP
-- Statements
RAISE NOTICE ’i is %’, i;
END LOOP;
Tags: , ,