11.10 IF Statement

The IF construct implements IF-THEN-ELSE logic with optional ELSIF clauses.

11.10.1 Simple IF

The simple IF statement performs an action based on a true-false condition, illustrated by the diamond in Figure 11-1. If the condition evaluates TRUE, the code executes the logic in the box.

Figure 11-1. Simple IF.

An example is:

 IF (a < 10) THEN b := 1; END IF; 

11.10.2 If-Then-Else

An IF-THEN-ELSE statement, shown in Figure 11-2, performs an action based on a true-false condition. An alternative action is always performed. If the test evaluates TRUE, the code executes the logic in that box, otherwise it executes the logic in the other box.

Figure 11-2. If-Then-Else.

An example is:

 IF (a < 10) THEN b := 1; ELSE b := 20; END IF; 

11.10.3 If-Then-Elsif with Else

The IF-THEN-ELSIF-with-ELSE construct is shown in Figure 11-3. Any TRUE condition will execute code in that box and exit the construct. A FALSE condition enters the next decision. The logic in the ELSE part executes if no condition is TRUE.

Figure 11-3. If-Then-Elsif with Else.

In this example, B is set to 3 if no condition ever evaluates to TRUE

 IF (a < 10) THEN     b := 1; ELSIF (a = 11) THEN     b := 2; ELSE     b := 3; END IF; 

Multiple conditions may be true, but the logic of the first TRUE condition is executed, only. The following IF statement begins with assigning A the value of 8. The first test is TRUE, so is the second. After this IF statement, B is equal to 1.

 a := 8; IF (a < 10) THEN     b := 1; ELSIF (a < 20) THEN     b := 2; ELSE     b := 3; END IF; 

11.10.4 If-Then-Elsif No Else

The IF-THEN-ELSIF-No-ELSE construct, shown in Figure 11-4, offers another option with IF statements. Any TRUE condition will execute code in that box and exit the construct. Each decision failure enters the next decision. If all tests evaluate to FALSE, no logic is executed. This is significantly different from IF statements that use an ELSE clause.

Figure 11-4. If-Then-Elsif No Else.

This example does not assign a value to B if the test conditions for A are each false.

 IF (a < 10) THEN     b := 1; ELSIF (a = 11) THEN     b := 2; END IF; 

11.10.5 Statement Expressions

The IF syntax includes an optional ELSIF and ELSE clause.

 IF (multiline BOOLEAN expression) THEN     Statement;     Statement; ELSIF (multiline BOOLEAN expression) THEN     Statement;     Statement; ELSIF (multiline BOOLEAN expression) THEN     Statement;     Statement; ELSE     Statement;     Statement; END IF; 

The following is a simple IF statement. This IF-THEN-ELSE statement assigns a value to B for all values of A. An IF statement can be coded in such a manner that it overlooks some conditions. Sometimes this is intended. Sometimes it is a bug.

 IF (a < 10) THEN b := 1; END IF; 

The previous IF statement only considers the case where A is less than 10. This next statement takes an action on B for any value of A.

 IF (a < 10) THEN b := 1; ELSE b := 2; END IF; 

An IF statement can use the PL/SQL NULL statement to indicate that all conditions are being considered . For example, the following IF-THEN-ELSE contains a PL/SQL NULL statement to explicitly state that no action is to be taken if A is greater than 10.

 IF (a <= 10) THEN     b := 1; ELSE     NULL; END IF; 

An IF-THEN-ELSIF statement does not have to include an ELSE clause. The following takes no action if A is greater than 10 and not equal to 20.

 IF (a < 10) THEN     b := 1; ELSIF (a = 20) THEN     b := 2; END IF; 

For this next statement, if A is less than 10, B is assigned 1, not 2. When an IF condition is met, no other conditions are considered.

 IF (a < 10) THEN     b := 1; ELSIF (a <= 20) THEN     b := 2; ELSE     b := 3; END IF; 

Decision tables are useful tools for planning lengthy IF statements. A decision table is two columns with the left column condition determining the value of the right column. A decision table can quickly identify a missing condition. A sample decision table for changing values of B based on A is the following:

A	B
A < 10	1
10 >= A < 20	2
225	3
All other values	4

The left column of the decision table includes all values in the range of A. The statement for this is the following.

 IF (A < 10) THEN     B := 1; ELSIF (A  >= 10 AND A < 20) THEN     B := 2; ELSIF (A = 225) THEN     B := 3; ELSE     B := 4; END IF; 

The IF condition can be any statement that evaluates to TRUE or FALSE. You have a package called BUSINESS_DAYS and it contains a function that returns the next business date. Showing this package and the function, NEXT_BUSINESS_DAY:

 PACKAGE business_days IS     function next_business_day RETURN DATE;     other functions, etc END business_days; 

You can use the logic in this package within an IF condition. Assume your code has a DATE variable called TRANSACTION_DATE. Use the logic built into the existing NEXT_BUSINESS_DATE function with the following code:

 IF (transaction_date = business_days.next_business_day) THEN 

It is common to have packages that are developed to encapsulate the logic of business rules. Should the rule for the next business day change, only the BUSINESS_DAY package is updated. The code that uses the package is not changed.

11.10.6 Use DECODE and CASE in SQL

Unnecessary logic can easily creep into PL/SQL procedures. Consider the transformations needed when querying data from the database. SQL CASE expressions have built-in IF-THEN_ELSE logic. The DECODE statement performs value transformations. Both are illustrated with examples.

Consider the following IF statement that assigns a value to B based on the value in A ”logic of this kind can and should be minimized where possible with SQL functions.

 IF (A < 10) THEN     B := 1; ELSIF (A  >= 10 AND A < 20) THEN     B := 2; ELSIF (A = 225) THEN     B := 3; ELSE     B := 4; END IF; 

Reduce and eliminate the type of logic shown by performing the transformation within the SQL query. The following illustrates how a SQL CASE statement performs the equivalent transformations used in the previous IF statements.

 CREATE TABLE TEMP (A NUMBER(2)); INSERT INTO TEMP VALUES (11); DECLARE     B INTEGER; BEGIN     SELECT CASE             WHEN A < 10             THEN 1             WHEN A >= 10 AND A < 20 THEN 2             WHEN A = 225            THEN 3 END     INTO B FROM TEMP;     dbms_output.put_line('B='b); END; 

SQL statements with DECODE transform values when the data is selected from the database. The following SQL queries the college major descriptions from the MAJOR_LOOKUP table. Within the query each major description is transformed to an alternative string.

 SELECT DECODE        ( major_desc,         'Undeclared'   , 'A1',         'Biology'      , 'A2',         'Math/Science' , 'A3',         'History'      , 'A4',         'English'      , 'A5') major FROM major_lookup; 

The SQL CASE expression is more powerful that DECODE. Use CASE and DECODE to initially transform data. This simplifies the PL/SQL logic.