Section 1.4. Control Statements

1.4. Control Statements

There are two types of PL/SQL control statements : conditional control statements and sequential control statements . Almost every piece of code you write will require conditional control, which is the ability to direct the flow of execution through your program based on a condition. You do this with IF-THEN-ELSE and CASE statements (CASE statements are available in Oracle9i Database and Oracle Database 10g). There are also CASE expressions ; while not the same as CASE statements, they can sometimes be used to eliminate the need for an IF or CASE statement altogether. Far less often, you will need to tell PL/SQL to transfer control unconditionally via the GOTO statement , or explicitly to do nothing via the NULL statement .

1.4.1. IF Statements

The IF statement allows you to design conditional logic into your programs, and comes in three flavors, as shown in Table 1-4.

Table 1-4. Types of IF statements

IF type	Characteristics
IF condition THEN END IF;	This is the simplest form of the IF statement. The condition between IF and THEN determines whether the set of statements between THEN and END IF should be executed. If the condition evaluates to FALSE, the code is not executed.
IF condition THEN ELSE END IF;	This combination implements an either/or logic: based on the condition between the IF and THEN keywords, execute the code either between THEN and ELSE or between ELSE and END IF. One of these two sections of executable statements is performed.
IF condition1 THEN ELSIF condition2 THEN ELSE END IF;	This last and most complex form of the IF statement selects an action from a series of mutually exclusive conditions and then executes the set of statements associated with that condition. If you're writing IF statements like this using any release from Oracle9i Database Release 1 onwards, you should consider using searched CASE statements instead.

1.4.2. CASE Statements and Expressions

The CASE statement allows you to select one sequence of statements to execute out of many possible sequences. CASE statements have been part of the SQL standard since 1992, although Oracle SQL didn't support CASE until the release of Oracle8i Database, and PL/SQL didn't support CASE until Oracle9i Database Release 1. From this release onward, the following types of CASE statements are supported:

Simple CASE statement

Associates each of one or more sequences of PL/SQL statements with a value. Chooses which sequence of statements to execute based on an expression that returns one of those values.

Searched CASE statement

Chooses which of one or more sequences of PL/SQL statements to execute by evaluating a list of Boolean conditions. The sequence of statements associated with the first condition that evaluates to TRUE is executed.

In addition to CASE statements, PL/SQL also supports CASE expressions . A CASE expression is very similar in form to a CASE statement and allows you to choose which of one or more expressions to evaluate. The result of a CASE expression is a single value, whereas the result of a CASE statement is the execution of a sequence of PL/SQL statements.

1.4.2.1. Simple CASE statement

A simple CASE statement allows you to choose which of several sequences of PL/SQL statements to execute based on the results of a single expression. Here is an example of a simple CASE statement that uses the employee type as a basis for selecting the proper bonus algorithm:

     CASE employee_type     WHEN 'S' THEN        award_salary_bonus(employee_id);     WHEN 'H' THEN        award_hourly_bonus(employee_id);     WHEN 'C' THEN        award_commissioned_bonus(employee_id);     ELSE        RAISE invalid_employee_type;     END CASE;

This CASE statement has an explicit ELSE clause ; however, the ELSE is optional. When you do not explicitly specify an ELSE clause of your own, PL/SQL implicitly uses the following:

     ELSE        RAISE CASE_NOT_FOUND;

1.4.2.2. Searched CASE statement

A searched CASE statement evaluates a list of Boolean expressions and, when it finds an expression that evaluates to TRUE, executes a sequence of statements associated with that expression. Essentially, a searched CASE statement is the equivalent of the CASE TRUE statement shown in the previous section. Here is an example of a searched CASE statement:

     CASE     WHEN salary >= 10000 AND salary <=20000 THEN        give_bonus(employee_id, 1500);     WHEN salary > 20000 AND salary <= 40000 THEN        give_bonus(employee_id, 1000);     WHEN salary > 40000 THEN        give_bonus(employee_id, 500);     ELSE        give_bonus(employee_id, 0);     END CASE;