Notes 0008

PL/SQL IF Statement

PL/SQL, being a procedural language naturally has lots of flow control constructs, from IF statements to WHILE loops.

In these notes we will examine some of the more popular ones.

Remember to type: SET SERVEROUTPUT ON in SQL*Plus before running any programs, so that you can see the output.

IF - THEN Structure

The general format of an IF statement is:

IF condition THEN
    program_statements
END IF;

Assuming we all know how to program, and know what IF statements are, I'm not going to spend too much time on the obvious.

An example program that uses an IF statement is:

DECLARE
    A NUMBER(6);
    B NUMBER(6);
BEGIN
    A := 23;
    B := A * 5;
    IF A < B THEN
        DBMS_OUTPUT.PUT_LINE('Ans: ' || A || ' is less than ' || B);
    END IF;
END;

Which produces the expected output of:

Ans: 23 is less than 115

IF - ELSE Structure

Just as in any programming language that has an IF statement, there is also the ELSE clause to the IF statement.

The full structure of an IF statement is thus:

IF condition THEN
    if_condition_is_true_code
ELSE
    if_condition_is_false_code
END IF;

Let's modify our simple example to:

DECLARE
    A NUMBER(6);
    B NUMBER(6);
BEGIN
    A := 23;
    B := A / 5;
    IF A < B THEN
        DBMS_OUTPUT.PUT_LINE('Ans: ' || A || ' is less than ' || B);
    ELSE
        DBMS_OUTPUT.PUT_LINE('Ans: ' || A || ' is greater than ' || B);    
    END IF;
END;

Note that we've also modified the B := A * 5 to B := A / 5 in order to test the ELSE condition.

IF Statement nesting

We can also put IF statements inside other IF statements. Here, again, let's jump right into an example:

DECLARE
    A NUMBER(6);
    B NUMBER(6);
    C NUMBER(6);
    ABCMAX NUMBER(6);
BEGIN
    A := 23;
    B := A / 5;
    C := B * 7;
    IF A > B THEN
        IF A > C THEN
            ABCMAX := A;
        ELSE
            ABCMAX := C;
        END IF; 
    ELSE
        IF B > C THEN
            ABCMAX := B;
        ELSE
            ABCMAX := C;
        END IF;            
    END IF;
    DBMS_OUTPUT.PUT_LINE('Max of: ' || A || ', ' || B || 
        ', and ' || C || ' is ' || ABCMAX);    
END;

The code above finds the maximum value (ABCMAX) of three variables (A, B, and C).

The code looks self explanatory; so we won't spend much time on it.

IF – ELSIF Structure

When IF and ELSE are not enough, we can resort to using ELSIF. This is an else if equivalent in C (and in Perl it is actually named elsif).

Let's say we wanted to calculate the letter grade given a number grade, we may write a program such as:

DECLARE
    NGRADE NUMBER;
    LGRADE CHAR(2);
BEGIN

    NGRADE := 82.5;

    IF NGRADE > 95 THEN
        LGRADE := 'A+';
    ELSIF NGRADE > 90 THEN
        LGRADE := 'A';
    ELSIF NGRADE > 85 THEN
        LGRADE := 'B+';
    ELSIF NGRADE > 80 THEN
        LGRADE := 'B';
    ELSIF NGRADE > 75 THEN
        LGRADE := 'C+';
    ELSIF NGRADE > 70 THEN
        LGRADE := 'C';
    ELSIF NGRADE > 65 THEN
        LGRADE := 'D+';
    ELSIF NGRADE > 60 THEN
        LGRADE := 'D';
    ELSE
        LGRADE := 'F';
    END IF;    
    DBMS_OUTPUT.PUT_LINE('Grade ' || NGRADE || ' is ' || LGRADE);        
END;

Which for our particular example number grade produces output:

Grade 82.5 is B

That about covers the IF statement.

© 2006, Particle