3.5 Control Structures

Table of Contents Previous Next


3 Stored Procedure Language : 3.5 Control Structures

The programming statements in SPL that make it a full procedural complement to SQL are described in this section.
IF statements let you execute commands based on certain conditions. SPL has four forms of IF:
3.5.1.1 IF-THEN
IF boolean-expression THEN
statements
IF-THEN statements are the simplest form of IF. The statements between THEN and END IF will be executed if the condition is true. Otherwise, they are skipped.
In the following example an IF-THEN statement is used to test and display employees who have a commission.
3.5.1.2 IF-THEN-ELSE
IF boolean-expression THEN
statements
statements
IF-THEN-ELSE statements add to IF-THEN by letting you specify an alternative set of statements that should be executed if the condition evaluates to false.
The previous example is modified so an IF-THEN-ELSE statement is used to display the text Non-commission if the employee does not get a commission.
IF statements can be nested so that alternative IF statements can be invoked once it is determined whether or not the conditional of an outer IF statement is true or false.
In the following example the outer IF-THEN-ELSE statement tests whether or not an employee has a commission. The inner IF-THEN-ELSE statements then test whether the employee’s total compensation exceeds or is less than the company average.
Note: The logic in this program can be simplified considerably by calculating the employee’s yearly compensation using the NVL function within the SELECT command of the cursor declaration, however, the purpose of this example is to demonstrate how IF statements can be used.
When you use this form, you are actually nesting an IF statement inside the ELSE part of an outer IF statement. Thus you need one END IF statement for each nested IF and one for the parent IF-ELSE.
IF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements ] ...]
statements ]
IF-THEN-ELSIF-ELSE provides a method of checking many alternatives in one statement. Formally it is equivalent to nested IF-THEN-ELSE-IF-THEN commands, but only one END IF is needed.
The following example uses an IF-THEN-ELSIF-ELSE statement to count the number of employees by compensation ranges of $25,000.

3 Stored Procedure Language : 3.5 Control Structures

Table of Contents Previous Next