main | forum
May 25th, 2024

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.