main | forum May 25th, 2020  CP207 Main Overview Forum Notes 0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012 0013 0014 0015 0016 Tests/Quizes quiz1 quiz2 quiz3 quiz4 quiz5 midterm northwoods db midterm ans final  ## 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
BEGIN

ELSE
END IF;
END;``````

Which for our particular example number grade produces output:

That about covers the IF statement. 