Prentice Hall Oracle Plsql By Example 3Rd Edition [Electronic resources] نسخه متنی

Lab 5.3 Nested IF Statements

You have encountered different types of conditional controls: IF-THEN statement, IF-THEN-ELSE statement, and ELSIF statement. These types of conditional controls can be nested inside of anotherfor example, an IF statement can be nested inside an ELSIF and vice versa. Consider the following:

FOR EXAMPLE

DECLARE
v_num1 NUMBER := &sv_num1;
v_num2 NUMBER := &sv_num2;
v_total NUMBER;
BEGIN
IF v_num1 > v_num2 THEN
DBMS_OUTPUT.PUT_LINE ('IF part of the outer IF');
v_total := v_num1 - v_num2;
ELSE
DBMS_OUTPUT.PUT_LINE ('ELSE part of the outer IF');
v_total := v_num1 + v_num2;

IF v_total < 0 THEN

DBMS_OUTPUT.PUT_LINE ('Inner IF');

v_total := v_total * (-1);

END IF;
END IF;
DBMS_OUTPUT.PUT_LINE ('v_total = '||v_total);
END;

The IF-THEN-ELSE statement is called an outer IF statement because it encompasses the IF-THEN statement (shown in bold letters). The IF-THEN statement is called an inner IF statement because it is enclosed by the body of the IF-THEN-ELSE statement.

Assume that the value for v_num1 and v_num2 are 4 and 3 respectively. First, the condition

v_num1 > v_num2

of the outer IF statement is evaluated. Since 4 is not greater than 3, the ELSE part of the outer IF statement is executed. As a result, the message

ELSE part of the outer IF

is displayed, and the value of v_total is calculated. Next, the condition

v_total < 0

of the inner IF statement is evaluated. Since that value of v_total is equal l, the condition yields TRUE, and message

Inner IF

is displayed. Next, the value of v_total is calculated again. This logic is demonstrated by the output produced by the example:

Enter value for sv_num1: -4

old 2: v_num1 NUMBER := &sv_num1;

new 2: v_num1 NUMBER := -4;

Enter value for sv_num2: 3

old 3: v_num2 NUMBER := &sv_num2;

new 3: v_num2 NUMBER := 3;

ELSE part of the outer IF

Inner IF

v_total = 1

PL/SQL procedure successfully completed.

Logical Operators

So far in this chapter, you have seen examples of different IF statements. All of these examples used test operators, such as >, <, and =, to test a condition. Logical operators can be used to evaluate a condition, as well. In addition, they allow a programmer to combine multiple conditions into a single condition if there is such a need.

FOR EXAMPLE

DECLARE
v_letter CHAR(1) := '&sv_letter';
BEGIN
IF (v_letter >= 'A' AND v_letter <= 'Z') OR
(v_letter >= 'a' AND v_letter <= 'z')
THEN
DBMS_OUTPUT.PUT_LINE ('This is a letter');
ELSE
DBMS_OUTPUT.PUT_LINE ('This is not a letter');
IF v_letter BETWEEN '0' and '9' THEN
DBMS_OUTPUT.PUT_LINE ('This is a number');
ELSE
DBMS_OUTPUT.PUT_LINE ('This is not a number');
END IF;
END IF;
END;

In this example, the condition

(v_letter >= 'A' AND v_letter <= 'Z') OR

(v_letter >= 'a' AND v_letter <= 'z')

uses logical operators AND and OR. There are two conditions

(v_letter >= 'A' AND v_letter <= 'Z')

and

(v_letter >= 'a' AND v_letter <= 'z')

combined into one with the help of the OR operator. It is also important for you to realize the purpose of the parentheses. In this example, they are used to improve readability only, because the operator AND takes precedence over the operator OR.

When the symbol "?" is entered at runtime, this example produces the following output:

Enter value for sv_letter: ?

old 2: v_letter CHAR(1) := '&sv_letter';

new 2: v_letter CHAR(1) := '?';

This is not a letter

This is not a number

PL/SQL procedure successfully completed.