Conditional control
You use IF statements to take action based on certain conditions.
NZPLSQL has three forms of IF statements:
- IF-THEN
- IF-THEN-ELSE
- IF-THEN-ELSE IF
All NZPLSQL IF statements require a corresponding END IF statement. In ELSE-IF statements, you need two END IF statements: one for the first IF and one for the second (ELSE IF).
IF-THEN statements
IF-THEN statements are
the simplest form of an IF statement. The statements between THEN
and END IF are executed if the condition is true. Otherwise, the statements
that follow the END IF are executed. An example follows:
IF v_user_id <> 0 THEN
UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;
IF-THEN-ELSE statements
IF-THEN-ELSE statements
add an ELSE branch for cases when the IF-THEN condition evaluates
to FALSE. You can specify the statements to run in the ELSE section.
For example:
IF parentid IS NULL or parentid = ''
THEN
return fullname;
ELSE
return hp_true_filename(parentid) || '/' || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count(count) VALUES(v_count);
return 't';
ELSE
return 'f';
END IF;
You can nest IF statements as
in the following example:
IF movies.genre = 'd' THEN
film_genre := 'drama';
ELSE
IF movies.genre = 'c' THEN
film_genre := 'comedy';
END IF;
END IF;
IF-THEN-ELSE IF statements
When you use
the "ELSE IF" statement, you are nesting an IF statement inside the
ELSE statement. Thus, you need one END IF statement for each nested
IF and one for the parent IF-ELSE. For example:
IF movies.genre = 'd' THEN
film_genre := 'drama';
ELSE IF movies.genre = 'c' THEN
film_genre := 'comedy';
END IF;
END IF;
While this form works, it can become a little tedious
and error-prone if there are many alternatives to check. Thus, the
language offers the alternative syntax by using ELSIF or ELSEIF, as
follows:
IF movies.genre = 'd' THEN
film_genre := 'drama';
ELSIF movies.genre = 'c' THEN
film_genre := 'comedy';
ELSIF movies.genre = 'a' THEN
film_genre := 'action';
ELSIF movies.genre = 'n' THEN
film_genre := 'narrative';
ELSE
-- An uncategorized genre form has been requested.
film_genre := 'Uncategorized';
END IF;
The IF-THEN-ELSIF-ELSE form offers some flexibility and eases the coding process when you need to check many alternatives in one statement. While it is equivalent to nested IF-THEN-ELSE-IF-THEN commands, it needs only one END IF statement.